This EMI calculator works out the monthly instalment for a home, car or personal loan, the total interest you’ll pay, and a year-by-year repayment schedule. It also shows the Excel formula, so you can build the same thing in a spreadsheet.
- Total interest
- Total payment
- Interest share
Year-by-year repayment schedule
| Year | Principal paid | Interest paid | Balance |
|---|
Estimates for a fixed-rate loan with monthly payments. Your lender's figures may differ slightly.
How EMI is calculated
The EMI formula is EMI = P × r × (1 + r)ⁿ ÷ ((1 + r)ⁿ − 1), where:
- P is the loan amount
- r is the monthly interest rate: the yearly rate ÷ 12
- n is the number of monthly instalments
For a ₹25,00,000 loan at 8.5% for 20 years: r = 8.5% ÷ 12 = 0.708% and n = 240, giving an EMI of ₹21,696. Over 20 years you pay about ₹27 lakh in interest, more than the loan itself.
EMI in Excel
=PMT(B2/12,B3*12,-B1)With the loan amount in B1, the yearly rate in B2 and the years in B3. The amount is negative because, from your point of view, it’s money you receive. To split each EMI into principal and interest, use PPMT and IPMT; see the PMT function guide.
| To calculate | Excel formula |
|---|---|
| Monthly EMI | =PMT(B2/12,B3*12,-B1) |
| Total interest | =PMT(B2/12,B3*12,-B1)*B3*12-B1 |
| Interest in month 1 | =IPMT(B2/12,1,B3*12,-B1) |
| Principal in month 1 | =PPMT(B2/12,1,B3*12,-B1) |
How to lower your EMI or interest
- A longer tenure lowers the EMI but increases the total interest.
- Part-prepayments, especially early in the loan, cut the interest the most, because interest is charged on the remaining balance.
- A lower rate has a big effect over long loans: on ₹25 lakh for 20 years, 0.5% less saves about ₹1.8 lakh.
Frequently asked questions
Is the EMI the same every month?
Yes, for a fixed-rate loan. The split changes, though: early EMIs are mostly interest, and later ones mostly principal.
Does this include processing fees or insurance?
No. It covers the loan repayment only.
What about floating-rate loans?
The EMI or tenure changes when the rate changes. Recalculate with the new rate and the remaining balance.