Annual rate of a loan
| A | B | C | |
|---|---|---|---|
| 1 | Months | Payment | Loan |
| 2 | 36 | 350 | 10000 |
Formula: E2: =ROUND(RATE(A2,-B2,C2)*12,4)
Result: 0.1568
The monthly rate is multiplied by 12 to quote an annual rate of about 15.7%.
Google Sheets function
RATE in Google Sheets
RATE with worked examples you can test in the live grid. The argument details are in the Excel guide.
In Google Sheets
The arguments and common errors are covered in the RATE Excel guide; check the function help in Google Sheets for its own argument list.
Worked examples in Google Sheets
Compound annual growth
| A | B | C | |
|---|---|---|---|
| 1 | Years | Start | End |
| 2 | 5 | 1000 | 1800 |
Formula: E2: =ROUND(RATE(A2,0,-B2,C2),4)
Result: 0.1247
With no payments, RATE gives the yearly growth rate that turns 1,000 into 1,800 over five years.
Annual rate from a 48-month plan
| A | B | C | |
|---|---|---|---|
| 1 | Months | Payment | Loan |
| 2 | 48 | 300 | 12000 |
Formula: E2: =ROUND(RATE(A2,-B2,C2)*12,4)
Result: 0.0924
The result is about 9.2% a year, found by iteration.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in D2: =RATE(24,-500,10000)*12
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Cash flow | Result | |
| 2 | 0 | -10000 | 0.181570126828 | |
| 3 | 1 | 3000 | ||
| 4 | 2 | 4000 | ||
| 5 | 3 | 4500 | ||
| 6 | 4 | 2500 |
Result in D2: 0.181570126828
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.