What it does
RATE solves the PMT calculation in reverse. The result is a rate per period: with monthly payments it is a monthly rate. For an annual figure, multiply by 12 (simple) or convert with (1+rate)^12-1 (compound).
The sign rule is essential here: the loan amount or cash price (value you receive) must be positive and the payment (money you pay) negative. If both have the same sign, there is no solution and you get #NUM!.
The result is found by trial and error; only rarely does the sixth argument need a guess. To find the number of payments use NPER; for the return of irregular flows use IRR.
Syntax
=RATE(nper, pmt, pv, [fv], [type], [guess])=FAİZ_ORANI(dönem_sayısı; devresel_ödeme; bd; [gd]; [tür]; [tahmin])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
nper | Required | The total number of payments. |
pmt | Required | The payment made each period; negative because it leaves you. |
pv | Required | The loan amount or cash price; positive as value you receive. |
fv | Optional | The amount left after the last payment; leave it out for a loan. |
type | Optional | 0 or omitted: payments at the end of each period. 1: at the start, for sales where the first instalment is due immediately. |
guess | Optional | The rate to start the search from; 10% if omitted. Change it if no result is found. |
Examples
1Compare the interest rate of three loan offers
Three lenders offer the same amount with different payments and terms. The loan amount is positive and the payment negative. Column E gives the monthly rate, F the simple annual rate and G the compound annual rate.
=RATE(D2,C2,B2)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Offer | Loan Amount | Monthly Payment | Months | Monthly Rate | Annual (Simple) | Annual (Compound) |
| 2 | Lender A | 20,000.00 | -620.00 | 36 | 0.61% | 7.27% | 7.52% |
| 3 | Lender B | 20,000.00 | -500.00 | 48 | 0.77% | 9.24% | 9.64% |
| 4 | Lender C | 20,000.00 | -900.00 | 24 | 0.63% | 7.50% | 7.76% |
- E2 (filled down to E4)
=RATE(D2,C2,B2) - F2 (filled down to F4)
=E2*12 - G2 (filled down to G4)
=(1+E2)^12-1
Lender A's monthly rate is 0.61%; times 12 it is 7.27%, or 7.52% compounded. Lender B has the lowest payment, but its monthly rate is 0.77%: compare offers by rate, not by payment.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2The monthly interest hidden in an instalment price
The cash price is pv, positive, and the instalment is negative. The result shows the monthly rate you are effectively borrowing at when you buy in instalments.
=RATE(D2,C2,B2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Cash Price | Instalment | Instalments | Monthly Rate |
| 2 | Fridge | 1,600.00 | -150.00 | 12 | 1.86% |
| 3 | Air conditioner | 950.00 | -110.00 | 9 | 0.83% |
| 4 | Laptop | 1,150.00 | -200.00 | 6 | 1.23% |
- E2 (filled down to E4)
=RATE(D2,C2,B2)
Buying the fridge in instalments is the same as borrowing at 1.86% a month; for the air conditioner the rate is 0.83%. Compare it with the monthly return your money could earn elsewhere.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #NUM!
Cause: The payment and the amount have the same sign, or Excel could not reach a result in its attempts.
Fix: Enter the amount as positive and the payment as negative; give a guess if needed.
- Monthly rate read as annual
Cause: The rate from monthly payments was read as an annual rate.
Fix: Multiply by 12 for a simple annual figure, or use
(1+rate)^12-1for the compound annual rate.- #VALUE!
Cause: One of the arguments is text that cannot be read as a number.
Fix: Make sure the cells really hold numbers.
Good to know
- To find the hidden interest in a sale with a known cash price and instalments, give the cash price as pv and the instalment as a negative pmt.
- When comparing offers, look at the rate in the same unit, not at the payment; a longer offer can have a lower payment but a higher rate.
In Nexell
Get Nexell to do it from a sentence
Add columns to my loan offers table showing the monthly rate and its annual equivalent from the amount, payment and term
Convert to tableBring your offers table into the tool and type this. Nexell adds the rate columns with real Excel formulas and shows them in the preview; in the downloaded file, an offer's rate changes when its payment does.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I work out the interest rate of an instalment purchase?
=RATE(instalments,-instalment,cash_price) gives the monthly rate. For example, for an item priced 1,600 in cash or 12 × 150 in instalments, =RATE(12,-150,1600). For the compound annual equivalent, use =(1+RATE(12,-150,1600))^12-1.