📎 This article includes 1 downloadable practice file ↓
| Question | Formula | ₹25 lakh, 8.5%, 20 yrs |
|---|---|---|
| EMI | =-PMT(8.5%/12, 240, 2500000) |
₹21,696 |
| Interest in EMI 1 | =-IPMT(8.5%/12, 1, 240, 2500000) |
₹17,708 |
| Principal in EMI 1 | =-PPMT(8.5%/12, 1, 240, 2500000) |
₹3,987 |
| Interest in year 1 | =-CUMIPMT(8.5%/12, 240, 2500000, 1, 12, 0) |
₹2,10,591 |
| Balance after 12 EMIs | =2500000 + CUMPRINC(8.5%/12, 240, 2500000, 1, 12, 0) |
₹24,50,244 |
Over 20 years the borrower pays ₹27,06,939 in interest, more than the loan itself. That’s why prepayments matter.
What does a prepayment save?
Pay ₹2,00,000 extra after month 12 and keep the same EMI. The remaining months:
=NPER(8.5%/12, PMT(8.5%/12,240,2500000), balance_after_12 - 200000) → 188 monthsInstead of 228 months left, 188: 40 EMIs saved. Most banks let you choose between a lower EMI and a shorter tenure; shorter tenure saves far more interest.
A full schedule in one formula
=LET(n, SEQUENCE(240), r, 8.5%/12,
HSTACK(n, -IPMT(r, n, 240, 2500000), -PPMT(r, n, 240, 2500000)))Practice
Download the workbook below. Type your formula in the yellow column; the Check column turns green when it matches Excel’s own answer, and the Answers sheet has a working formula for every task. Tax rates and limits in the examples are for practice; verify current rules before real use.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook₹25 lakh home loan at 8.5% for 20 years, 8 tasks.⬇ XLSX · 13 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.