Correctly calculate the accrued interest on a bond with an odd first period
Oct
18
Written by:
Charles Flock
10/18/2011 8:13 PM
11 Financial Calculations that you can’t do in EXCEL - Part 5 of 11
5. Correctly calculate the accrued interest on a bond with an odd first period
Set up a worksheet with the following data.
Enter the following formula in B9: =ACCRINT(B1,B2,B3,B4,B5,B6,B7,B8)
This produces the following result:
What this should be telling us is that a bond with coupon rate of 2%, a par value of 100, issued on the 12th of March 2011, purchased on the 13th of March 2011, with a first interest payment date of the 12th of January, 2012, paying interest twice-yearly, has .016424454 in accrued interest, when calculated using the actual/actual basis. Is this the right amount? Not even close!
This bond has what is referred to as an odd long first coupon. The way to calculate the accrued interest for bonds like this is to calculate something called a quasi-coupon. We know from the inputs, that the normal coupon is 1.00 which is paid every six months. We also know that there are 2 implied coupon periods; 12-Jan-11 through 12-Jul-11 and 12-Jul-11 through 12-Jan-12. There are 181 days in the first quasi-coupon period and 184 days in the second quasi-coupon period.
Since the settlement date is one day after the issue data and occurs in the first quasi-coupon period, the accrued interest should be 1/181 * 1.00.
Here’s what the answer should have been:
SELECT wct.ACCRINT(
'2011-03-12' --issue
,'2012-01-12' --first_interest
,'2011-03-13' --settlement
,.02 --rate
,100 --par
,2 --frequency
,1 --basis
,0 --calc_method
)
This produces the following result.
----------------------
0.00552486187845304
(1 row(s) affected)
Is it possible that we are misunderstanding the EXCEL documentation? Maybe EXCEL wants us to enter the quasi-coupon start date?
That’s an answer that’s closer to correct, but it’s still not right. This answer is 1/184 * 1, but as we pointed out earlier, there are 181 days in the quasi-coupon period in which the settlement date occurs, not 184. It’s also important to point out that the EXCEL documentation provides an example where the first_interest date is greater than the issue date and greater than the settlement date and that the first interest payment date for a bond is generally included in the bond characteristics whereas a quasi-coupon data has to be calculated elsewhere before it can be entered into the function.
This is elementary bond mathematics. It’s shocking that EXCEL cannot get this right.