Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .