Hi,
In analyzing a bond that pays interest, I'm trying to find a way to calculate the next payment date and the last payment date in order to calculate what is the accumulated interest that is to be paid between the 2 dates in question.
So, let's say I know the issue date of a bond. And I know that that bond pays a coupon every 6 months after the first coupon payment date (days in column A). How would I go about calculating the accumulated interest every day (in column B) between 2 coupon payment dates? And when the next coupon date arrives, the accumulated interest resets back to 0 because the coupon is paid out. The next cell below, the interest restarts being accumulated every day with the new "last coupon payment date". So on and so forth from the issue date all the way to today.
I don't know how to make the accumulated interest reset back to 0 when the coupon payment date arrives. I know I'm supposed to use ACCRINT function. But if I only want to calculate the interest accumulated since the last payment rather than since the issue date, I need to reset it somehow and I'm lost as to how to do that.
In the following table, you see the first coupon payment date is December 15th 2005. Then the accumulated interest starts up until the next coupon payment 6 months later (June 15th 2006).
In the following table: The interest should accumulated up until June 14th 2006 and reset back to 0 on June 15th 2006. Then restart counting from June 16th all the way to December 14th 2006, reset to 0 on on December 15th 2006. Rinse and repeat.
Right now, it just takes the issue date and accumulates interest non-stop until the end of the series (today). I want it column B to reset back to 0 at the correct coupon payment date (every 6 months after the first coupon payment date) and calculate accrued interest in between every coupon payment dates.
Thanks a lot for your help!!!
P.S. I posted a similar question that was poorly worded in this thread Calculating Accrued Interest of a Bond (no answers). Hence I'm trying to be clearer in this one in order to get some help.
In analyzing a bond that pays interest, I'm trying to find a way to calculate the next payment date and the last payment date in order to calculate what is the accumulated interest that is to be paid between the 2 dates in question.
So, let's say I know the issue date of a bond. And I know that that bond pays a coupon every 6 months after the first coupon payment date (days in column A). How would I go about calculating the accumulated interest every day (in column B) between 2 coupon payment dates? And when the next coupon date arrives, the accumulated interest resets back to 0 because the coupon is paid out. The next cell below, the interest restarts being accumulated every day with the new "last coupon payment date". So on and so forth from the issue date all the way to today.
I don't know how to make the accumulated interest reset back to 0 when the coupon payment date arrives. I know I'm supposed to use ACCRINT function. But if I only want to calculate the interest accumulated since the last payment rather than since the issue date, I need to reset it somehow and I'm lost as to how to do that.
| DB - Historical CAN Bond Data - Interest accrued.xlsb | ||||
|---|---|---|---|---|
| A | B | |||
| 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 | ||||
In the following table, you see the first coupon payment date is December 15th 2005. Then the accumulated interest starts up until the next coupon payment 6 months later (June 15th 2006).
| DB - Historical CAN Bond Data - Interest accrued.xlsb | ||||
|---|---|---|---|---|
| A | B | |||
| 2545 | 12/10/2005 | |||
| 2546 | 12/11/2005 | |||
| 2547 | 12/12/2005 | |||
| 2548 | 12/13/2005 | |||
| 2549 | 12/14/2005 | |||
| 2550 | 12/15/2005 | 0 | ||
| 2551 | 12/16/2005 | 0.607923 | ||
| 2552 | 12/17/2005 | 1.215847 | ||
| 2553 | 12/18/2005 | 1.82377 | ||
| 2554 | 12/19/2005 | 2.431694 | ||
| 2555 | 12/20/2005 | 3.039617 | ||
| 2556 | 12/21/2005 | 3.647541 | ||
| 2557 | 12/22/2005 | 4.255464 | ||
| 2558 | 12/23/2005 | 4.863388 | ||
| 2559 | 12/24/2005 | 5.471311 | ||
| 2560 | 12/25/2005 | 6.079235 | ||
Sheet1 | ||||
In the following table: The interest should accumulated up until June 14th 2006 and reset back to 0 on June 15th 2006. Then restart counting from June 16th all the way to December 14th 2006, reset to 0 on on December 15th 2006. Rinse and repeat.
| DB - Historical CAN Bond Data - Interest accrued.xlsb | ||||
|---|---|---|---|---|
| A | B | |||
| 2726 | 6/9/2006 | 106.9945 | ||
| 2727 | 6/10/2006 | 107.6025 | ||
| 2728 | 6/11/2006 | 108.2104 | ||
| 2729 | 6/12/2006 | 108.8183 | ||
| 2730 | 6/13/2006 | 109.4262 | ||
| 2731 | 6/14/2006 | 110.0342 | ||
| 2732 | 6/15/2006 | 110.6421 | ||
| 2733 | 6/16/2006 | 111.25 | ||
| 2734 | 6/17/2006 | 111.8579 | ||
| 2735 | 6/18/2006 | 112.4658 | ||
| 2736 | 6/19/2006 | 113.0738 | ||
| 2737 | 6/20/2006 | 113.6817 | ||
| 2738 | 6/21/2006 | 114.2896 | ||
| 2739 | 6/22/2006 | 114.8975 | ||
| 2740 | 6/23/2006 | 115.5055 | ||
| 2741 | 6/24/2006 | 116.1134 | ||
| 2742 | 6/25/2006 | 116.7213 | ||
Sheet1 | ||||
Right now, it just takes the issue date and accumulates interest non-stop until the end of the series (today). I want it column B to reset back to 0 at the correct coupon payment date (every 6 months after the first coupon payment date) and calculate accrued interest in between every coupon payment dates.
Thanks a lot for your help!!!
P.S. I posted a similar question that was poorly worded in this thread Calculating Accrued Interest of a Bond (no answers). Hence I'm trying to be clearer in this one in order to get some help.
Last edited:
