EMI Amortisation Schedule Excel
Month-by-month breakdown of your loan EMI into principal and interest components with running outstanding balance — up to 360 months. Essential for auditors reconciling interest expense, tax payers claiming Sec 24(b) home-loan interest, and anyone refinancing a loan.
⬇ Download Excel — Free No signup. No email required. Direct download.What you get
Sheet-by-sheet overview
Workbook contents (3 sheets)
- Cover — Version, loan terminology, legal disclaimer on interest claim.
- Inputs & Summary — Principal, rate (annualised), tenure in months, prepayment schedule. Shows total interest, total outflow, effective cost.
- Amortisation Schedule — Month-by-month rows — opening balance, EMI, interest portion, principal portion, closing balance.
How to use
- Open the workbook. Input cells are unlocked and highlighted.
- Enter loan principal, annual interest rate (e.g. 8.75 for 8.75 percent), and tenure in months (e.g. 240 for 20 years).
- The amortisation schedule auto-populates. Scroll down to view every month.
- To simulate prepayment, enter the lump sum in the prepayment column against the relevant month — the schedule rebalances downstream automatically.
- Sum the interest column for a given financial year to support your Sec 24(b) claim.
Why this utility matters for CAs and salaried professionals
Every home loan borrower in India needs two numbers from their loan schedule: the total interest they will pay over the life of the loan, and the interest paid in each financial year (for Section 24(b) claim, capped at ₹2 lakh for self-occupied property). Most banks either charge a fee for a detailed statement or issue one that is difficult to reconcile with the return filing. This Excel utility lets you generate the exact same schedule in under a minute — and gives you the flexibility to simulate prepayments without waiting for the bank to confirm the revised schedule.
The mathematics is the standard reducing-balance EMI formula: monthly EMI = P × r × (1 + r)^n ÷ ((1 + r)^n − 1), where r is monthly interest (annual rate ÷ 12 ÷ 100) and n is tenure in months. Each month the interest portion is outstanding balance × r, and the principal portion is EMI minus interest. The workbook implements this as a locked formula in every row of the amortisation sheet and exposes only the top-level inputs.
Where auditors use this
During statutory audits of companies with sizable term loan portfolios, the auditor needs to verify the interest expense booked to the profit and loss account. The bank sanction letter gives the rate; the loan balance is in the books — but reconciling the monthly split requires either the bank schedule (often unavailable mid-year) or an amortisation model. This workbook plugs that gap. Copy the inputs from the sanction letter, confirm the schedule aligns with actual debits in the bank statement, and the interest expense falls out cleanly.
Prepayment and partial settlement
Banks typically charge no prepayment penalty on floating-rate home loans (RBI directive since 2012). If you have surplus cash, the schedule lets you model the impact before making the payment. Entering a ₹5 lakh prepayment at month 60 of a 240-month loan will often shave off 40–50 months of the tail, depending on the rate. The sheet shows the revised closing balance and cumulative interest saving.
Frequently asked questions
Can I change the interest rate mid-loan (floating rate scenario)?
The current version uses a single fixed rate. For floating rates, you can manually split the tenure at the rate-change month and run two schedules, or use the online EMI calculator on the calculators hub which supports rate changes.
Does this handle moratorium periods?
Not directly. For EMI holidays or interest-only periods, you would need to add interest to principal manually at the end of the moratorium and restart the schedule.
Is the EMI formula the same one banks use?
Yes. Indian banks use the reducing-balance method with monthly compounding, which is what this workbook implements. You should match the bank schedule to the rupee barring minor rounding at month 360.