Once a bond is issued, the amount of interest that will be paid to the bond holder is fixed until the bond matures. To adjust for changing interest rates, the market value of the bond will increase or decrease over time. For example, if a bond pays an 8 percent annual coupon rate and current rates are 5 percent, the bond will sell at a premium price to bring the return to a new owner in line with current rates. An estimated price of a premium bond can be calculated using the current yield and the coupon rate; or a simple spreadsheet can be set up to calculate an accurate current price.
Quick Price Calculation
Look up the current yield for comparable bonds. The coupon rate or annual interest and time to maturity of the bond should be known. The Wall Street Journal, both in print and online at wsj.com, provides a listing of current bond data.
An estimated bond value is the annual coupon rate divided by the current yield. If a bond pays $80 per year in interest per $1,000 of face amount (8 percent coupon) and the current market yield is 7 percent, calculate 80 divided by 0.07. In this case, a $1,000 bond has a premium value of $1,142.85.
Use this premium calculation to get an idea what a premium bond is worth. The longer the maturity, the more accurate the estimate.
Spreadsheet Price Calculation
Using spreadsheet software such as Microsoft Excel or Open Office Calc, set up two columns, one for labels and the second for data.
Type these labels into the label column: Today's Date, Maturity Date, Coupon Rate, Current Yield, Par Value, Frequency and Price.
In the second column enter the data corresponding to the label. The maturity date is the date when the bond principal will be returned to the bond holder. Coupon rate and current yield should be expressed as a percentage. A $1,000 bond paying $40 twice a year would has a coupon rate of 8 percent. Par value is the maturity value expressed as a percentage, usually 100. However, enter this value as a whole number, not a percentage. The frequency is the number of interest payments per year, usually two.
In the column next to the price label, type in the PRICE function using the entered data. If the data is in column B, the formula will look like this: \=PRICE(B1,B2,B3,B4,B5,B6). The PRICE function will return a current value of the bond based on the provided data.
Check the results of your spreadsheet. Using a 8 percent coupon, 7 percent current yield and a 30-year maturity from the current date should produce a price of $112.47. This means a $1,000 face amount bond would have a premium price of $1,124.70. If the results are accurate, you can now figure the premium value of any bond with an above market coupon rate.
Tips
The PRICE function and the required data is listed this way: PRICE(settlement, maturity, rate, yield, redemption, frequency [basis]). The spreadsheet help section will explain the elements if you have problems. The dates and percentage rates should be set up with the "Format Cells" command. Do not forget the "=" sign in front of the PRICE function.
Warnings
The quick price calculation will provide a price that is significantly higher than the market value for all but the longest term bonds. The spreadsheet uses internal rate of return calculations to provide an accurate current premium price.
References
Tips
- The PRICE function and the required data is listed this way: PRICE(settlement, maturity, rate, yield, redemption, frequency [basis]). The spreadsheet help section will explain the elements if you have problems.
- The dates and percentage rates should be set up with the "Format Cells" command.
- Do not forget the "=" sign in front of the PRICE function.
Warnings
- The quick price calculation will provide a price that is significantly higher than the market value for all but the longest term bonds. The spreadsheet uses internal rate of return calculations to provide an accurate current premium price.
Writer Bio
Tim Plaehn has been writing financial, investment and trading articles and blogs since 2007. His work has appeared online at Seeking Alpha, Marketwatch.com and various other websites. Plaehn has a bachelor's degree in mathematics from the U.S. Air Force Academy.