Excel RATE Function: Find the Interest Rate Behind a Deal
Works out the interest rate implied by a payment, a term and an amount.
RATE runs the loan calculation backwards. You know what you are borrowing, what you are paying each month and for how long — RATE tells you what interest rate that actually amounts to. It is how you check whether a finance offer quoting "only £250 a month" is competitive.
It returns the rate *per period*, which is the part people miss. Feed it monthly payments and you get a monthly rate; multiply by 12 for the annual figure that everyone quotes. Forgetting that step makes a 7% loan look like a 0.58% one.
Unlike the rest of the family, RATE cannot solve its equation directly — it iterates towards an answer. That is why it has a guess argument and why it occasionally gives up with #NUM! on unusual inputs. The default guess of 10% works for almost everything; supplying your own helps only in extreme cases.
Syntax
=RATE(nper, pmt, pv, [fv], [type], [guess])Arguments
nperRequired- The total number of payments.
pmtRequired- The payment per period, entered negative if pv is positive. The signs must oppose or the equation has no solution.
pvRequired- The present value — what was borrowed or invested.
fvOptional- The balance remaining at the end. Defaults to 0, a loan fully repaid.
guessOptional- A starting point for the iteration. Defaults to 10%; only worth supplying when RATE returns #NUM!.
The example data
Headers in row 1, data in A2:E5. Rates are annual.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Plan | Deposit | Annual rate | Years | Target |
| 2 | Reserve fund | 500 | 0.045 | 10 | 75000 |
| 3 | Equipment pot | 250 | 0.032 | 5 | 16000 |
| 4 | Expansion | 1200 | 0.061 | 8 | 150000 |
Worked examples
=RATE(D2*12, -B2, 48000) * 12Result: 0.0453
£500 a month for ten years against £48,000 borrowed works out at about 4.53% a year. The *12 converts the monthly rate.
=RATE(D2*12, -B2, 48000)Result: 0.0038
The same calculation without annualising. This is the monthly rate, and quoting it as the interest rate would be badly wrong.
=RATE(D3*12, -B3, 13500) * 12Result: 0.0396
The smaller plan, again annualised.
=RATE(D2, 0, -30000, E2) Result: 0.0959
An investment rather than a loan: what annual return turns £30,000 into £75,000 over ten years with no further payments.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#NUM!
Why it happens: RATE could not converge in twenty iterations, usually because pmt and pv share a sign so no rate can satisfy the equation.
How to fix it: Check the signs oppose — if pv is positive, pmt must be negative. If they do, try a guess closer to the expected answer.
The rate looks far too small
Why it happens: It is the rate per period and has not been annualised.
How to fix it: Multiply by the number of periods per year: *12 for monthly, *4 for quarterly.
Result shows as 0.05
Why it happens: The cell is formatted as General rather than as a percentage.
How to fix it: Format it as a percentage. 0.045 and 4.5% are the same number.
#VALUE!
Why it happens: An argument is text.
How to fix it: Check every input is numeric, particularly any figure pasted from a document.
Tips worth knowing
- Always multiply the result by the periods per year before quoting it. This is the mistake that makes RATE answers look wrong.
- RATE ignores fees, so it gives the nominal rate rather than an APR. Add arrangement fees to pv to approximate one.
- For irregular cash flows, IRR is the equivalent and takes a range of amounts.
- If RATE returns #NUM!, check the signs before reaching for the guess argument — signs are the cause far more often.
Frequently asked questions
Why does RATE return such a small number?
It returns the rate for one period. With monthly payments that is a monthly rate, so 0.0038 means 0.38% a month. Multiply by 12 to get the 4.5% annual figure people actually quote.
Why does RATE give #NUM!?
Almost always because the payment and the present value have the same sign, which makes the equation unsolvable — one has to be money in and the other money out. Failing that, RATE gave up after twenty iterations, and supplying a guess closer to the real answer usually fixes it.
Does RATE give me the APR?
No. It gives the nominal rate implied by the payments, ignoring fees and charges. To approximate an APR, add any arrangement fee to the amount borrowed before passing it as pv — the higher effective borrowing shows up as a higher rate.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.