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. 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 - C7C8 = 32 = 6 pmt - C5/C8C4 = 7%/21000 = 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
