How EMI Is Calculated: Formula, Example and Excel Method

The EMI formula

An EMI (equated monthly instalment) is the fixed amount you pay a lender every month until the loan is cleared. Banks use the reducing-balance formula:

EMI = P × r × (1+r)^n ÷ ((1+r)^n − 1)

• P is the principal, the amount you borrow.
• r is the monthly interest rate. Divide the yearly rate by 12 and then by 100. A rate of 8.5% a year gives r = 0.085 ÷ 12 = 0.007083.
• n is the number of monthly instalments. A 20-year loan has n = 240.

The formula sets the EMI so that the loan balance reaches exactly zero after the last payment, with interest charged each month only on what you still owe.

Worked example: ₹50 lakh at 8.5% for 20 years

1. P = 50,00,000, r = 0.007083, n = 240.
2. (1+r)^n = 1.007083^240 ≈ 5.4412.
3. EMI = 50,00,000 × 0.007083 × 5.4412 ÷ (5.4412 − 1) = ₹43,391 a month.

Over 240 months you pay ₹1,04,13,879. That is ₹54,13,879 of interest on a ₹50 lakh loan, so you repay about 2.08 times what you borrowed.

How each EMI splits into interest and principal

The EMI is the same every month, but its contents change.

In month 1 the interest is 50,00,000 × 0.007083 = ₹35,417. The remaining ₹7,974 of the EMI reduces the principal. About 82% of your first payment is interest.

In month 2 the balance is ₹49,92,026, so interest is ₹35,361 and ₹8,030 goes to principal. Month by month the interest share shrinks and the principal share grows.

Over the first year, about 81% of what you pay is interest. This is why prepaying early saves so much: you cut the balance while it is still large. The prepayment calculator shows the effect for your own loan.

Calculate EMI in Excel or Google Sheets

Use the PMT function:

`=PMT(rate/12, years*12, -loan_amount)`

For the example above: `=PMT(8.5%/12, 20*12, -5000000)` returns 43,391. The minus sign before the loan amount makes the result positive. To see the interest part of month 1, use `=IPMT(8.5%/12, 1, 240, -5000000)`, and for the principal part use `=PPMT(8.5%/12, 1, 240, -5000000)`.

What changes the EMI

• Loan amount: the EMI scales in direct proportion. Double the loan and you double the EMI.
• Interest rate: a higher rate raises the EMI. On ₹50 lakh for 20 years, going from 8.5% to 9.5% raises the EMI from ₹43,391 to ₹46,607.
• Tenure: a longer tenure lowers the EMI but raises the total interest sharply. The same ₹50 lakh at 8.5% costs ₹49,237 a month over 15 years and ₹38,446 over 30 years, but the 30-year loan costs ₹88.4 lakh in interest versus ₹38.6 lakh.

Flat rate versus reducing rate

Some lenders, mostly for two-wheelers, used cars and small personal loans, quote a flat rate. A flat rate charges interest on the full original amount for the entire tenure. An 8% flat rate over 3 years is about 14.5% on a reducing basis. Always convert before you compare, using the flat to reducing rate calculator.

Why your lender's EMI can differ by a few rupees

Banks may round the monthly rate, use a daily interest calculation, or start the first month on a part-month. They may also add processing fees or insurance to the loan amount. Ask for the amortisation schedule, which lists every payment.

FAQ

Is EMI calculated on reducing balance or flat?

Banks and housing finance companies use the reducing-balance method for home, car and most personal loans. Some dealers and small lenders quote flat rates, which cost much more in real terms.

Does the EMI stay the same for the whole loan?

On a fixed-rate loan, yes. On a floating-rate loan the lender may change the EMI or the tenure when the benchmark rate moves.

What is the EMI for ₹10 lakh?

At 10% for 10 years it is ₹13,215. At 9.5% for 5 years it is ₹21,002. Enter your own rate and tenure in the EMI calculator.

Why is so much of my early EMI interest?

Interest is charged on the outstanding balance, which is highest at the start. As you repay principal, the balance and the interest on it fall.