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.

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

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

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

Last edited by a moderator: