RATE function
RATE solves for the interest rate per period hidden in a loan or annuity. For monthly payments, multiply the result by 12 to get a nominal annual rate.
Example
Read it as a sentence: find the rate per period on an 8000 loan paid off in 48 periods with 200 paid each period, which gives 0.00770147248820137.
A
B
C
D
1
0.00770147248820137
2
3
Syntax
RATE(nper, pmt, pv, [fv], [type], [guess])
Arguments in [square brackets] are optional.
-
nper(required) - Total number of periods.
-
pmt(required) - Payment each period.
-
pv(required) - Present value.
-
fv(optional) - Future value; 0 if omitted.
-
type(optional) - 0 = payments at period end (default), 1 = at start.
-
guess(optional) - Starting estimate; 0.1 if omitted.
Returns: Interest rate per period.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=RATE(60,-377.42,20000) | 0% | Loan |
=RATE(60,-377.42,20000,0,0,0.01) | 0% | With guess |
=RATE(10,0,-1000,2000) | 7% | Pmt 0 fv |
=RATE(12,-100,1100,0,1) | 2% | Type 1 |
=RATE("60","-377.42","20000") | 0% | Text number |
=RATE({12,24},-100,1000) | 3% | Spill |
=RATE(10,100,1000) | #NUM! | No solution |
=RATE(0,-100,1000) | #NUM! | Nper 0 |
Watch out for
- Returns a per-period rate, so annualise it yourself.
- Can return #NUM! if it does not converge; try another guess.
Where it works
- Excel
- RATE is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .