Calculating Accrued Interest of a Bond

[gcefaloni]

Hi,

I'm trying to calculate accrued interest since last coupon payment date (using ACCRINT function) without any helper columns. I have access to Issue date, first coupon, coupon rate, coupon frequency, par amount and every information needed for the ACCRINT function to work.

So, essentially I have daily dates rolling down (in column A), and interest accrued in a bond payment (in Column B) up until the actual coupon is paid out at which date the accrued interest needs to come back to zero. The following day after a coupon payment, interest accrued needs to restart accumulating in column B. So on and so forth. Accrued interest accumulates until the coupon payment is made, at which date it goes to 0. Rinse and repeat.

The problem I'm having is that I don't know how to make it recognize when the coupon payment date is and then have the following accrued interest calculation take that date as the new last coupon date. All that without using a helper column.

DB - Historical CAN Bond Data - Interest accrued.xlsb
	A	B
1	INT_ACC	01285PBU1
2	SECURITY_NAME	ALBCAP 4.45 12/15/25
3	ISSUE_DT	10/5/2005
4	FIRST_CPN_DT	12/15/2005
5	CPN	4.45
6	CPN_FREQ	2
7	PAR_AMT	5000
Sheet1

I'm on my way there but not quite. I'm not sure I'm going the right way about this. Right now, the accrued interest just keeps accumulating from the issue date and never resets back to 0 when a coupon is supposed to be paid (every 6 months).

DB - Historical CAN Bond Data - Interest accrued.xlsb
	A	B
2474	9/30/2005
2475	10/1/2005
2476	10/2/2005
2477	10/3/2005
2478	10/4/2005
2479	10/5/2005	0
2480	10/6/2005	0.618056
2481	10/7/2005	1.236111
2482	10/8/2005	1.854167
2483	10/9/2005	2.472222
2484	10/10/2005	3.090278
2485	10/11/2005	3.708333
2486	10/12/2005	4.326389
2487	10/13/2005	4.944444
2488	10/14/2005	5.5625
Sheet1

Any help on how to make the column B reset to 0 when an interest payment is made (6 months from the last coupon payment date) and the restart calculating from that new last coupon date, would be EXTREMELY appreciated.

Initially, that was my first formula: =IF(A2480=DATEVALUE(B$3),0,IF(A2480<DATEVALUE(B$3),"",ACCRINT(B$3,B$4,A2480,B$5/100,B$7,B$6,0,FALSE)))
But then I needed to find a way to make the last coupon payment date change so I tried to add this: INDEX($A$1:$A2479,AGGREGATE(14,6,ROW($B$1:$B2479)/($B$1:$B2479=0)*($B$1:$B2479<>""),COUNTIF($B$1:B2479,0))) but I still don't know how to make the cell change to 0 when a coupon is paid...

I'm confused at this point and need help sorting this out.

Again, any help would be very very appreciated.

Thanks!
I posted the same question on: Calculating accrued interest

 

[KRice]

Thanks for clearing up that concern. Some changes will be needed to deal with variable rates. You might consider creating a table somewhere showing the starting date associated with a rate and a particular type of bill (3 columns), and then changing the main formula running down the table by substituting in a new expression for B9 (in two places). Then instead of consistently referring back to a fixed daily accrual amount in B9, the new expression would use either VLOOKUP or INDEX/MATCH and compare the date associated with that row to the lookup table and extract from it the appropriate rate for use in the ACCRINT function.

By the way, I noticed in the formula that the reference to the daily accrual amount is $B$9 in one place and B$9 in another place...it is probably better to use B$9 in both places so that if you copy this formula into other columns for tracking other bonds, the formula will automatically adjust and point to the correct daily accrual amount in that same column.

 

[gcefaloni]

Thanks for clearing up that concern. Some changes will be needed to deal with variable rates. You might consider creating a table somewhere showing the starting date associated with a rate and a particular type of bill (3 columns), and then changing the main formula running down the table by substituting in a new expression for B9 (in two places). Then instead of consistently referring back to a fixed daily accrual amount in B9, the new expression would use either VLOOKUP or INDEX/MATCH and compare the date associated with that row to the lookup table and extract from it the appropriate rate for use in the ACCRINT function.

By the way, I noticed in the formula that the reference to the daily accrual amount is $B$9 in one place and B$9 in another place...it is probably better to use B$9 in both places so that if you copy this formula into other columns for tracking other bonds, the formula will automatically adjust and point to the correct daily accrual amount in that same column.

I'll dabble with that soon and see what comes out of it regarding the variable rates. As for the B9 change, I had already done it. Thank you for pointing it out still! I might come back and bug you some more later on if I encounter issues with the variable rates / total return calculation (although I think the total return one is pretty straight forward at this point).

Thanks again!

 

[KRice]

You're welcome...good luck!