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 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.
To get the present value of an annuity, you can use the PV function. In the example shown, the formula in C9 is: = PV ( C5 , C6 , C4 , 0 , 0 ) Explanation An annuity is a series of equal cash flows, spaced equally in time. In this example, an...
To get the present value of an annuity, you can use the PV function. In the example shown, the formula in C7 is: = FV ( C5 , C6 , - C4 , 0 , 0 ) Explanation An annuity is a series of equal cash flows, spaced equally in time. In this example, a $5000...
This simple example shows how present value and future value are related. In the example shown, Years, Compounding periods, and Interest rate are linked in columns C and F like this: F5 = C9 F6 = C6 F7 = C7 F8 = C8 The formula to calculate future...
The Excel PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant...
The Excel FV function is a financial function that returns the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate.
The Excel PRICE function returns the price per $100 face value of a security that pays periodic interest.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.