Microsoft KB Archive/37993

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Two-Step Method to Calculate Bond Price in Excel

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