Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All functions

RATE

Finance

Back the interest rate out of a deal you've already been quoted — the honest way to compare financing.

Difficulty

Good

Excel file

1What is it?

A vendor quotes a price, a term, and a monthly payment, and never quotes a rate. RATE solves the same equation as PMT the other way round and hands you the rate that's actually being charged, which is usually the only way to compare that offer against drawing on a line of credit. It works by iteration rather than a formula, so it can occasionally fail to land — and it returns the rate PER PERIOD, so a monthly deal needs multiplying by 12 before anyone quotes it as an annual number.

2What it looks like

RATE(nper, pmt, pv, [fv], [type], [guess])

nper: Total number of payments.
pmt: The payment each period, negative for money leaving.
pv: The amount financed today, positive.
[fv]: Anything owed at the end — a balloon or residual. 0 by default.
[type]: 0 (default) end of period, 1 beginning.
[guess]: A starting estimate, 10% by default. Supply one when RATE returns #NUM!.

3When you use it

Convert 'twelve payments of $950 on $10,000' into a rate you can argue with.
Compare equipment financing against a line of credit on the same basis.
Check what a customer payment plan is really costing you to offer.

4See it in action

Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.

A vendor quotes a term and a payment but never a rate. This is the rate.

=RATE(B3, -B4, B2) * 12Per month: 1.02%

	A	B	C
1	Input	Value	Result
2	Amount financed		Per month: 1.02%
3	Months		Annualised: 12.2%
4	Payment / month		Total paid: $18,000 on $15,000 borrowed

The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.

5Common errors

#NUM!RATE iterated 20 times without converging, or every cash flow points the same way.

Fix: Make pmt and pv opposite signs, then pass a guess near the answer — 0.01 for a monthly rate rather than the 10% default.

The rate looks impossibly smallYou read the monthly rate as an annual one.

Fix: Multiply by 12 (or by the number of periods in a year). RATE always answers per period.

Off by the residualA balloon or trade-in value was left out of fv.

Fix: Put the amount still owed at the end into fv, or the rate will absorb it and read high.

6Better functions & alternatives

PMT — The same relationship solved for the payment when you already know the rate.
IRR — Use IRR when the payments aren't level — RATE assumes every period is identical.
EFFECT — Converts a nominal annual rate into the effective one once compounding is counted.

Want RATE already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.