ACCRINT formula yields incorrect result

O

Onurali_k

I applied the ACCRINT formula to a security with

maturity: 09 Feb 2010
issue: 09 Feb 2005 (adjusted to exclude coupons already paid)
first interest: 09 Feb 2005
settlement: 25 Mar 2005
coupon rate: 9.25%
frequency: 1
basis: 1 (ACT/ACT)

the formula should yield

9.25 x (25 Mar 2005 - 09 Feb 2005) / (09 Feb 2006 - 09 Feb 2005) =
9.25 x 44 / 365 = 1.1150685

but Excel calculates it as 1.112021858

upon exploration, I found out that it takes the day count of year as 366
mistakenly.

Is there a solution for this bug?
 
D

Duke Carey

I'm using Excel XP on a Win2k PC & I get the results you seek.

What version are you using?
 
O

Onurali_k

Meanwhile I checked the formula in another computer running Excel 2003 SP-1
and it gives the wrong result too.
 
D

Duke Carey

I just checked it on a PC with Excel 2000 and got your expected results.

Please double-check that your dates are actually 2005 dates, not 2004 dates
 
D

Duke Carey

Well, apparently I am careless. The number I got didn't match either of
yours and I cannot figure out what got factored into that result. It looked
very much like your desired result, but started with 1.105 instead of 1.115

Also, I was careless in considering your question. If the bond is issued
2/9/05, the first payment will be 2/9/06 (assuming annual coupons). If you
change that argument in your formula, do you get the right answer? I *think*
I did.
 
O

Onurali_k

:) I was just going to post a message correct myself. Upon a not-so-related
question from a colleague, we discovered that a few minutes before I read
your response, of course with your invaluable help.

Most of the securities in my portfolio have bases of 30/360 and ACCRINT
calculates the correct accrued coupon even if I enter the last coupon date as
"first interest", therefore I applied the same wrong formula to all
securities and those having ACT day bases gave incorrect results.

Thank you so much...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top