Spreadsheet formulas explained, side by side

RATE Function in Excel, Google Sheets, Airtable & Notion

RATE works out the interest rate per period implied by a number of payments, the payment amount and the loan or investment value. It is found by trial and error (iteration), not a direct formula.

Syntax in each app

AppSupportSyntax & notes
Excel✔ Built inRATE(nper, pmt, pv, [fv], [type], [guess])
Google Sheets✔ Built inRATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess])
Airtable✘ Not available—
Airtable has no RATE function and formulas cannot iterate, so there is no exact formula. Calculate the rate in Excel or Google Sheets and store it in a number field. For a single lump sum (no payments) the rate does have a direct formula: POWER(fv / pv, 1 / nper) - 1.
Notion✘ Not available—
Notion has no rate() function and no practical way to iterate. Store the rate as a number property calculated elsewhere. For a single lump sum, pow(fv / pv, 1 / nper) - 1 gives the growth rate per period.
Try RATE with your own values
Negative: money you pay
Result (as Excel & Google Sheets return it)
0.0742 7.42% when the cell is formatted as a percentage
Your formula, ready to paste
Excel=RATE(5*12, -400, 20000)*12 open excel.new ↗
Google Sheets=RATE(5*12, -400, 20000)*12 open sheets.new ↗
Airtable✘ No equivalent. Airtable has no RATE function and formulas cannot iterate, so there is no exact formula. Calculate the rate in Excel or Google Sheets and store it in a number field. For a single lump sum (no payments) the rate does have a direct formula: POWER(fv / pv, 1 / nper) - 1.
Notion✘ No equivalent. Notion has no rate() function and no practical way to iterate. Store the rate as a number property calculated elsewhere. For a single lump sum, pow(fv / pv, 1 / nper) - 1 gives the growth rate per period.

RATE returns the rate per period; the formula multiplies it by the payments per year to give the nominal annual rate. The result is a decimal: 0.0742 means 7.42% (format the cell as a percentage).

Arguments

ArgumentWhat it means
nperTotal number of payments, e.g. 5*12.
pmtPayment per period, negative when you pay it.
pvLoan amount or present value, positive when you receive it.
[fv] optionalBalance after the last payment. Defaults to 0.
[type] optional0 (default) = payments at period end, 1 = at the beginning.
[guess] optionalStarting point for the iteration. Defaults to 10%; change it only if RATE fails to converge.

Examples

FormulaResultWhyCopy
=RATE(5*12, -400, 20000)*120.0742 (7.42%)Annual rate on a $20,000 loan repaid at $400 a month for 5 years.
=RATE(36, -300, 9000)0.0102 (1.02%)Monthly rate; multiply by 12 for the annual rate.
=RATE(10, 0, -1000, 2000)0.0718 (7.18%)Yearly growth needed to double $1,000 in 10 years.

Examples use Excel / Google Sheets syntax unless noted.

How RATE works

RATE answers "what interest rate am I really paying?" from the numbers you can see on a loan or lease offer: the amount, the payment and the term. Because the rate cannot be isolated algebraically, Excel and Google Sheets solve for it by iteration, starting from the guess (10% by default). If they cannot converge within 20 tries they return #NUM!; this usually means pv and pmt have the same sign, or the guess is far off.

The result is per period. With monthly payments, multiply by 12 for the nominal annual rate, as in the first example. That nominal figure is close to, but not the same as, an APR that includes fees, or the effective annual rate (1 + monthly)^12 − 1. Excel formats RATE results as a percentage automatically; the underlying value is a decimal such as 0.0742.

Airtable and Notion have no RATE and cannot run an iterative search, so there is no exact formula for loans with regular payments. Compute it in a spreadsheet and store the number. The one easy case is a single lump sum growing to a future value, where the rate per period is (fv / pv)1/n − 1, written with POWER in Airtable or pow in Notion.

Frequently Asked Questions

Why does RATE return #NUM!?
Check the signs: the loan amount and the payments must have opposite signs. If they do, supply a guess closer to the expected rate, e.g. =RATE(60, -400, 20000, 0, 0, 1%).

Part of the Financial functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.