Every Indian bank — SBI, HDFC, ICICI, Axis, Kotak — computes EMI the same way: a closed-form equation on the reducing- balance method. This post walks through the formula, shows a full worked example for a ₹50 lakh home loan, and compares Excel vs online calculators for getting it right to the paisa.
The EMI formula
EMI = P × r × (1 + r)n / ((1 + r)n − 1)
Where:
- P = principal (the amount disbursed by the lender)
- r = monthly interest rate in decimal (annual rate / 12 / 100). An 8.5% annual rate becomes
r = 0.085 / 12 = 0.00708333... - n = tenure in months (20 years × 12 = 240)
This is the standard reducing-balance formula used by every retail lender in India. The alternative “flat rate” formula inflates the effective rate by roughly 1.8× for short tenures and is only seen in some consumer-durable and tractor loans.
Worked example: ₹50L at 8.5% for 20 years
- P = ₹50,00,000
- r = 8.5 / 12 / 100 = 0.00708333
- n = 240
- (1 + r)n = (1.00708333)240 ≈ 5.4481
- EMI = 50,00,000 × 0.00708333 × 5.4481 / (5.4481 − 1) = 50,00,000 × 0.00708333 × 5.4481 / 4.4481 ≈ ₹43,391
Over 240 months, you pay ₹43,391 × 240 = ₹1,04,13,840. Principal returned: ₹50,00,000. Interest: ₹54,13,840. That’s why long-tenure home loans cost ~2× the sticker price by the time the last EMI clears.
Principal vs interest over time
Although the EMI is fixed, its composition changes every month:
- Month 1: Interest ≈ ₹50,00,000 × 0.00708333 = ₹35,417. Principal paid = 43,391 − 35,417 = ₹7,974. The rest of the principal rolls into month 2.
- Month 120 (halfway): Outstanding principal is still ~₹34.8L. Interest ≈ ₹24,650. Principal ≈ ₹18,741.
- Month 240 (last): Interest ≈ ₹305. Principal ≈ ₹43,086 — near-final principal chunk clears.
This front-loaded interest pattern is why early prepayments save disproportionately more interest. A ₹5L prepayment in year 2 can shave ₹12-15L off total interest; the same prepayment in year 18 saves just ₹50-70K. Our EMI Calculator lets you model one-time and recurring prepayments and see the revised amortization schedule.
EMI in Microsoft Excel
Excel’s PMT() function implements the same formula. For the ₹50L / 8.5% / 20-year example:
=PMT(8.5%/12, 20*12, -5000000)
Returns 43,391.16. Notes:
- Rate is the per-period rate (divide annual by 12).
- Principal is negative because it’s a cash outflow to you (the borrower gets it, then pays back).
- Result is positive EMI. Put it through
ROUND(..., 0)if you want integer rupees like the banks show.
Excel is great for one-shot calculations. But building a full amortization schedule in Excel takes 5-10 minutes of formula setup and makes prepayment-scenario comparisons painful. That’s where an online tool wins.
Floating-rate vs fixed-rate
Most Indian home loans are floating rate — the lender resets the rate to “RLLR + spread” every time the RBI repo changes. When that happens, banks typically hold your EMI constant and adjust the tenure instead. You can request EMI adjustment instead of tenure adjustment; it’s a one-line email.
Fixed-rate home loans are rare; they carry 1-2% spread over floating and a 2-4% prepayment penalty. Individual borrowers on floating-rate home loans pay no prepayment penalty under RBI / NHB rules.
Flat rate vs reducing balance
A “12% flat” personal loan or tractor loan is NOT the same as a 12% reducing-balance loan. Flat rate applies the interest to the original principal every month, regardless of how much is outstanding. For a ₹1L / 12% / 3-year loan:
- Reducing-balance EMI ≈ ₹3,321, total interest ≈ ₹19,575. Effective rate: 12%.
- Flat-rate EMI ≈ ₹3,778, total interest ≈ ₹36,000. Effective rate ≈ 22%.
Always ask for the reducing-balance / APR number. Regulators have pushed banks and NBFCs to disclose it since 2019, but some NBFC small-ticket lenders still quote flat rates.
Why online calculators beat spreadsheets for real decisions
- Prepayment modeling: what happens if you prepay ₹3L in year 3? Our tool shows the revised tenure and interest-saved instantly.
- Chart: the principal-vs-interest breakdown on a chart tells you the story better than a column of numbers.
- PDF export: the full amortization schedule for your records or to email your CA.
- Sharing: our URLs carry all inputs as query parameters — send a spouse or advisor a link, no re-entry.
Common mistakes
- Using annual rate instead of monthly in the formula. Divide by 12 first.
- Forgetting compound effect. (1 + r)n is power-of, not multiplication. In Excel use
(1+r)^n, not(1+r)*n. - Rounding too early. Monthly EMI fractional rupees accumulate over 240 months. Banks keep 2-decimal precision internally and round only on the last EMI. We use 40-digit decimal arithmetic internally for the same reason.
- Ignoring processing fees. A 1% processing fee on ₹50L is ₹50,000 — larger than the first month’s interest. Factor it in when comparing banks.
Run it yourself
The EMI Calculator runs this exact formula with support for prepayments, step-up EMIs, and a chart + PDF export. If you’re buying a home, use the Home Loan Calculator which adds stamp duty (all 25 states), registration, and tax savings under Sections 24(b) and 80C to show true cost of ownership.
Sources
- RBI Master Direction — Interest Rate on Advances, which requires banks to disclose the reducing-balance / APR rate.
- NHB (National Housing Bank) circulars — prohibition on prepayment penalty for individual borrowers on floating-rate home loans.
- SBI, HDFC, ICICI published EMI tables — cross-checked in our test fixtures to within ₹1.