01 / 09The Schedule Kept Paying After the Loan Was Gone

Financial FunctionsPMTFormulas

Loan Overpayment Schedule in Excel: Stop at a Zero Balance

Overpaying the Scanner Loan Was Meant to Save £5,337 of Interest. It Saved £3,549, Because the Schedule Kept Paying After the Loan Was Gone

19 Nov 20266 min read

UsesMIN, MAXPMTFVCUMIPMT, CUMPRINC

Thornbury Dental chose to overpay a £60,000 scanner loan by £500 a month because the schedule promised £5,336.98 of interest saved. The real saving was £3,548.94, and the difference was interest the sheet had credited for months after the loan was repaid.

FigureThe scheduleThe lender
Interest paid£5,101.14£6,889.18
Interest saved£5,336.98£3,548.94
Last paymentMonth 60Month 40

Scope. PMT, MIN, FV, NPER, SUM and COUNTIF work in every version of Excel. XLOOKUP needs Excel 2021 or Microsoft 365.

The schedule here charges interest monthly on the opening balance, as most UK business and personal loans do. Check your agreement: some lenders calculate interest daily.

01The Schedule Kept Paying After the Loan Was Gone

A loan schedule copied down a fixed number of rows does not know when the loan ends. It pays the same amount every row, whether anything is still owed or not.

The loan sits in H1:H4 and the schedule in A1:E61, one row per month:

H1:  60000                              amount borrowed
H2:  6.5%                               annual rate
H3:  =ROUND(-PMT(H2/12,60,H1),2)        → 1,173.97   contractual payment
H4:  500                                overpayment

Down the schedule, B2 is =$H$1 and B3 is =E2. Interest is =B2*$H$2/12, the payment =$H$3+$H$4, and the closing balance =B2+C2-D2.

With £500 extra the loan clears early. Month 40 owed only £1,604.35, but the sheet paid £1,673.97, and then paid it again for twenty more months.

Scenario: Build the schedule above in a blank sheet and fill row 2 down to row 61. Check that E41, month 40's closing balance, shows -69.62 instead of zero.

Try it in the grid

02Negative Interest Made the Total Look Smaller

Once the balance turns negative, =B2*$H$2/12 turns negative too. The sheet starts charging the lender interest, and those negative figures come off the total.

Thornbury Dental Scanner Loan, the Overpayment Schedule Around Month 40

Rows 39 to 43 and 61 of Thornbury Dental's overpayment schedule for a £60,000 loan at 6.5% over 60 months, paying £1,173.97 plus £500 a month. B is the opening balance, C the interest, =B2*$H$2/12, D the payment, =$H$3+$H$4, and E the closing balance, =B2+C2-D2. From month 40 the balance is negative and so is the interest.

ABCDEF
1
Month
Opening balance
Interest
Payment
Closing balance
What the sheet did
2
Month 38
4899.49
26.54
1673.97
3252.06
A normal month: interest on what is still owed.
3
Month 39
3252.06
17.62
1673.97
1595.7
The last full payment.
4
Month 40
1595.7
8.64
1673.97
-69.62
Only 1,604.35 was owed. The sheet paid 69.62 too much.
5
Month 41
-69.62
-0.38
1673.97
-1743.97
The loan is gone. The sheet pays again and books interest in Thornbury's favour.
6
Month 42
-1743.97
-9.45
1673.97
-3427.39
Another payment, and more negative interest.
7
Month 60
-33481.73
-181.36
1673.97
-35337.06
The last row: 20 months of negative interest, -1,788.04 in all.

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

The total interest cell was =SUM(C2:C61), and the saving was the 60-month schedule's interest minus that:

MonthsInterest in column C
1 to 40£6,889.18
41 to 60-£1,788.04
1 to 60£5,101.14

Against £10,438.12 on the contractual schedule, that read as £5,336.98 saved. The last 20 rows had invented £1,788.04 of it.

The bottom row held the clue all along: a closing balance of -£35,337.06. Nobody scrolled to row 61.

Scenario: Enter =SUM(C42:C61) in any empty cell. Check that it returns -1,788.04, the interest the sheet credited after the loan was repaid.

Try it in the grid

03Cap Each Payment at What Is Still Owed

