Calculating Accrued Interest of a Bond

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
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
AB
1INT_ACC01285PBU1
2SECURITY_NAMEALBCAP 4.45 12/15/25
3ISSUE_DT10/5/2005
4FIRST_CPN_DT12/15/2005
5CPN4.45
6CPN_FREQ2
7PAR_AMT5000
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
AB
24749/30/2005 
247510/1/2005 
247610/2/2005 
247710/3/2005 
247810/4/2005 
247910/5/20050
248010/6/20050.618056
248110/7/20051.236111
248210/8/20051.854167
248310/9/20052.472222
248410/10/20053.090278
248510/11/20053.708333
248610/12/20054.326389
248710/13/20054.944444
248810/14/20055.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
 
Last edited by a moderator:

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,061
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
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

Well-known Member
Joined
Dec 9, 2003
Messages
1,061
Office Version
  1. 2019
Platform
  1. Windows
You're welcome...good luck!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,149
Members
415,881
Latest member
tasic89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top