Bond valuation example

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:

=-PV(C6/C8,C7*C8,C5/C8*C4,C4)

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

Between coupon payment dates

In the example above, it is relatively straightforward to find the value of a bond on a coupon payment date with the PV function. Finding the value of a bond between coupon payment dates is more complex because interest does not compound between payments. The PRICE function can be used to calculate the "clean price" of a bond on any date.

More detail

For a more detailed explanation of bond valuation, see this article on tvmcalcs.com.