RATE on monthly payments, as a yearly rate
RATE answers per period; multiply by 12 for the figure on the advert.
Task:
A dealer offers a 20,000 car for 60 monthly payments of 400. In B5 give the annual interest rate the deal really charges.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of RATE
0/3The same formula in the other shapes it takes at work.
- RATE Function
Calculate the interest rate per period of an annuity using the RATE function.
CoreAdvanced - RATE on monthly payments, as a yearly rate
RATE answers per period; multiply by 12 for the figure on the advert.
Advanced - RATE for the growth of a single investment
No payments in between: start value, end value, years.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | |
|---|---|---|
| 1 | Payments | 60 |
| 2 | Monthly payment | 400 |
| 3 | Loan | 20000 |
| 4 | ||
| 5 | Annual rate |
What this exercise teachesMay contain the answer
"Only 400 a month" hides the price of the credit: 60 × 400 is 24,000 for a 20,000 car. RATE recovers the interest that makes those numbers balance, about 7.4% a year — the figure to compare against a bank loan.