Explanation

An annuity is a series of equal cash flows, spaced equally in time. The goal in this example is to have $100,000 at the end of 10 years, with an annual payment of $7,500 made at the end of each year. What interest rate is required?

To solve for the interest rate, the RATE function is configured like this in cell C9:

=RATE(C7,-C6,C4,C5)

nper - from cell C7, 10. pmt - from cell -C6, -7500 pv - from cell C4, 0. fv - from cell C5, 100000

With this information, the RATE function returns 0.0624. When a percentage number format is applied, the result displays as 6.24%. Note payment is negative because it represents a cash outflow.

Explanation

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 - 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 .