Loan & EMI Tracker with Prepayment (Excel)
Type the loan amount, interest rate, tenure and first EMI date: the workbook works out the EMI and writes the full amortisation schedule, showing for every month how much of the EMI goes to interest and how much reduces the loan. Add a one-time prepayment or a small extra amount every month and see at once how many months it cuts and how much interest it saves. Mark each EMI as paid to always know the balance still owed.
Free · no sign-up · opens in Microsoft Excel, Google Sheets, LibreOffice and WPS Office. Files contain no macros.
Preview
Real view of the file with its sample data (yellow cells are where you type).
| Loan name | Home loan | |
| Currency symbol | ₹ | |
| Loan amount | 35,00,000 | |
| Interest rate per year | 8.5% | |
| Tenure (months) | 240 | |
| First EMI date | 05-09-2024 | |
| Extra payment every month | 0 | |
| One-time prepayment amount | 2,00,000 | |
| One-time prepayment in EMI number | 36 | |
| Results | ||
| EMI (monthly instalment) | 30373.81 | |
| Total interest without prepayment | 37,89,714 | |
| Total interest with your prepayments | 3216519.23 | |
| Interest saved | 573194.77 | |
| Months to close the loan | 215 | |
| Months saved | 25 | |
| Loan closes on | 05-07-2042 | |
| EMIs marked paid | 26 | |
| Balance still owed | 3341254.9 |
What's inside
- Loan sheet: loan amount, yearly interest rate, tenure in months (up to 30 years), first EMI date, an extra monthly payment and a one-time prepayment in any EMI month
- Results: EMI, total interest without prepayment, total interest with prepayment, interest saved, months saved, the date the loan closes and the balance still owed today
- Schedule sheet with up to 360 rows: EMI date, opening balance, interest, principal, prepayment, closing balance and interest paid so far
- "Paid on" column to tick off EMIs as they are debited; paid rows turn green and prepayment months are highlighted
- All loans sheet: every loan in the family (home, vehicle, personal, education, gold, credit card) with outstanding amount, EMI, EMI date and end date, total EMIs and the share of monthly income they take
How to use it
- On the Loan sheet type the loan name, currency symbol, loan amount, interest rate per year, tenure in months and the date of the first EMI.
- Check the EMI against your loan sanction letter. A small difference of a few units is normal: banks round in their own way.
- To see the effect of paying extra, type an amount in "Extra payment every month" or a one-time prepayment and the EMI number in which you will pay it.
- Compare the results: interest saved, months saved and the new closing date.
- Each month, type the date the EMI was paid in the "Paid on" column of the Schedule sheet. "Balance still owed" on the Loan sheet follows the last paid row.
- List all your loans on the All loans sheet to see the total EMI and how much of your monthly income it uses.
Tips
- The schedule assumes a fixed rate and that a prepayment shortens the tenure while the EMI stays the same, which is how most lenders apply part-payments by default. If your lender reduces the EMI instead, the interest saved will be lower.
- Floating-rate loans change rate over time. When your lender revises the rate, start a new copy from the current outstanding balance and remaining months.
- Ask your lender about prepayment charges and whether there is a minimum part-payment amount before you plan one.
- Make one copy of the file per loan (File › Save As) and keep the All loans sheet in the copy you open most.
- This is a calculator and record, not advice on whether to prepay or invest; consider your emergency fund and talk to a qualified adviser if unsure.
Questions and answers
How is the EMI calculated?
With the standard reducing-balance formula used by banks: EMI = P × r × (1 + r)^n ÷ ((1 + r)^n − 1), where P is the loan amount, r the monthly rate (yearly rate ÷ 12) and n the number of months. Excel's PMT function does exactly this.
Why is most of my early EMI going to interest?
Interest is charged on the balance still owed. At the start the balance is highest, so interest takes the larger share; as the balance falls, more of each EMI goes to principal. The Schedule sheet shows this month by month.
Can I enter more than one prepayment?
The Loan sheet takes one one-time prepayment plus a fixed extra amount every month. For several irregular prepayments, keep a copy of the file per scenario, or ask for a longer version through the contact page.
Does it work for car, personal and education loans?
Yes, for any loan with equal monthly instalments and a fixed rate. It is not meant for credit card balances, which charge interest differently.