The fix is one function in the payment column. Pay the planned amount or the balance plus this month's interest, whichever is smaller:

D2:  =MIN($H$3+$H$4,B2+C2)

In month 40 the balance plus interest is 1,604.35, so that is what MIN pays, and the closing balance lands on exactly zero.

From month 41 the opening balance is zero, interest is zero and MIN pays zero. The schedule stops itself, however many rows you fill.

Scenario: Replace D2 with =MIN($H$3+$H$4,B2+C2) and fill it down to D61. Check that D41 shows 1,604.35 and every payment below it shows 0.

Try it in the grid

04Check That the Totals Agree

Every pound paid is either principal or interest. So total payments minus the amount borrowed must equal total interest, and on a broken schedule they disagree:

=SUM(D2:D61)-H1      total paid less principal
=SUM(C2:C61)         total interest
=MIN(E2:E61)         lowest closing balance
=COUNTIF(E2:E61,"<0")
CheckBefore MINAfter MIN
Paid less principal£40,438.20£6,889.18
Total interest£5,101.14£6,889.18
Lowest balance-£35,337.06£0.00
Negative balances210

The gap of £35,337.06 between the first two rows is the negative closing balance. When the two agree, the loan ends at zero.

Scenario: Enter =COUNTIF(E2:E61,"<0") next to the schedule. Check that it shows 21 before the MIN fix and 0 after it.

Try it in the grid

05FV Confirms the Final Payment Independently

FV returns the balance after any number of payments, without a schedule. Use it to check the one row that matters most, the last one:

=-FV(H2/12,39,-(H3+H4),H1)                → 1,595.70
=-FV(H2/12,39,-(H3+H4),H1)*(1+H2/12)      → 1,604.35
=NPER(H2/12,-(H3+H4),H1)                  → 39.96

After 39 payments, £1,595.70 is owed, and a month of interest makes the final payment £1,604.35. NPER says 39.96 payments, so the 40th is a part payment.

On the fixed schedule, =XLOOKUP(0,E2:E61,A2:A61) returns 40, the month the balance first reaches zero.

Scenario: Enter =-FV(H2/12,39,-(H3+H4),H1)*(1+H2/12) in an empty cell. Check that it matches D41 on the fixed schedule, 1,604.35.

Try it in the grid

06Compare Overpayments on the Fixed Schedule

With MIN in place, change H4 and read the totals. Each extra £250 saves less than the one before, because there is less balance left to charge:

OverpaymentMonthsInterestSaved
£060£10,438.12£0.00
£25048£8,291.93£2,146.19
£50040£6,889.18£3,548.94
£75035£5,900.83£4,537.29
£1,00030£5,164.83£5,273.29

The broken sheet's £5,336.98 for £500 a month was more than the true saving from £1,000 a month. A comparison built on it would have ranked every option wrongly.

Scenario: On the fixed schedule, set H4 to 250 and read =SUM(C2:C61). Check that it returns 8,291.93, and that D50 is the first zero payment.

Try it in the grid

07Five Traps

  1. A fixed payment with no cap. Without MIN, the schedule pays past zero and books negative interest.
  2. Trusting a total over a long range. =SUM(C2:C61) happily adds negative months. Check =MIN(E2:E61) first.
  3. Using CUMIPMT for an overpaid loan. It works out the payment from the term, so it always describes the contractual schedule.
  4. Rounding every row. Round the payment once in H3, or each month adds its own error.
  5. Stopping the fill at the payoff row. Change the overpayment and the payoff month moves; fill all 60 rows and let MIN stop it.

08Four Exercises

  1. Reproduce it. Build the broken schedule and confirm E61 shows -35,337.06.
  2. Fix it. Add MIN to column D and confirm total interest of 6,889.18.
  3. Check it. Confirm =SUM(D2:D61)-H1 and =SUM(C2:C61) agree.
  4. Vary it. Set H4 to 750 and confirm the loan clears in month 35.

What to Take Away

Every formula in the schedule was right for a loan that still existed. The sheet never asked whether it did, and twenty months of negative interest turned a £3,549 saving into £5,337.

Cap every payment with =MIN($H$3+$H$4,B2+C2), and check that the last closing balance is zero before you read any total.

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

Price a Balloon Finance Deal with PMT's Fourth Argument

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

Solve today’s challenge