Ashcombe Joinery financed five vans, £120,000 over 48 months, and took the dealer's 4.9% over the bank's 7.9%. The 4.9% was a flat rate, and the vans cost £3,906.24 more in interest than the loan they turned down.
| Quote | Quoted rate | Monthly payment | Total interest |
|---|---|---|---|
| Dealer finance | 4.9% flat | £2,990.00 | £23,520.00 |
| Bank loan | 7.9% APR | £2,908.62 | £19,613.76 |
Scope.
RATE,PMT,EFFECTandMINwork in every version of Excel.XLOOKUPandSORTBYneed Excel 2021 or Microsoft 365.In the UK and the EU an APR compounds the monthly rate, which is what section 4 does. A US APR is the monthly rate times 12, the first column of the table in section 4.
01A Flat Rate Charges Interest on the Whole Loan
A flat rate is worked out once, on the amount borrowed, for every year of the term. It ignores the repayments completely. For the dealer's quote:
=120000*4.9%*4 → 23,520.00 interest
=(120000+23520)/48 → 2,990.00 monthly payment
Each payment repays part of the £120,000, so the balance falls every month. By the last year Ashcombe owed less than £36,000, and was still paying interest as if it owed £120,000.
On average over the term the balance is about half the loan. Interest on twice the money you actually owe is, roughly, twice the rate you were quoted.
Scenario: In an empty sheet, enter =120000*4.9%*4 in H1 and =(120000+H1)/48 in H2. Check that H2 shows 2,990, the dealer's monthly payment.
02The Sheet Ranked Quotes on the Headline Rate
The comparison sheet held each lender's rate in column B and picked the winner with:
=XLOOKUP(MIN(B2:B5),B2:B5,A2:A5)
Ashcombe Joinery Van Finance, Four Quotes for £120,000 Over 48 Months
Four quotes to finance five vans, £120,000 repaid monthly over 48 months, in A2:F5 of the Quotes sheet. B is the rate as each lender quoted it and C whether that rate is flat or an APR. D is the monthly payment, E the total interest, =D2*48-120000, and F the APR worked back from the payment with =(1+RATE(48,-D2,120000))^12-1.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
It returned Dealer finance. Column C said Flat for the two lowest rates and APR for the other two, but MIN compares numbers, not what they mean.
The bank's payment comes from its APR, compounded back to a monthly rate:
=PMT((1+7.9%)^(1/12)-1,48,-120000) → 2,908.62
Ranked by the interest each quote actually charges, the order turns over:
| Quote | Quoted rate | Total interest | Rank on cost |
|---|---|---|---|
| Dealer finance | 4.9% flat | £23,520.00 | 3 |
| Finance broker | 5.5% flat | £26,400.00 | 4 |
| Bank loan | 7.9% APR | £19,613.76 | 1 |
| Credit union | 8.9% APR | £22,127.52 | 2 |
The two flat quotes were first and second on the headline, and third and fourth on cost.
Scenario: Enter the four monthly payments in D2:D5 and =D2*48-120000 in E2, filled to E5. Check that E4, the bank, is the lowest at 19,613.76.
03RATE Finds the Rate the Payments Imply
RATE works backwards from a loan: given the number of payments, the payment and the amount borrowed, it returns the interest rate per period. It does not care how the lender described the rate.
=RATE(48,-2990,120000) → 0.756% per month
=RATE(48,2990,120000) → #NUM!
The payment is negative because it goes out, and the loan positive because it comes in. With both the same sign there is no rate that balances them, and RATE returns #NUM!.
0.756% looks small because it is a month's interest. It needs annualising before it can sit beside a quoted rate.
Scenario: Enter =RATE(48,-D2,120000) in G2 and format it as a percentage with three decimals. Check that it shows 0.756%, then flip the sign of D2 and watch it turn to #NUM!.
04Annualise It the Way an APR Does
Multiplying the monthly rate by 12 gives the nominal annual rate. A UK or EU APR compounds it instead, because each month's interest is charged on the last month's:
=(1+RATE(48,-D2,120000))^12-1
=EFFECT(RATE(48,-D2,120000)*12,12) returns the same figure. For the four quotes:
| Quote | Quoted rate | RATE × 12 | APR |
|---|---|---|---|
| Dealer finance | 4.9% flat | 9.07% | 9.45% |
| Finance broker | 5.5% flat | 10.11% | 10.60% |
| Bank loan | 7.9% APR | 7.63% | 7.90% |
| Credit union | 8.9% APR | 8.56% | 8.90% |
The bank and the credit union come back at exactly their own APRs. That is the test of the formula: a quote that is already an APR should survive the round trip unchanged.
Scenario: Enter =(1+RATE(48,-D2,120000))^12-1 in F2 and fill it to F5. Check that F4 shows 7.90%, the bank's own APR, and F2 shows 9.45%.
05Rank the Quotes on the APR Column
Point the same lookup at column F instead of column B. One letter changes the answer:
=XLOOKUP(MIN(F2:F5),F2:F5,A2:A5) → Bank loan
=SORTBY(A2:F5,F2:F5)
SORTBY lists the four quotes cheapest first: bank, credit union, dealer, broker. The dealer's payment was £81.38 a month higher than the bank's, and 48 of those are £3,906.24.
An APR comparison is fair only for the same amount over the same term. For a 36-month quote against a 48-month one, compare the total interest in column E as well.
Scenario: Enter =SORTBY(A2:F5,F2:F5) in H2. Check that Bank loan comes first and Finance broker last, the reverse of the order in column B.
06Fees and Balloons Go Into RATE Too
An arrangement fee paid on day one means you receive less than you borrow. Take it off the amount in RATE, because that is the money you actually got.
Had the dealer charged a £995 documentation fee up front:
=(1+RATE(48,-2990,120000-995))^12-1 → 9.93%
A balloon payment due with the last instalment goes in the fourth argument, negative because it goes out: RATE(48,-D2,120000,-balloon). Leave either one out and the rate looks cheaper than it is.
Scenario: Enter =(1+RATE(48,-3050,120000-995))^12-1 for the broker with the same fee. Check that it returns about 11.08%, against 10.60% without it.
07A Flat Rate Is About Half the True Rate
The ratio barely moves with the term. For the same 4.9% flat, every term from one year to five comes out between 9.30% and 9.54% APR:
| Term | Monthly payment | APR | APR ÷ flat |
|---|---|---|---|
| 12 months | £10,490.00 | 9.30% | 1.90 |
| 24 months | £5,490.00 | 9.54% | 1.95 |
| 36 months | £3,823.33 | 9.52% | 1.94 |
| 48 months | £2,990.00 | 9.45% | 1.93 |
| 60 months | £2,490.00 | 9.36% | 1.91 |
So doubling a flat rate is a fair first guess, and RATE is the answer. It also runs the other way: the bank's £19,613.76 of interest is =19613.76/120000/4, 4.09% flat.
Scenario: Enter =(1+RATE(24,-5490,120000))^12-1 for a two-year term at 4.9% flat. Check that it returns 9.54%, nearly twice the quoted rate.
08Five Traps
- Comparing a flat rate with an APR. They measure different things. Turn every quote into an APR first.
- A positive payment in
RATE. With the loan and the payment the same sign,RATEreturns#NUM!. - Reading the monthly rate as annual.
RATEreturns the rate per payment period: 0.756% a month, not a year. - Calling RATE × 12 an APR. In the UK and EU it understates: 9.07% against 9.45% for the dealer.
- Leaving fees out. A fee paid up front comes off the amount borrowed, or the rate looks lower than it is.
09Four Exercises
- Reproduce it. Work out the broker's flat interest and payment, and confirm £26,400 and £3,050.
- Reverse it. Use
RATEon the broker's payment, and confirm 10.60% APR. - Add the fee. Take £995 off the amount, and confirm the broker rises to about 11.08%.
- Translate back. Express the credit union's interest as a flat rate, and confirm 4.61%.
What to Take Away
Every number on the comparison sheet was right. Two of them were flat rates and two were APRs, and the cheapest-looking quote charged interest on money Ashcombe had already paid back.
Before comparing loans, turn every monthly payment into an APR with =(1+RATE(48,-D2,120000))^12-1, and rank on that column.