Pune
Whether you are planning your first home purchase or evaluating a top-up loan, knowing how to calculate home loan EMI empowers you to make informed financial decisions. EMI calculation is not complex and mastering it in Excel, by formula, or using an online calculator can save you from costly surprises.
This guide explains every method: the EMI formula, Excel calculations, pre-EMI interest, and real-world examples.
EMI (Equated Monthly Instalment) is the fixed monthly amount you pay to the bank to repay your home loan, comprising both principal repayment and interest. Your EMI remains fixed for floating- rate loans (unless the rate changes) and is constant for fixed- rate loans throughout the tenure.
The standard mathematical formula to calculate EMI is:
|
EMI Formula |
|
EMI = [P × R × (1+R)^N] ÷ [(1+R)^N − 1] |
Where:
EMI = [50,00,000 × 0.007292 × (1.007292)^240] ÷ [(1.007292)^240 − 1]
EMI ≈ ₹44,215 per month
Total amount paid = ₹44,215 × 240 = ₹1,06,11,600 | Total interest = ₹56,11,600
EMI ≈ ₹30,428 per month
Total interest paid = ₹24,77,040
|
Interest Rate |
10 Years (₹/month) |
15 Years (₹/month) |
20 Years (₹/month) |
25 Years (₹/month) |
|
8.00% |
₹60,664 |
₹47,782 |
₹41,822 |
₹38,591 |
|
8.50% |
₹61,993 |
₹49,238 |
₹43,391 |
₹40,258 |
|
8.75% |
₹62,667 |
₹49,984 |
₹44,215 |
₹41,117 |
|
9.00% |
₹63,341 |
₹50,713 |
₹44,986 |
₹41,960 |
|
9.50% |
₹64,694 |
₹52,211 |
₹46,607 |
₹43,688 |
|
10.00% |
₹66,075 |
₹53,730 |
₹48,251 |
₹45,435 |
Excel’s built-in PMT function calculates EMI directly:
|
Step |
Action |
Formula / Input |
|
1 |
Enter the loan amount in cell A1 |
5000000 (₹50 lakh) |
|
2 |
Enter the annual interest rate in A2 |
8.75% |
|
3 |
Enter tenure in years in A3 |
20 |
|
4 |
Calculate the monthly rate in A4 |
=A2/12 |
|
5 |
Calculate total months in A5 |
=A3*12 |
|
6 |
Calculate EMI in A6 |
=PMT(A4,A5,-A1) |
|
7 |
Result in A6 |
₹44,215 |
In any cell, type the following (replacing values with your loan details):
|
Excel Formula for EMI |
|
=((5000000*0.007292*(1+0.007292)^240)/((1+0.007292)^240-1)) |
Create a complete loan amortisation schedule in Excel:
This reveals how much of your early EMIs go to interest (a lot!) vs principal — motivating prepayments.
Pre-EMI applies to under-construction properties where the bank disburses the loan in tranches. You pay interest only on the disbursed amount until the full loan is disbursed and construction is complete.
|
Pre-EMI Formula |
|
Pre-EMI per month = Disbursed Amount × Monthly Interest Rate |
Loan: ₹50 lakh | Rate: 8.75% | Disbursed so far: ₹20 lakh
Pre-EMI = ₹20,00,000 × (8.75% ÷ 12) = ₹14,583 per month
Pre-EMI does not reduce your principal. Once full disbursement is done, regular EMI begins.
|
Pre-EMI payments are pure interest — they do not reduce your loan principal. Always opt for full EMI (even during construction) if you can afford it, to reduce your total interest outgo. |
|
Use SquareYards’ free Home Loan EMI Calculator to plan your purchase. Compare EMI across lenders and find the best deal at SquareYards.com. |
At 8.75% interest, the EMI for a ₹50 lakh home loan over 20 years is approximately ₹44,215 per month. The exact amount varies with the interest rate offered by your lender.
Use the formula: EMI = [P × R × (1+R)^N] ÷ [(1+R)^N − 1], where P is the loan amount, R is the monthly interest rate (annual rate ÷ 12 ÷ 100), and N is the number of months.
=PMT(monthly_rate, total_months, -loan_amount). For example, for ₹50 lakh at 8.75% for 20 years: =PMT(8.75%/12, 240, -5000000) returns ₹44,215.
Pre-EMI is the interest paid on the disbursed portion of a home loan for under-construction properties. It is interest-only — it does not reduce the principal balance.
Full EMI is financially better as it reduces the principal from day one, lowering total interest outgo. Pre-EMI is cheaper in the short term but results in higher total interest over the loan lifetime.
A general rule: total EMI (all loans) should not exceed 40–50% of your net take-home salary. On a ₹80,000 net salary, your total EMI capacity is ₹32,000–40,000 per month.
