Spreadsheets

Home loan prepayment tracker sheet showing interest saved

Track home loan prepayments in Excel with a month-by-month amortisation table and extra-payment column, and compare interest with and without prepayment.

Editorial illustration for Home loan prepayment tracker sheet showing interest saved

A home loan prepayment tracker is an amortisation table with one extra column: the lump sum you pay in a given month. Each prepayment reduces the principal, so later interest is lower, and the sheet shows either how many months fall off the tenure at the same EMI or how far the EMI can drop. A summary at the top compares total interest with and without prepayments.

What are the inputs?

Put four inputs at the top: B1 Loan amount, B2 Annual interest rate, B3 Tenure in months, B4 EMI. In B4 write =PMT(B2/12,B3,-B1). The rate is divided by 12 because each period is a month; the minus sign on the loan keeps the EMI positive. Wrap it in ROUND if your lender rounds to the rupee, but compare against your own repayment schedule, since lenders differ slightly.

How do I build the schedule?

Row 7 holds headers: A Month, B Opening balance, C EMI paid, D Interest, E Principal, F Prepayment, G Closing balance. In row 8:

  • A8 =1, and A9 =A8+1, filled down
  • B8 =B1 (opening balance is the loan), and B9 =G8
  • D8 =B8*$B$2/12
  • C8 =MIN($B$4,B8+D8) so the last EMI cannot overshoot the balance
  • E8 =C8-D8
  • F8 is an input cell. Leave it blank, or type the lump sum in the month you pay it.
  • G8 =MAX(0,B8-E8-F8)

Fill rows down for your tenure plus some spare rows. Rows after the balance reaches zero show zeros, which is the sign the loan has ended. To count the months actually needed, use =COUNTIF(G8:G400,">0")+1; the +1 adds the final month, whose closing balance is zero.

How do I compare shorter tenure with lower EMI?

With the formulas above and the EMI left unchanged, a prepayment shortens the tenure. For the other choice, find the balance after the prepayment and recalculate. If you prepaid in month 24, which is row 31, write =PMT($B$2/12,$B$3-A31,-G31). This keeps the same end date and gives a lower EMI.

Say the loan is ₹30,00,000 at 8.5% for 240 months. EMI is about ₹26,035, and total interest across the full term is about ₹32,48,327. After 24 EMIs the balance is about ₹28,75,309. A prepayment of ₹3,00,000 brings it to ₹25,75,309.

Choice after the prepaymentResultInterest still to pay
Do nothing216 months remain at ₹26,035about ₹27,48,186
Keep EMI, shorten tenureabout 171 months at ₹26,035about ₹18,73,797
Keep tenure, lower EMI216 months at about ₹23,318about ₹24,61,449

These are illustrative numbers, not a prediction for any bank. Interest already paid in the first 24 months is about ₹5,00,142, so the full-term interest with the prepayment and shortened tenure is 5,00,142 + 18,73,797 = about ₹23,73,939, against ₹32,48,327 without it. The summary box should show both totals and the difference, about ₹8,74,388.

Which summary lines should I add?

  • Total interest paid: =SUM(D8:D400)
  • Total prepaid: =SUM(F8:F400)
  • Months to close: the COUNTIF formula above
  • Interest without prepayment: =B4*B3-B1
  • Interest saved: interest without prepayment minus total interest paid

How do I keep the real payments in view?

The schedule is a model; your bank's statement is the truth. Each EMI debit and prepayment appears on the loan or savings account statement. If you import those statements into SpendixAI, keep EMIs and prepayments in separate categories so the sheet's Prepayment column and your real spending line up.

A routine that works

  1. After every prepayment, update column F and recheck the closing balance against the lender's outstanding figure.
  2. If the two differ by more than a rupee or two, ask the lender for a fresh schedule.
  3. Revisit once a year and decide whether the next lump sum should go to the loan or elsewhere.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports