Loan Amortisation Schedule in Excel: EMI, Interest, Principal and Prepayments

📎 This article includes 1 downloadable practice file ↓

A bank sanction letter gives you an EMI and not much else. An amortisation schedule shows, month by month, how much of each EMI is interest, how much reduces the loan, and what you still owe. With it you can check the bank’s figures, plan prepayments and get the yearly interest and principal numbers you need for income-tax deductions.

In this article

1. Step 1: inputs
2. Step 2: the EMI
3. Step 3: the schedule
4. Yearly totals without the schedule
5. Prepayment: reduce tenure or reduce EMI?
6. When the rate changes
7. Reverse questions
8. Where people go wrong
9. Practice
10. Continue learning

The example below uses a ₹15 lakh loan at 9% a year for 15 years. The rate is only an example.

Step 1: inputs

Cell	Input	Value
B2	Loan amount	15,00,000
B3	Annual interest rate	9%
B4	Tenure (years)	15
B5	Start month	01-Apr-2026

Step 2: the EMI

=ROUND(PMT(B3/12, B4*12, -B2), 0)

Result: ₹15,214. PMT needs the rate and the number of periods in the same unit, months here. The minus sign in front of the loan makes the result positive.

Step 3: the schedule

Headers in row 8: Month no., Month, Opening balance, EMI, Interest, Principal, Closing balance. First row (row 9):

A9: 1
B9: =EDATE($B$5, A9-1)
C9: =$B$2
D9: =MIN($B$7, C9 + E9)            ' $B$7 holds the EMI; MIN fixes the last instalment
E9: =ROUND(C9 * $B$3/12, 0)
F9: =D9 - E9
G9: =C9 - F9

Row 10: A10 = A9+1, C10 = G9, and copy the other columns down to month 180. Interest each month is the opening balance × monthly rate, so as the balance falls, more of each EMI goes to principal. The MIN in column D stops the final EMI overshooting, so the closing balance ends at exactly zero.

Month 1 splits as ₹11,250 interest and ₹3,964 principal. In the first year you pay about ₹1.33 lakh interest and repay only about ₹50,000 of the loan.

Yearly totals without the schedule

=-CUMIPMT(B3/12, B4*12, B2, 1, 12, 0)     ' interest in months 1-12
=-CUMPRINC(B3/12, B4*12, B2, 1, 12, 0)    ' principal in months 1-12
=-FV(B3/12, 12, -PMT(B3/12, B4*12, -B2), B2)  ' balance after 12 EMIs

For a financial-year view where the loan started mid-year, sum the schedule with SUMIFS on the Month column between 1 April and 31 March.

Prepayment: reduce tenure or reduce EMI?

Say you prepay ₹1 lakh at the very start.

=NPER(B3/12, B7, -(B2 - 100000))       ' months left with the same EMI → about 157
=PMT(B3/12, B4*12, -(B2 - 100000))     ' new EMI with the same tenure → about ₹14,200

Keeping the EMI and cutting the tenure saves more interest, because the loan is cleared about two years earlier. For a prepayment in a later month, add a Prepayment column to the schedule and subtract it in the closing balance: G = C − F − Prepayment.

When the rate changes

Floating-rate loans reset. Add a Rate column, fill the new rate from the reset month, and calculate interest with that row’s rate. Banks usually keep the EMI the same and stretch the tenure; recalculate the EMI with PMT on the remaining balance if yours changes the EMI instead.

Reverse questions

=PV(B3/12, B4*12, -20000)          ' loan you can afford with ₹20,000 EMI
=RATE(B4*12, -16000, B2) * 12      ' annual rate implied by a ₹16,000 EMI

Where people go wrong

Mistake	Fix
Annual rate with monthly periods	Rate/12 and years×12, always together
Closing balance not zero at the end	Rounding; use MIN in the EMI column for the last instalment
Comparing to a bank schedule that is off by a few rupees	Banks may compute daily interest or round differently; small differences are normal
Interest on the original loan every month	Interest uses the opening balance of that month

Practice

Download the practice workbook below. It uses the same ₹15 lakh example loan; build the schedule yourself on a new sheet as a bonus. Each task has an automatic ✓ check, and the Answers sheet has a working formula if you get stuck.

See also: EMI with PMT, IPMT and PPMT.

Continue learning

All Excel for Finance guides →

Excel Month-End Financial Close: Step-by-Step Checklist, Reconciliation Formulas & TemplateMastering the Month-End Financial Close in Excel The month-end close process is the pulse…Bank Reconciliation Statement (BRS) in Excel: Month-End Close ChecklistA practical bank-reconciliation workflow with a downloadable Excel template, matching process and month-end review…SAP FICO Essential T-Codes: General Ledger, AP, AR & Period-End ClosingIn SAP ERP (ECC 6.0 and S/4HANA), SAP FICO (Financial Accounting & Management Controlling)…Excel for Finance Lesson 2: TDS Register and Quarterly SummaryMaintain a TDS register in Excel: section-wise rates, the higher rate without PAN, TDS…

📎 Practice files for this article

• 📗
  Loan schedule practice workbookA Rs 15 lakh, 15-year example loan with 11 tasks: EMI, interest/principal split, yearly totals, balance, prepayment and affordability, with checks.
  ⬇ 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.