ACCRINT formula yields incorrect result

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?

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

What version are you using?

"Onurali_k" wrote:

> 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?
>
>

MS Excel 2000 9.0.6926 SP-3

"Duke Carey" wrote:

> I'm using Excel XP on a Win2k PC & I get the results you seek.
>
> What version are you using?
>
> "Onurali_k" wrote:
>
> > 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?
> >
> >

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

"Onurali_k" wrote:

> MS Excel 2000 9.0.6926 SP-3
>
> "Duke Carey" wrote:
>
> > I'm using Excel XP on a Win2k PC & I get the results you seek.
> >
> > What version are you using?
> >
> > "Onurali_k" wrote:
> >
> > > 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?
> > >
> > >

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

"Onurali_k" wrote:

> Meanwhile I checked the formula in another computer running Excel 2003 SP-1
> and it gives the wrong result too.
>
> "Onurali_k" wrote:
>
> > MS Excel 2000 9.0.6926 SP-3
> >
> > "Duke Carey" wrote:
> >
> > > I'm using Excel XP on a Win2k PC & I get the results you seek.
> > >
> > > What version are you using?
> > >
> > > "Onurali_k" wrote:
> > >
> > > > 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?
> > > >
> > > >

Not that careless, they are 2005 dates. Can this be a Y2K problem?

"Duke Carey" wrote:

> 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
>
> "Onurali_k" wrote:
>
> > Meanwhile I checked the formula in another computer running Excel 2003 SP-1
> > and it gives the wrong result too.
> >
> > "Onurali_k" wrote:
> >
> > > MS Excel 2000 9.0.6926 SP-3
> > >
> > > "Duke Carey" wrote:
> > >
> > > > I'm using Excel XP on a Win2k PC & I get the results you seek.
> > > >
> > > > What version are you using?
> > > >
> > > > "Onurali_k" wrote:
> > > >
> > > > > 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?
> > > > >
> > > > >

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.



"Onurali_k" wrote:

> Not that careless, they are 2005 dates. Can this be a Y2K problem?
>
> "Duke Carey" wrote:
>
> > 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
> >
> > "Onurali_k" wrote:
> >
> > > Meanwhile I checked the formula in another computer running Excel 2003 SP-1
> > > and it gives the wrong result too.
> > >
> > > "Onurali_k" wrote:
> > >
> > > > MS Excel 2000 9.0.6926 SP-3
> > > >
> > > > "Duke Carey" wrote:
> > > >
> > > > > I'm using Excel XP on a Win2k PC & I get the results you seek.
> > > > >
> > > > > What version are you using?
> > > > >
> > > > > "Onurali_k" wrote:
> > > > >
> > > > > > 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?
> > > > > >
> > > > > >

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...

"Duke Carey" wrote:

> 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.
>
>
>
> "Onurali_k" wrote:
>
> > Not that careless, they are 2005 dates. Can this be a Y2K problem?
> >
> > "Duke Carey" wrote:
> >
> > > 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
> > >
> > > "Onurali_k" wrote:
> > >
> > > > Meanwhile I checked the formula in another computer running Excel 2003 SP-1
> > > > and it gives the wrong result too.
> > > >
> > > > "Onurali_k" wrote:
> > > >
> > > > > MS Excel 2000 9.0.6926 SP-3
> > > > >
> > > > > "Duke Carey" wrote:
> > > > >
> > > > > > I'm using Excel XP on a Win2k PC & I get the results you seek.
> > > > > >
> > > > > > What version are you using?
> > > > > >
> > > > > > "Onurali_k" wrote:
> > > > > >
> > > > > > > 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?
> > > > > > >
> > > > > > >