Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Bond premium amortization using the constant yield method

Yes, Microsoft Excel has a function that can calculate bond premium amortization using the constant yield method. The function is called ACCRINT and it can be used to calculate the amount of bond premium amortization for a given period.

Here's how you can use the ACCRINT function to calculate the bond premium amortization for the example you provided:
  1. Open a new Excel spreadsheet and enter the following information in separate cells:
    - Par: 100,000
    - Price: 101
    - Bought: July 15, 2009
    - Maturity: June 30, 2019
    - Cash Paid: 101,000
    - Premium: 1,000
    - Coupon: 5%
    - Payment Frequency: Semi-Annual
  2. In a new cell, enter the following formula:
    Code:
    =ACCRINT(settlement, maturity, rate, par, frequency, [basis], [calc_method], [first_period], [last_period])
  3. Replace the arguments in the formula with the following:
    - settlement: The date the bond was purchased (July 15, 2009)
    - maturity: The maturity date of the bond (June 30, 2019)
    - rate: The annual coupon rate (5%)
    - par: The face value of the bond (100,000)
    - frequency: The number of coupon payments per year (2 for semi-annual)
    - basis: The day count basis to use (optional, defaults to 0 for US (NASD) 30/360)
    - calc_method: The method to use for calculating the bond's yield (optional, defaults to 0 for the US Treasury method)
    - first_period: The first period for which to calculate the bond's accrued interest (optional, defaults to settlement date)
    - last_period: The last period for which to calculate the bond's accrued interest (optional, defaults to maturity date)
  4. Press Enter to calculate the bond premium amortization for the first period. The result should be -4.166666667, which represents the amount of premium amortization for the first semi-annual period.
  5. Copy the formula to the cells below to calculate the bond premium amortization for each subsequent period.

That's it! The ACCRINT function should provide you with the bond premium amortization using the constant yield method for the given bond.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to calculate bond value and current yield Gary Excel Worksheet Functions 2 July 7th 07 11:32 PM
yield on bond priced over 100 John Excel Worksheet Functions 2 January 23rd 06 02:58 PM
Bond Yield Template with Payment schecule and chart (Excel) Jen Excel Discussion (Misc queries) 3 April 28th 05 08:18 PM
Effective yield rate - non-level premium flow Brad Excel Discussion (Misc queries) 2 April 6th 05 08:07 PM
Effective yield rate - non level premium flow Brad Excel Worksheet Functions 1 April 6th 05 07:48 PM


All times are GMT +1. The time now is 01:36 PM.

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

About Us

"It's about Microsoft Excel"