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.
| Figure | The schedule | The lender |
|---|---|---|
| Interest paid | £5,101.14 | £6,889.18 |
| Interest saved | £5,336.98 | £3,548.94 |
| Last payment | Month 60 | Month 40 |
Scope.
PMT,MIN,FV,NPER,SUMandCOUNTIFwork in every version of Excel.XLOOKUPneeds 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.
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.
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:
| Months | Interest 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.
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.
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")
| Check | Before MIN | After 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 balances | 21 | 0 |
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.
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.
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:
| Overpayment | Months | Interest | Saved |
|---|---|---|---|
| £0 | 60 | £10,438.12 | £0.00 |
| £250 | 48 | £8,291.93 | £2,146.19 |
| £500 | 40 | £6,889.18 | £3,548.94 |
| £750 | 35 | £5,900.83 | £4,537.29 |
| £1,000 | 30 | £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.
07Five Traps
- A fixed payment with no cap. Without
MIN, the schedule pays past zero and books negative interest. - Trusting a total over a long range.
=SUM(C2:C61)happily adds negative months. Check=MIN(E2:E61)first. - Using
CUMIPMTfor an overpaid loan. It works out the payment from the term, so it always describes the contractual schedule. - Rounding every row. Round the payment once in
H3, or each month adds its own error. - Stopping the fill at the payoff row. Change the overpayment and the payoff month moves; fill all 60 rows and let
MINstop it.
08Four Exercises
- Reproduce it. Build the broken schedule and confirm
E61shows -35,337.06. - Fix it. Add
MINto column D and confirm total interest of 6,889.18. - Check it. Confirm
=SUM(D2:D61)-H1and=SUM(C2:C61)agree. - Vary it. Set
H4to 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.