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
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
Arguments
| Argument | Required? | What it does |
|---|---|---|
| nper | Yes | The total number of payment periods. |
| pmt | Yes | The payment each period (leave blank or 0 for a lump sum). |
| pv | Yes | The present value — the loan amount, or what a series of payments is worth today. |
| fv | No | The balance you want left after the last payment. Omitted = 0 (a loan paid off in full). |
| type | No | When payments fall: 0 or omitted = end of each period, 1 = start of each period. |
| guess | No | A 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?
| A | B | |
|---|---|---|
| 1 | Measure | Rate |
| 2 | Monthly | 0.47% |
| 3 | Annual | 5.64% |
Example 2 — Compound annual growth
Revenue grew from $1.2M to $1.9M in five years. Leave pmt empty.
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

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.