How to calculate accumulated interest daily?

Status
Not open for further replies.

gcefaloni

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

DB - Historical CAN Bond Data - Interest accrued.xlsb
AB
3ISSUE_DT10/5/2005
4FIRST_CPN_DT12/15/2005
5CPN4.45
6CPN_FREQ2
7PAR_AMT5000
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
AB
254512/10/2005 
254612/11/2005 
254712/12/2005 
254812/13/2005 
254912/14/2005 
255012/15/20050
255112/16/20050.607923
255212/17/20051.215847
255312/18/20051.82377
255412/19/20052.431694
255512/20/20053.039617
255612/21/20053.647541
255712/22/20054.255464
255812/23/20054.863388
255912/24/20055.471311
256012/25/20056.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
AB
27266/9/2006106.9945
27276/10/2006107.6025
27286/11/2006108.2104
27296/12/2006108.8183
27306/13/2006109.4262
27316/14/2006110.0342
27326/15/2006110.6421
27336/16/2006111.25
27346/17/2006111.8579
27356/18/2006112.4658
27366/19/2006113.0738
27376/20/2006113.6817
27386/21/2006114.2896
27396/22/2006114.8975
27406/23/2006115.5055
27416/24/2006116.1134
27426/25/2006116.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:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,129,299
Messages
5,635,387
Members
416,856
Latest member
silentir

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