# Calculating Accrued Interest of a Bond

#### gcefaloni

##### Board Regular
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
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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### gcefaloni

##### Board Regular
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
You're welcome...good luck!

Replies
2
Views
48
Replies
1
Views
144
Replies
1
Views
74
Replies
1
Views
151
Replies
1
Views
208

1,127,325
Messages
5,624,016
Members
416,006
Latest member
PCaffrey

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

### Which adblocker are you using?

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

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