Loan amortization schedule and EMI calculator

Enter the loan amount, interest rate, term and first payment date, and the sheet works out the regular payment with Excel’s PMT function and lists every payment: the opening balance, how much goes to interest and how much to principal, and the balance left. Add an extra payment each period to see how much sooner the loan ends and how much interest you save. The EMI version is set up for home, car and personal loans in India.

Templates

Loan amortization schedule (Excel spreadsheet): A loan amortization schedule in Excel: enter amount, rate and term to get the payment and a full schedule of interest, principal and balance, with extra payments.
EMI calculator (Excel) (Excel spreadsheet): An EMI calculator in Excel for home, car or personal loans: monthly EMI, total interest and a month-by-month repayment schedule.
Savings goal tracker (Excel spreadsheet): A savings tracker: set a goal and date, log deposits, and see your progress, what is left and how much to save each month.

How to use these templates

Enter the loan amount, annual interest rate and term in years.
Set payments per year (12 for monthly) and the first payment date.
Read the scheduled payment or EMI and the totals for interest and amount paid at the top.
Try an extra payment each period to see the last payment date move earlier.

Understanding your loan schedule

Early payments are mostly interest, because interest is charged on a large balance. The principal share grows with every payment.
Even small extra payments early in the loan save a surprising amount of interest. Try different amounts in the extra payment cell.
The schedule assumes a fixed rate. For a floating-rate loan, update the rate when it changes and the payments recalculate.
Banks may round the EMI and the final payment differently, so the last payment can differ by a few rupees or cents.
Check whether your lender charges a fee for prepayment before planning extra payments.

Frequently asked questions

How is EMI calculated?

EMI = P × r × (1 + r)^n ÷ ((1 + r)^n − 1), where P is the loan amount, r the monthly interest rate and n the number of months. The sheet uses Excel’s PMT function, which gives the same result.

Can I use it for a mortgage?

Yes. Enter the mortgage amount, rate and 25 or 30 year term. It does not include property tax or insurance, which many US mortgage payments also cover.

How many payments does the schedule show?

Up to 360, which covers a 30-year monthly loan. Rows after the final payment stay blank.

Does it handle extra payments?

Yes. The extra amount is added to each payment and the loan finishes when the balance reaches zero.