Exceljet

Quick, clean, and to the point

Annuity solve for interest rate

Excel formula: Annuity solve for interest rate
Generic formula 
=RATE(nper,pmt,pv,fv)
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)
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.

Author 
Dave Bruns
Related formulas 
Excel formula: Future value of annuity
Future value of annuity
The 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. An annuity is a series...
Excel formula: Future value vs. Present value
Future value vs. Present value
The FV function is a financial function that returns the future value of an investment, given periodic, constant payments with a constant interest rate. The PV function returns the present value of an investment. You can use the PV function to get...
Excel formula: Present value of annuity
Present value of annuity
The 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 interest...
Excel formula: Payment for annuity
Payment for annuity
The PMT function is a financial function that returns the periodic payment for a loan. You can use the PMT function to figure out payments for a loan, given the loan amount, number of periods, and interest rate. An annuity is a series of equal cash...
Related functions 
Excel RATE function
Excel 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...

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.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.