ACCRINT problem

[M Welinder]

Consider the following sheet:

A1=14-Sep-2000 [issue]
A2=22-Mar-2001 [first interest]
A3=21-Dec-2000 [settlement]
A4=1% [rate]
A5=$1000 [par]
A6=4 [frequency]
A7=1 [basis ACT/ACT]

A10=ACCRINT(A1,...,A7)
A11=A10/(A4*A5/A6)

A11 comes out as 1.075845... which is precisely 8/92 + 89/90.

There are quasi-coupon dates at 22-Jun-2000 and 22-Sep-2000 which are 92 days
apart, so the 8/92 part is clear. The next quasi-coupon date is 22-Dec-2000
(91 days later), so I claim the last part should be 90/91.

There are 98 days from 14-Sep-2000 to 21-Dec-2000, so how can Excel claim that
there are just 8 + 89 = 97?

M.

 

[Don P]

M,

Excel's ACCRINT formula does not handle "long-coupon dates", which is what
you are describing. It can only handle accruals between two payment dates.
If you substitute 9/22/2000 as the issue date, 12/22/2000 as the first
interest date, and 12/21/2000 as settlement, ACCRINT returns $2.4725275. The
quarterly payment of $2.5 * 90/91 =$2.4725275, which is what you claim the
last part should be. If you need to use long-coupons, I would suggest
writing your own macro. You can by third party add-ins, but they are not
cheap.

Don Pistulka

 

[Jay Petrulis]

Hi,

Without looking too much into the specifics, I agree with you on this
one in thinking that Excel *may* be a bit inconsistent.

IIRC, if you "wake up in the morning" as the owner of the asset, you
get that day's interest.

So, we have the following interest amounts from 14 Sep 00 to 21 Dec 00
Sept -- 16 days (Sept 15 - Sept 30)
Oct -- 31 days
Nov -- 30 days
Dec -- 21 days (seller receives the accrued interest on settlement
date)

98 days would be what I would calculate as well. Of course, simple
date arithmetic returns the 98 days, too (99 days if held throughout
[9/14 - 12/21] rather than (9/14 - 12/21]).

Excel *might* treat the settlement "as if" it occurred at the
beginning of the day, whereby the buyer gets the interest. I believe
that is wrong with an ACT/ACT day-basis. Also, Excel might be
mishandling the interim quasi-coupon on 22 Sep 00, or more accurately,
its understanding of the interest owed in the periods.

Bye,
Jay

 

[M Welinder]

(e-mail address removed) (Jay Petrulis) wrote in message
[...]

98 days would be what I would calculate as well. Of course, simple
date arithmetic returns the 98 days, too (99 days if held throughout
[9/14 - 12/21] rather than (9/14 - 12/21]).

Excel *might* treat the settlement "as if" it occurred at the
beginning of the day, whereby the buyer gets the interest. I believe
that is wrong with an ACT/ACT day-basis. Also, Excel might be
mishandling the interim quasi-coupon on 22 Sep 00, or more accurately,
its understanding of the interest owed in the periods.

It gets weirder. Now add 30 days to each date. They become
14-Oct-2000, 21-Apr-2001, and 20-Jan-2001. Repeat the calculation.

Excel now uses 7/92+89/90. 7/92 is right, but the latter half should
be 91/92. In other words, Excel is now dumping two days on the floor!

Is it me, or is ACCRINT just utterly bogus in this case?