How to Calculate Loan EMI: Formula, Examples & Excel Guide

Every loan comes with a monthly payment — but very few borrowers know exactly how that number is calculated. Understanding the EMI formula puts you in control: you can compare loan offers accurately, spot when a lender's numbers don't add up, and make smarter decisions about tenure, prepayment, and down payments.

An EMI (Equated Monthly Instalment) is the fixed amount you pay every month to repay a loan in full over a set period. It combines both principal repayment and interest into one consistent payment — which is why it stays the same every month even though the interest-to-principal ratio shifts dramatically over time.

Quick Overview: What Affects Your EMI?

Three variables completely determine your EMI. Change any one of them and the monthly payment changes.

The core tension: A longer tenure lowers your monthly payment but costs significantly more in total interest. A shorter tenure costs more each month but saves money overall. This trade-off is the central decision in any loan.

The EMI Formula

EMI = P × r × (1 + r)ⁿ ÷ ((1 + r)ⁿ − 1)

Variable Definitions

Converting Annual Rate to Monthly Rate

This is the step most people get wrong. The annual interest rate must be converted to a monthly rate before using the formula.

Monthly rate (r) = Annual Interest Rate ÷ 12 ÷ 100

Step-by-Step Worked Examples

Example 1: Personal Loan — $10,000 at 12% for 2 Years

Given:

• P = $10,000
• Annual rate = 12% → r = 12 ÷ 12 ÷ 100 = 0.01
• Tenure = 2 years → n = 2 × 12 = 24 months

Calculation:

EMI = 10,000 × 0.01 × (1.01)²⁴ ÷ ((1.01)²⁴ − 1)

Step 1: (1.01)²⁴ = 1.26973
Step 2: Numerator   = 10,000 × 0.01 × 1.26973 = 126.973
Step 3: Denominator = 1.26973 − 1 = 0.26973
Step 4: EMI         = 126.973 ÷ 0.26973 = $470.73

✅ Monthly EMI = $470.73

Total cost breakdown:

Example 2: Home Loan — $250,000 at 7% for 20 Years

Given:

• P = $250,000
• Annual rate = 7% → r = 7 ÷ 12 ÷ 100 = 0.005833
• Tenure = 20 years → n = 20 × 12 = 240 months

Calculation:

EMI = 250,000 × 0.005833 × (1.005833)²⁴⁰ ÷ ((1.005833)²⁴⁰ − 1)

Step 1: (1.005833)²⁴⁰ = 3.9701
Step 2: Numerator   = 250,000 × 0.005833 × 3.9701 = 5,791.85
Step 3: Denominator = 3.9701 − 1 = 2.9701
Step 4: EMI         = 5,791.85 ÷ 2.9701 = $1,950.12

✅ Monthly EMI = $1,950.12

Total cost breakdown:

Eye-opener: On a 20-year home loan at 7%, you pay nearly as much in interest as you borrowed. This is why making even small extra payments early in a mortgage has an outsized impact.

Example 3: EMI Comparison — Same Loan, Different Tenures

Loan: $15,000 at 10% annual interest

Key takeaway: Choosing a 1-year tenure over 5 years saves $3,066 in interest — but requires paying $998.79 more per month. The right choice depends entirely on your cash flow.

Calculate EMI in Excel & Google Sheets

Excel's built-in PMT function calculates EMI in a single formula — no manual arithmetic needed.

The PMT Function

=PMT(rate, nper, pv)

Practical Examples

Why negative pv? Excel's PMT function uses cash flow sign conventions — money going out (a loan you receive) is negative. Entering -pv makes the EMI result display as a positive number.

Additional Useful Formulas

-- Total repayment
=PMT(rate/12, nper, -pv) * nper

-- Total interest paid
=(PMT(rate/12, nper, -pv) * nper) - pv

-- Remaining balance after k payments
=PV(rate/12, nper-k, PMT(rate/12, nper, -pv))

-- Interest portion of payment k
=IPMT(rate/12, k, nper, -pv)

-- Principal portion of payment k
=PPMT(rate/12, k, nper, -pv)

Build a Full Amortization Schedule in Excel

Put these headers in row 1: Month | Opening Balance | EMI | Interest | Principal | Closing Balance

-- Row 2 (Month 1) formulas, assuming:
-- B1 = Principal, B2 = Annual Rate, B3 = Tenure (months)

