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
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | RATE(nper, pmt, pv, [fv], [type], [guess]) |
| Google Sheets | ✔ Built in | RATE(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. |
| 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. | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
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
| Argument | What it means |
|---|---|
nper | Total number of payments, e.g. 5*12. |
pmt | Payment per period, negative when you pay it. |
pv | Loan amount or present value, positive when you receive it. |
[fv] optional | Balance after the last payment. Defaults to 0. |
[type] optional | 0 (default) = payments at period end, 1 = at the beginning. |
[guess] optional | Starting point for the iteration. Defaults to 10%; change it only if RATE fails to converge. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=RATE(5*12, -400, 20000)*12 | 0.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%).