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 95, 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:
- 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).
- 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.
- 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.
|