Bond valuation example in Excel
This tutorial show how to calculate the value of a bond on the issue date using the PV function.
In the example shown, the formula in C10 is:
Note: This example assumes that today is the issue date, so the next payment will occur in exactly six months. See note below on finding the value of a bond on any date.
How this formula works
In the example shown, we have a 3-year bond with a face value of $1,000. The coupon rate is 7% so the bond will pay 7% of the $1,000 face value in interest every year, or $70. However, because interest is paid semiannually in two equal payments, there will be 6 coupon payments of $35 each. The $1,000 will be returned at maturity. Finally, the required rate of return (discount rate) is assumed to be 8%.
The value of an asset is the present value of its cash flows. In this example we use the PV function to calculate the present value of the 6 equal payments plus the $1000 repayment that occurs when the bond reaches maturity. The PV function is configured as follows:
The arguments provided to PV are as follows:
rate – C6/C8 = 8%/2 = 4%
nper – C7*C8 = 3*2 = 6
pmt – C5/C8*C4 = 7%/2*1000 = 35
fv – 1000
The PV function returns -973.79. To get positive dollars, we use a negative sign before the PV function to get final result of $973.79