Syntax
RATE(nper, pmt, pv, [fv], [type], [guess])
| Parameter | Description |
|---|---|
| nper | Parameter of the RATE function. |
| pmt | Parameter of the RATE function. |
| pv | Parameter of the RATE function. |
| [fv] | (Optional.) Parameter of the RATE function. |
| [type] | (Optional.) Parameter of the RATE function. |
| [guess] | (Optional.) Parameter of the RATE function. |
Examples
What rate am I paying on this car loan?
Formula
=RATE(60, -450, 22000)*12
6.68%. If you pay $450/month for 60 months on a $22,000 car, your annual interest rate is about 6.68%. Multiply by 12 to annualize.
Investment return rate
Formula
=RATE(120, -200, 0, 40000)*12
5.74%. To go from $0 to $40,000 with $200/month over 10 years, you need an annual return of about 5.74%.
Mortgage rate from payment
Formula
=RATE(360, -1900, 300000)*12
5.63%. A $1,900/month payment on a $300K 30-year mortgage implies an annual rate of about 5.63%.
Common Errors
#NUM!
RATE cannot converge on a solution after 100 iterations. This usually means the inputs are inconsistent (e.g., payment too low to ever reach the future value).
#VALUE!
One or more arguments is non-numeric.
Tips
Multiply by periods per year
RATE returns the rate per period. For monthly payments, multiply by 12 to get the annual rate. Don't forget this step — it's a common mistake.
Use the guess parameter
If RATE returns #NUM!, provide a guess (e.g., 0.1 for 10%) to help it converge. The default guess is 10%, which doesn't work for all scenarios.
Compare loan offers
When dealers quote different terms (different down payments, trade-in values, months), use RATE on each offer to compare the true interest rates side by side.
Try RATE in Viztab
Import your data and use RATE with 370+ other formulas. No signup required.
Open Viztab