RATE Function

Finds the interest rate behind a loan, lease or investment.

Category

Financial

Available in

All versions, Microsoft 365

Returns

An interest rate per period

Want to practice RATE in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

RATE works backwards: given the number of payments, the payment and the amount, it finds the interest rate per period. Excel solves it by trial and error.

The result is per period — multiply a monthly rate by 12 for the annual figure.

Syntax

=RATE(nper, pmt, pv, [fv], [type], [guess])

Arguments

ArgumentRequired?What it does
nperYesThe total number of payment periods.
pmtYesThe payment each period (leave blank or 0 for a lump sum).
pvYesThe present value — the loan amount, or what a series of payments is worth today.
fvNoThe balance you want left after the last payment. Omitted = 0 (a loan paid off in full).
typeNoWhen payments fall: 0 or omitted = end of each period, 1 = start of each period.
guessNoA starting guess for the rate. Omitted = 10%.

Examples

Example 1 — The real rate on an equipment lease

A supplier offers a $30,000 machine for $575 a month over 60 months. What interest rate is built in?

B2fx=RATE(60,-575,30000)
AB
1MeasureRate
2Monthly0.47%
3Annual5.64%

Example 2 — Compound annual growth

Revenue grew from $1.2M to $1.9M in five years. Leave pmt empty.

=RATE(5,,-1200000,1900000) Explain

Result: 9.63% a year. RRI gives the same answer more directly.

Common mistakes

  • #NUM! error. RATE couldn't find an answer in 20 tries. Check the signs (pmt and pv opposite) or give a guess like 0.01.
  • The rate looks tiny. It's per period. Multiply monthly results by 12.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.