A bank's repayment schedule shows the loan as if nothing will ever change. Yours will: a bonus goes in as a prepayment, the floating rate resets, you start paying a little extra every month. This sheet lays the loan out month by month and lets you put all three in, so you can see the date it actually ends and the interest you save.
⬇ Download: Home loan amortization schedule with prepayment (.xlsx, free)What's in the sheet
- Loan amount, starting rate, tenure (up to 360 months) and first EMI date.
- A regular extra payment every month, a column for one-off prepayments in any month, and a column for a new rate from the month it resets.
- The full monthly schedule: opening balance, interest, principal, prepayment and closing balance.
- Side by side with the same loan without prepayments: months to close, months saved, total interest and interest saved.
How to use it
- Fill the loan details at the top; the EMI calculates.
- Type one-off prepayments in the month you'll make them, and a new rate in the month a reset takes effect; it carries forward.
- Read the top box: how many months sooner the loan closes and how much interest that saves.
The formulas behind each row
Amortization formulas in Excel
EMI: =ROUND(PMT(B6/1200,B7,-B5),0)
Interest for month: opening balance × rate ÷ 1200
Principal: EMI − interest
Closing balance: opening − principal − prepayment
Months to close: =COUNTIF(C22:C381,">0")
The EMI stays the same and prepayments shorten the loan, which is what most banks do by default. To compare that with lowering the EMI instead, use the reduce-EMI-or-tenure sheet.
Reduce EMI or tenure? Compare bothFrequently asked questions
- How do I make a home loan amortization schedule in Excel?
- Work out the EMI with =PMT(rate/1200, months, -loan). Then for each month: interest is the opening balance × rate ÷ 1200, principal is EMI minus interest, and the closing balance becomes next month's opening balance. Add a prepayment column to subtract extra payments.
- Does a prepayment reduce EMI or tenure?
- Most banks keep the EMI and shorten the tenure unless you ask in writing to reduce the EMI. Shortening the tenure saves more interest.
- Are there charges for prepaying a home loan?
- Not on a floating-rate loan taken by an individual for non-business use: banks cannot charge for part or full prepayment. Fixed-rate loans can carry a charge; check your agreement.