RATE
Work out the interest rate per period from the number of payments, the payment and the amount — the rate a loan is really charging.
Syntax
RATE(nper, pmt, pv, [fv], [type], [guess])| Argument | Required | Description |
|---|---|---|
nper | Required | The number of payments. |
pmt | Required | The payment each period, negative for money paid out. |
pv | Required | The amount borrowed or invested now. |
fv | Optional | The balance to leave at the end. Defaults to 0. |
type | Optional | 0 (the default) for payments at the end of each period, 1 for the start. |
guess | Optional | A starting estimate. Defaults to 10%. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=RATE(48,-200,8000) | 0.01 | A 4-year loan of 8,000 at 200 a month charges about 0.77% a month. |
=RATE(48,-200,8000)*12 | 0.09 | About 9.24% a year. |
=RATE(5,0,-1000,1500) | 0.08 | 1,000 growing to 1,500 in five years is about 8.4% a year. |
=RATE(360,PMT(0.05/12,360,200000),200000)*12 | 0.05 | RATE undoes PMT. |
What a loan really charges
Given the payment, the term and the amount, RATE recovers the interest rate.
It is the check on a quoted payment plan — a dealer’s monthly figure, a
lease, a buy-now-pay-later schedule — and the answer is often higher than the
headline rate suggests.
Multiply by 12 for an annual rate from monthly payments, as in the examples.
Signs
As with PMT, money you receive is positive and money you
pay is negative. A loan received as a positive pv with a positive payment has
no answer, and returns #NUM!.
Growth rate of a lump sum
With no payments, RATE is the compound growth rate between two amounts —
RATE(5,0,-1000,1500) is the annual growth from 1,000 to 1,500 over five
years, the same as POWER(1500/1000,1/5)-1.
Excel compatibility
Matches Excel, including the sign convention and the optional fv, type and guess arguments.