RATE function
RATE works out the interest rate per period from the number of payments, the payment and the amount borrowed or invested. Use it to find the real rate on a loan or lease when you only know the payment, or the annual growth rate of an investment. Multiply a monthly result by 12 for an annual rate.
- Available in
- Every current version
- Category
- Financial functions
- Returns
- The interest rate per period, as a decimal. Format it as a percentage.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=RATE(nper, pmt, pv, [fv], [type], [guess])
| Argument | Required? | What it is |
|---|---|---|
nper | Required | The total number of payment periods. |
pmt | Required | The payment each period, negative for money paid out. Use 0 for a lump sum. |
pv | Required | The amount today: positive for money received, such as a loan. |
fv | Optional | The amount at the end. Defaults to 0. |
type | Optional | 0 or omitted for payments at the end of each period, 1 for the beginning. |
guess | Optional | A starting guess for the rate. Defaults to 10%; change it only if RATE fails to find an answer. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =RATE(B4,-B3,B2) | 0.54% | The monthly rate on the loan |
| D3 | =RATE(B4,-B3,B2)*12 | 6.5% | Times 12 for the annual rate: 6.5% |
| D4 | =RATE(B7,0,-B5,B6) | 8.1% | Yearly growth that turned $10,000 into $16,000 in 6 years |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Input | Value | Result | |
| Amount borrowed | $25,000 | 0.54% | |
| Monthly payment | $489.15 | 6.5% | |
| Payments | 60 | 8.1% | |
| Invested | $10,000 | ||
| Grew to | $16,000 | ||
| Years | 6 |
Practice RATE
Graded instantly, then re-checked on changed data.
What rate is this loan?
B5, calculate the annual interest rate on the loan: B2 borrowed, repaid at B3 a month over the number of payments in B4.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| A rate that looks tiny, like 0.5% | RATE returns the rate per period. For monthly payments, multiply by 12 to get the annual rate. |
| #NUM! | The payment and the loan have the same sign, so there is no rate that works. Make the payment negative and the loan positive. |
| #NUM! on an unusual loan | RATE solves by trial and error and can fail to converge. Supply a guess close to the expected rate, such as 0.01 for 1% a month. |
Tips
- A dealer quoting only the monthly payment? RATE tells you the interest rate you are actually paying.
Questions
How do I find the interest rate on a loan in Excel?
Use =RATE(number_of_payments, -payment, loan_amount)*12 for the annual rate on a monthly loan.
How do I calculate an annual growth rate in Excel?
Use =RATE(years, 0, -start_value, end_value). It returns the compound annual growth rate.
Why does RATE return #NUM!?
Usually the signs are wrong (payment and loan both positive) or RATE couldn't converge. Fix the signs or add a guess argument.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.