A2: 1
B2: =B1                                          (opening balance = principal)
C2: =PMT($B$2/12, $B$3, -$B$1)                  (fixed EMI)
D2: =B2 * ($B$2/12)                              (interest = balance × monthly rate)
E2: =C2 - D2                                     (principal = EMI − interest)
F2: =B2 - E2                                     (closing balance)

-- Row 3 onwards:
B3: =F2                                          (opening = previous closing)
-- Drag C3:F3 down for all n months

Understanding the Amortization Schedule

Every EMI payment is split between interest and principal — but the ratio changes dramatically over the loan's life.

Sample Amortization: $10,000 at 12% for 24 Months (EMI = $470.73)

What this reveals:

• In Month 1, $100 of your $470.73 payment is interest (21.2%)
• By Month 18, only $29.52 is interest (6.3%)
• The earlier you make extra payments, the more principal you eliminate — and the less future interest you owe

How to Reduce Your EMI

Option 1: Increase Your Down Payment

Reducing the principal directly reduces the EMI in exact proportion.

Example: On a $300,000 home loan at 8% for 15 years:

• 10% down ($30,000) → Loan = $270,000 → EMI = $2,578
• 20% down ($60,000) → Loan = $240,000 → EMI = $2,291 (saves $287/month)
• 30% down ($90,000) → Loan = $210,000 → EMI = $2,005 (saves $573/month)

Option 2: Negotiate a Lower Interest Rate

Even a 1% rate reduction creates meaningful savings over time.

$200,000 loan for 15 years:

A 3% rate reduction saves over $61,000 in total interest on this loan.

Option 3: Make Prepayments

Paying extra principal at any point reduces your outstanding balance — which reduces all future interest charges.

Impact of a $5,000 prepayment at Month 12 on the $10,000 / 12% / 24-month loan:

• Without prepayment: Total interest = $1,297.52
• With $5,000 prepayment at Month 12: Remaining balance ≈ $5,211 → pay off in ~11 more months
• Total interest paid ≈ $820 → saves ~$477

Option 4: Extend the Tenure (Use With Caution)

Extending tenure reduces monthly EMI but increases total interest significantly.

$50,000 at 9% annual rate:

Rule of thumb: Only extend tenure if the lower EMI genuinely improves your monthly cash flow. Never extend just to "afford" a larger loan — you're paying for that comfort in interest.

The 30–40% EMI Rule

Financial advisors universally recommend keeping your total monthly EMI obligations below 30–40% of your net take-home salary.

Maximum Safe EMI = Monthly Take-Home Salary × 0.30

Example:

• Monthly salary: $5,000
• Maximum safe total EMI: $5,000 × 0.30 = $1,500/month
• This includes all loans combined — home, car, personal, student

Why 30–40%? The remaining 60–70% needs to cover rent/mortgage (if separate), food, utilities, insurance, savings, and emergency fund contributions. Exceeding 40% EMI-to-income ratio is a leading indicator of financial stress.

EMI for Different Loan Types

The same formula applies to all loan types, but typical rates and tenures vary significantly:

Frequently Asked Questions

Q: Why does my EMI stay the same but the interest portion changes every month? Because interest is calculated on the outstanding balance, which decreases with every payment. As the balance falls, the interest component shrinks and the principal component grows — but the total EMI stays constant.

Q: What happens if I miss an EMI payment? Most lenders charge a late payment penalty (typically 1–2% of the overdue amount) and report the missed payment to credit bureaus. Multiple missed payments can trigger loan default proceedings. Always contact your lender before missing a payment — most offer restructuring options.

Q: Is it better to make a lump-sum prepayment or increase my monthly EMI? Both reduce total interest, but a lump-sum prepayment has an immediate large impact on the outstanding balance. Increasing monthly EMI is more sustainable for most borrowers. If your lender allows it, a combination of both is optimal.

Q: Does the EMI formula work for mortgages? Yes — the same formula applies to all amortising loans including mortgages. The only difference is that mortgage payments may include escrow for property taxes and insurance, which are added on top of the calculated EMI.

Q: What is a floating rate EMI? A floating (variable) rate loan has an interest rate that changes with market conditions. When the rate changes, lenders typically either adjust the EMI amount or adjust the remaining tenure — check your loan agreement for which method applies.

Quick Reference Summary

Use our free Loan EMI Calculator to get your monthly payment, total interest, and full amortization schedule instantly — no spreadsheet required.