Annuity solve for interest rate

Summary

To solve for an annuity interest rate, you can use the RATE function. In the example shown, C9 contains this formula:

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

Generic formula

=RATE(nper,pmt,pv,fv)

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.

Support Exceljet

Many websites (including Exceljet) have lost over half their traffic in the past year because of the crushing impact of AI on web searches. Exceljet is still at it, publishing free content each week. If you find Exceljet helpful, you can support the site and go ad-free with the Exceljet Lifetime Pass. One payment, no subscription, and ads are gone forever. Plus, you get our Excel Shortcuts video course as a thank you.

Get the Lifetime Pass Already have a Pass? Log in to hide ads.

Was this page helpful?

Author

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.