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.
Related formulas
Future value of annuity
Future value vs. Present value
Present value of annuity
Payment for annuity
Related functions
RATE Function
The Excel RATE function is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate. The RATE function calculates by iteration.
