01 / 10A Flat Rate Charges Interest on the Whole Loan

Financial FunctionsRATEFormulas

Flat Rate vs APR in Excel: Find the True Rate With RATE

We Took the 4.9% Van Finance Over the 7.9% Bank Loan and Paid £3,906 More, Because 4.9% Was a Flat Rate

18 Nov 20267 min read

UsesRATEPMTXLOOKUPMIN, MAX

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.

QuoteQuoted rateMonthly paymentTotal interest
Dealer finance4.9% flat£2,990.00£23,520.00
Bank loan7.9% APR£2,908.62£19,613.76

Scope. RATE, PMT, EFFECT and MIN work in every version of Excel. XLOOKUP and SORTBY need 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.

Try it in the grid

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.

ABCDEF
1
Quote
Quoted rate
Basis
Monthly payment
Total interest
APR from RATE
2
Dealer finance
4.9%
Flat
2990
23520
9.45%
3
Finance broker
5.5%
Flat
3050
26400
10.60%
4
Bank loan
7.9%
APR
2908.62
19613.76
7.90%
5
Credit union
8.9%
APR
2960.99
22127.52
8.90%

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:

QuoteQuoted rateTotal interestRank on cost
Dealer finance4.9% flat£23,520.003
Finance broker5.5% flat£26,400.004
Bank loan7.9% APR£19,613.761
Credit union8.9% APR£22,127.522

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.

Try it in the grid

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

Try it in the grid

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:

QuoteQuoted rateRATE × 12APR
Dealer finance4.9% flat9.07%9.45%
Finance broker5.5% flat10.11%10.60%
Bank loan7.9% APR7.63%7.90%
Credit union8.9% APR8.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%.

Try it in the grid

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.

Try it in the grid

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.

Try it in the grid

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:

TermMonthly paymentAPRAPR ÷ flat
12 months£10,490.009.30%1.90
24 months£5,490.009.54%1.95
36 months£3,823.339.52%1.94
48 months£2,990.009.45%1.93
60 months£2,490.009.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.

Try it in the grid

08Five Traps

  1. Comparing a flat rate with an APR. They measure different things. Turn every quote into an APR first.
  2. A positive payment in RATE. With the loan and the payment the same sign, RATE returns #NUM!.
  3. Reading the monthly rate as annual. RATE returns the rate per payment period: 0.756% a month, not a year.
  4. Calling RATE × 12 an APR. In the UK and EU it understates: 9.07% against 9.45% for the dealer.
  5. Leaving fees out. A fee paid up front comes off the amount borrowed, or the rate looks lower than it is.

09Four Exercises

  1. Reproduce it. Work out the broker's flat interest and payment, and confirm £26,400 and £3,050.
  2. Reverse it. Use RATE on the broker's payment, and confirm 10.60% APR.
  3. Add the fee. Take £995 off the amount, and confirm the broker rises to about 11.08%.
  4. 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.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge