Loan Amortization Schedule in Excel + EMI Calculator (PMT, NPER, Extra Payments), by PK: An Excel Expert on YouTube. 10 min 23 s.
- Video length
- 10 min 23 s
- Time to do it
- 30 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Lay out your inputs in separate cells: loan amount, annual interest rate, term in months and payment start date. Keep them apart from the formula cells so you can change them easily.
In a cell, type =PMT(rate/12, months, -loan). Divide the annual rate by 12 for the monthly rate and put a minus sign before the loan amount so the payment shows as positive.
Check the result against a known payment. For a $48,500 loan at 7.25% over 60 months the PMT formula returns about $966.09 a month.
Start the schedule with a payment date column. In the first row use the start date, then use =EDATE(previous date, 1) and fill down for each month.
Add columns for opening balance, EMI, interest, principal and closing balance. Interest is opening balance times the monthly rate. Principal is EMI minus interest. Closing balance is opening balance minus principal.
Fix the classic error: the second row's opening balance must equal the first row's closing balance, not the original loan amount. Reference the cell above and fill down.
Fill the schedule down to the final month. The closing balance should land at or near zero on the last payment.
To test extra payments, add the extra amount to the monthly payment and use =ROUNDUP(NPER(rate/12, -new payment, loan), 0) to get the new number of months.
Subtract the new term from the original term to see months saved, and compare total interest paid in both cases. Use =EDATE(start date, new months) for the debt-free date.
To compare loan offers, put each offer's rate, term and fees in rows. Use PMT for the payment and INDEX, MATCH and MINIFS to pick the cheapest offer that fits your monthly budget.
Tips and common mistakes
Always divide the annual interest rate by 12 before using PMT or NPER, or the payment will be far too high.
Put a minus sign before the loan amount in PMT, otherwise Excel returns a negative payment.
Check that the last closing balance is zero. If it is not, your opening balance references are wrong.
Extra payments save the most interest early in the loan, so compare the interest saved, not just the lower payment.
Questions
Why does my PMT formula give a negative number?
Excel treats money paid out as negative. Add a minus sign before the loan amount, or wrap the formula in ABS, to show the payment as a positive figure.
How do I show extra payments in the amortization schedule?
Add the extra amount to the EMI in each row, or add a separate extra payment column and subtract it from the closing balance. Then recalculate the term with NPER.
Can I use this for a mortgage instead of a car loan?
Yes. The same PMT, EDATE and NPER formulas work for any fixed-rate loan. Just change the loan amount, rate and term in months.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
