Excel formula: Annuity solve for interest rate

Generic formula

=RATE(nper,pmt,pv,fv)

Explanation

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:

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

With this information, the RATE function returns 0.0624. Note payment is negative because it represents a cash outflow.

Author

Dave Bruns

Related formulas

Future value of annuity

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

Future value vs. Present value

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

Present value of annuity

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

Payment for annuity

To solve for an annuity payment, you can use the PMT function. In the example shown C9 contains this formula: = PMT ( C6 , C7 , C4 , C5 , 0 ) Explanation An annuity is a series of equal cash flows, spaced equally in time. The goal in this example is...

Related functions

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.