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 per cent annual coupon rate and current rates are 5 per cent, 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.

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 £52 per year in interest per £650 of face amount (8 per cent coupon) and the current market yield is 7 per cent, calculate 80 divided by 0.07. In this case, a £650 bond has a premium value of £742.80.

Use this premium calculation to get an idea what a premium bond is worth. The longer the maturity, the more accurate the estimate.

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 £650 bond paying £26 twice a year would has a coupon rate of 8 per cent. 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 per cent coupon, 7 per cent current yield and a 30-year maturity from the current date should produce a price of £73.10. This means a £650 face amount bond would have a premium price of £731.0. If the results are accurate, you can now figure the premium value of any bond with an above market coupon rate.

#### Tip

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.

#### Warning

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.

#### Tips and warnings

- 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.
- 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.

### Things you need

- Calculator

Show More