Every bank, NBFC and app in India quotes your loan as a monthly EMI. But how is that number actually worked out? Once you understand the EMI formula, you can check any lender’s quote yourself, compare offers, and see exactly how much interest you will pay. This guide explains the formula in plain language, solves real examples step by step, shows how to calculate EMI in Excel with the PMT function, and compares different tenures. If you just want a quick answer, use our free EMI calculator.
What Is an EMI?
EMI stands for Equated Monthly Instalment. It is a fixed amount you pay every month until the loan is fully repaid. Each EMI has two parts: interest on the outstanding balance, and principal repayment. The EMI stays the same, but the split changes every month. In the beginning most of your EMI goes towards interest; towards the end, most of it reduces the principal. This is called a reducing-balance loan, and it is how almost all home, car, personal and education loans in India work.
The EMI Formula
The standard formula used by Indian banks is:
EMI = P × r × (1 + r)^n ÷ [(1 + r)^n − 1]
- P = principal, the loan amount you borrow
- r = monthly interest rate = annual rate ÷ 12 ÷ 100
- n = tenure in months (years × 12)
The most common mistake is using the annual rate directly. If your loan is at 9% per year, r is 9 ÷ 12 ÷ 100 = 0.0075, not 0.09.
Example 1: ₹10 Lakh Home Loan at 9% for 20 Years
Let us solve one example completely.
- P = ₹10,00,000
- r = 9 ÷ 12 ÷ 100 = 0.0075
- n = 20 × 12 = 240 months
- (1 + r)^n = (1.0075)^240 ≈ 6.0092
- EMI = 10,00,000 × 0.0075 × 6.0092 ÷ (6.0092 − 1) ≈ ₹8,997 (₹8,997.26 exactly)
Over 240 months you pay about ₹21,59,342 in total, of which ₹11,59,342 is interest. Yes, the interest is more than the loan itself on a 20-year loan at 9%.
How the First EMI Is Split
In month one, interest = ₹10,00,000 × 0.0075 = ₹7,500. The rest of the EMI, ₹8,997.26 − ₹7,500 = ₹1,497.26, reduces your principal. So in the first month only about 17% of your payment actually cuts the loan. Next month the interest is calculated on the slightly lower balance, so the principal share grows a little every month.
Example 2: ₹5 Lakh Personal Loan at 10.5% for 5 Years
- P = ₹5,00,000, r = 10.5 ÷ 1200 = 0.00875, n = 60
- EMI ≈ ₹10,747
- Total payment ≈ ₹6,44,817
- Total interest ≈ ₹1,44,817
Because the tenure is short, interest is only about 29% of the principal, compared with over 100% in the 20-year example.
Example 3: ₹20 Lakh Home Loan at 8.5% for 20 Years
- P = ₹20,00,000, r = 8.5 ÷ 1200, n = 240
- EMI ≈ ₹17,356
- Total payment ≈ ₹41,65,552
- Total interest ≈ ₹21,65,552
You can verify all three examples in seconds on our free loan EMI calculator.
How to Calculate EMI in Excel (PMT Function)
Excel and Google Sheets have a built-in function called PMT that applies the EMI formula for you. The syntax is:
=PMT(rate/12, months, -principal)
- rate/12: the annual rate as a decimal divided by 12 (9% becomes 9%/12 or 0.09/12)
- months: total number of EMIs
- -principal: the loan amount with a minus sign, so the result shows as a positive number
Step-by-Step Excel Example
- In cell B1 type the loan amount: 1000000
- In B2 type the annual rate: 9%
- In B3 type the tenure in months: 240
- In B4 type: =PMT(B2/12, B3, -B1)
Excel returns 8,997.26, exactly matching our manual calculation. You can also type it directly as =PMT(9%/12, 240, -1000000).
Extra Excel Formulas
- Total payment: =B4*B3 → ₹21,59,342
- Total interest: =B4*B3-B1 → ₹11,59,342
- Interest in a given month: =IPMT(B2/12, month, B3, -B1)
- Principal in a given month: =PPMT(B2/12, month, B3, -B1)
For month 1, IPMT returns ₹7,500 and PPMT returns ₹1,497.26, matching the split above. Drag these formulas down 240 rows and you have your own amortization schedule.
Tenure Comparison: ₹30 Lakh at 8.5%
Choosing a longer tenure lowers your EMI but sharply increases total interest. Here is a ₹30 lakh home loan at 8.5% across different tenures:
| Tenure | Monthly EMI | Total Payment | Total Interest |
|---|---|---|---|
| 10 years | ₹37,196 | ₹44,63,485 | ₹14,63,485 |
| 15 years | ₹29,542 | ₹53,17,594 | ₹23,17,594 |
| 20 years | ₹26,035 | ₹62,48,327 | ₹32,48,327 |
| 25 years | ₹24,157 | ₹72,47,044 | ₹42,47,044 |
| 30 years | ₹23,067 | ₹83,04,266 | ₹53,04,266 |
Look at the difference between 20 and 30 years. The EMI drops by only about ₹2,968 a month, but you pay roughly ₹20,55,939 more in interest. Going from 20 to 10 years raises the EMI by about ₹11,161 but saves nearly ₹17,84,842 in interest.
Practical Takeaways
- Pick the shortest tenure whose EMI fits comfortably in your budget. A common guideline is keeping all EMIs under 40–50% of take-home pay.
- If you take a long tenure for flexibility, make part-prepayments whenever you can. Early prepayments save the most interest because that is when the outstanding balance is highest.
- Even a small rate difference matters on long loans, so compare lenders before signing.
Common Mistakes When Calculating EMI
- Using the annual rate instead of the monthly rate.
- Entering tenure in years instead of months.
- Confusing flat-rate quotes with reducing-balance rates. A flat rate looks lower but costs much more.
- Ignoring processing fees, insurance and GST, which add to the real cost but are not part of the EMI.
Frequently Asked Questions
Does the EMI change during the loan?
On a fixed-rate loan the EMI stays the same. On a floating-rate loan, when the lender’s rate changes, banks usually adjust the tenure first, and sometimes the EMI.
Why is most of my early EMI going to interest?
Interest is charged on the outstanding balance, which is highest at the start. As you repay principal, interest falls and the principal share rises.
Is the Excel PMT result the same as the bank’s EMI?
Yes, for standard reducing-balance loans PMT uses the same formula. Small differences can come from rounding or the lender’s day-count method.
What is the fastest way to check my EMI?
Enter your loan amount, rate and tenure in the EMItotal EMI calculator to see your EMI, total interest and total payment instantly.
Leave a Reply