Microsoft KB Archive/37993

From BetaArchive Wiki

Two-Step Method to Calculate Bond Price in Excel



The information in this article applies to:


  • Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
  • Microsoft Excel for Windows, version 7.0





SUMMARY

It is possible to calculate the price of a bond in just two steps, while still using the "long" method of calculation. This method of price calculation is preferred to the shorter method of present value tables, due to the greater accuracy of the long method. The accuracy becomes increasingly important as the face value of the bond grows and time to maturity increases.

To do this calculation, do the following:


  1. From the Data menu, choose Series and enter the number of periods until maturity is in sequential order (that is, A1 is 1, A2 is 2, and so on).


Microsoft Excel 5.0 and later




From the Edit menu, choose Fill then Series, and enter the number of periods until maturity is in sequential order (that is, A1 is 1, A2 is 2, and so on).

  1. In another cell (that is, cell C1), enter the following array formula:


{=SUM(I/(1+Y)^A1:A2)}

Please note that the curly braces are not typed, they are the result of entering the formula with CTRL+SHIFT+ENTER. This makes it an array formula. I = interest payments, Y = yield to maturity (required rate of return), n = total number of periods.

  1. In still another cell (that is, cell C2), enter the following


=C1+(Pn/(1+Y)^n)

where Pn= Principle payment at maturity.

This cell will return the present value (the most you should be willing to pay for the bond based on your required investment return "Y") of a bond that matures at time n.

Additional query words:

Keywords :
Version :
Platform :
Issue type :
Technology :


Last Reviewed: April 12, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.