Your bank tells you the EMI. An amortisation schedule shows where each rupee goes — how much is interest, how much repays the loan, and how prepayments shorten it.
Inputs
| Cell | Input | Example |
|---|---|---|
| B1 | Loan amount | 30,00,000 |
| B2 | Annual rate | 8.75% |
| B3 | Years | 15 |
| B4 | EMI | =PMT(B2/12, B3*12, -B1) |
The schedule (from row 7)
| Column | Heading | Formula (row 8) |
|---|---|---|
| A | Month | 1, 2, 3… |
| B | Opening balance | row 8: =$B$1; row 9: =F8 |
| C | Interest | =ROUND(B8*$B$2/12, 2) |
| D | Prepayment | type amounts when you prepay |
| E | Principal | =MIN($B$4-C8, B8) |
| F | Closing balance | =MAX(B8-E8-D8, 0) |
Fill down 180 rows. When the closing balance hits zero, the loan is over — prepayments make that happen earlier.
Useful totals
=SUM(C8:C187) ' total interest
=COUNTIF(F8:F187, ">0") + 1 ' months until the loan closes
=SUMIFS(C8:C187, A8:A187, "<=12") ' interest in year 1 (for income-tax deduction estimates)Check with IPMT and PPMT
=IPMT($B$2/12, A8, $B$3*12, -$B$1) ' interest in month A8 (no prepayments)
=PPMT($B$2/12, A8, $B$3*12, -$B$1) ' principal in month A8💡 Early EMIs are mostly interest. Even a small prepayment in the first years saves far more interest than the same amount paid later.
Quick numbers without a sheet: the free EMI & SIP calculator.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong