Skip to content

Excel function · Financial

Excel RATE function

RATE finds the interest rate per period of a loan or an instalment sale when you know the amount, the payment and the number of payments: the monthly rate of an offer of 20,000 repaid at 620 a month for 36 months, for example.

In Turkish Excel: FAİZ_ORANI

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

English Excel=RATE(nper, pmt, pv, [fv], [type], [guess])
Turkish Excel=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

ArgumentRequiredDescription
nperRequiredThe total number of payments.
pmtRequiredThe payment made each period; negative because it leaves you.
pvRequiredThe loan amount or cash price; positive as value you receive.
fvOptionalThe amount left after the last payment; leave it out for a loan.
typeOptional0 or omitted: payments at the end of each period. 1: at the start, for sales where the first instalment is due immediately.
guessOptionalThe 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.

E2fx=RATE(D2,C2,B2)
Compare the interest rate of three loan offers: example table
ABCDEFG
1OfferLoan AmountMonthly PaymentMonthsMonthly RateAnnual (Simple)Annual (Compound)
2Lender A20,000.00-620.00360.61%7.27%7.52%
3Lender B20,000.00-500.00480.77%9.24%9.64%
4Lender C20,000.00-900.00240.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.

E2fx=RATE(D2,C2,B2)
The monthly interest hidden in an instalment price: example table
ABCDE
1ItemCash PriceInstalmentInstalmentsMonthly Rate
2Fridge1,600.00-150.00121.86%
3Air conditioner950.00-110.0090.83%
4Laptop1,150.00-200.0061.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-1 for 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

Type this in Nexell

Add columns to my loan offers table showing the monthly rate and its annual equivalent from the amount, payment and term

Convert to table

Bring 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.

Related functions

All financial functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free