Banks quote an EMI; Excel lets you check it and see how much of each instalment is interest. The example uses a ₹25 lakh loan at 8.5% a year (an example rate) for 20 years.
In this article
Inputs
| Cell | Item | Value |
|---|---|---|
| B1 | Loan amount | 2500000 |
| B2 | Annual rate | 8.5% |
| B3 | Years | 20 |
EMI
=PMT(B2/12, B3*12, -B1)About ₹21,696. Divide the annual rate by 12 and multiply the years by 12; mixing an annual rate with monthly periods is the most common error. The minus sign on the loan makes the EMI positive (money in vs money out).
Interest and principal in a given month
=IPMT(B2/12, 1, B3*12, -B1) ' interest in month 1 ≈ ₹17,708
=PPMT(B2/12, 1, B3*12, -B1) ' principal in month 1 ≈ ₹3,988In the early years most of the EMI is interest, which is why prepaying early helps most.
Full schedule
Headers in row 6: Month, Opening, EMI, Interest, Principal, Closing. Row 7:
A7: 1
B7: =$B$1
C7: =ROUND(PMT($B$2/12, $B$3*12, -$B$1), 0)
D7: =ROUND(B7*$B$2/12, 0)
E7: =C7-D7
F7: =B7-E7
A8: =A7+1 B8: =F7 (C8:F8 as above, then fill down to month 240)The last closing balance won’t be exactly zero because of rounding; banks adjust the final EMI. Set the last row’s EMI to its opening balance plus its interest.
Excel 365: the schedule in one spill
=LET(n, B3*12, r, B2/12, m, SEQUENCE(n),
HSTACK(m, -IPMT(r, m, n, B1), -PPMT(r, m, n, B1)))Total interest and prepayment
=PMT(B2/12, B3*12, -B1) * B3*12 - B1 ' total interest paid
=NPER(B2/12, PMT(B2/12, B3*12, -B1), -(B1-200000)) / 12 ' years needed after a ₹2 lakh prepayment at the startKeeping the EMI the same after a prepayment shortens the tenure, which usually saves more interest than reducing the EMI.
Where people go wrong
| Mistake | Effect |
|---|---|
Annual rate with months: PMT(8.5%, 240, …) |
Absurd EMI |
| Rate typed as 8.5 instead of 8.5% | Rate becomes 850% |
| Negative EMI | Sign convention; put a minus on the loan amount |
| Ignoring floating-rate resets | Rebuild from the reset month with the new rate and remaining balance |
Practice
Download the combo practice workbook below. Its Sales sheet of 200 orders (dates, regions, products, customers, amounts) is ready data to try every formula on this page, and the Practice sheet has 50 checked tasks on related combos.
More: all formula combos · Excel function course.
Stuck on a step? Ask a question and the AI answers using this article.