A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Bond premium amortization using the constant yield method



 
 
Thread Tools Display Modes
  #1  
Old January 26th, 2010, 08:38 PM posted to microsoft.public.excel.worksheet.functions
lmahaffey
external usenet poster
 
Posts: 2
Default Bond premium amortization using the constant yield method

Anyone know of a function that calculates the bond premium amortization using
the constant yield method?

An example of a bond premium that would need amortizing would be:

Par: 100,000
Price: 101
Bought: July 15, 2009
Maturity: June 30, 2019
Cash Paid: 101,000
Premium: 1,000
Coupon: 5% paid semi-annually, 6/30 and 12/31

Thank you
  #2  
Old June 4th, 2010, 12:54 AM posted to microsoft.public.excel.worksheet.functions
Dev Purkayastha
external usenet poster
 
Posts: 1
Default Calculating Bond Amortization using Excel functions

To do the calculation first you need to calculate yield to maturity. Then you create the the Bond Calendar by using Days360 function in Excel. Please note that corporate bond interest is calculated on a 360 day year basis. There are two flavors, one is US NASDAQ, and the other is European. Using this function you compute the number of days from the purchase date to each month end. Then you can subtract the previous months number of days since purchase from this month's number. That gives you the number of days inthe month. Then you calculate the Effective Interest earned on the (Bond Par Value+Premium or -discount). Then you subtract this interest from the coupon interest on the Par Value of the Bond. If the Bond was purchased at a premium, the effective interest will be lower than the coupon. This difference is then applied to the Bond Premium to write-off the premium against Coupon Interest. You have to reduce premium every month vbefroe you calculate the next month's effective interest. Coupon interest stays constant. So if all your calculations are right, the bond premium should be reduced to 0 by the maturity date. Good luck. It sounds more complicated than it is.



lmahaffey wrote:

Bond premium amortization using the constant yield method
26-Jan-10

Anyone know of a function that calculates the bond premium amortization using
the constant yield method?

An example of a bond premium that would need amortizing would be:

Par: 100,000
Price: 101
Bought: July 15, 2009
Maturity: June 30, 2019
Cash Paid: 101,000
Premium: 1,000
Coupon: 5% paid semi-annually, 6/30 and 12/31

Thank you

Previous Posts In This Thread:

On Tuesday, January 26, 2010 2:38 PM
lmahaffey wrote:

Bond premium amortization using the constant yield method
Anyone know of a function that calculates the bond premium amortization using
the constant yield method?

An example of a bond premium that would need amortizing would be:

Par: 100,000
Price: 101
Bought: July 15, 2009
Maturity: June 30, 2019
Cash Paid: 101,000
Premium: 1,000
Coupon: 5% paid semi-annually, 6/30 and 12/31

Thank you


Submitted via EggHeadCafe - Software Developer Portal of Choice
MSChart For VB.Net
http://www.eggheadcafe.com/tutorials...for-vbnet.aspx
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:50 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.