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 prepayment | Result | Interest still to pay |
|---|---|---|
| Do nothing | 216 months remain at ₹26,035 | about ₹27,48,186 |
| Keep EMI, shorten tenure | about 171 months at ₹26,035 | about ₹18,73,797 |
| Keep tenure, lower EMI | 216 months at about ₹23,318 | about ₹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
- After every prepayment, update column F and recheck the closing balance against the lender's outstanding figure.
- If the two differ by more than a rupee or two, ask the lender for a fresh schedule.
- Revisit once a year and decide whether the next lump sum should go to the loan or elsewhere.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


