Home Loan EMI Calculator 2023 (Free Excel sheet)

So you are looking for Home Loan EMI Calculator (Free Excel sheet download) for the year 2023?

A home loan is probably the biggest loan you will ever take in your life. At least for the vast majority of people who aren’t into business requiring business loans. Right? So you should understand how to calculate EMI on home loan correctly even if you have to use a loan EMI calculator in excel or otherwise.

The banks will no doubt be willing to tell you the EMI you need to service, but it’s in your interest to understand the calculation of home loan EMIs

In this post, we discuss how to calculate home loan EMI yourself and also provide you with Free Excel-based Home Loan EMI Calculator (Free download)

Many people think that EMI calculations are tough and even if they tried, they won’t be able to figure things out. Wrong! It’s on the contrary quite easy.

And once you understand how EMI works, you will be in a better position to understand how changing the variables like principal, loan interest rate and tenure results in changing the monthly EMIs.

Let’s first see how these EMIs work.

How Home Loan EMI works?

Do you already know what EMI is?

EMI stands for Equated Monthly Installment. It’s a monthly payment that the borrower makes towards repayment of the home loan. The best part about EMI is that you get to repay back the loan in small parts which are easier for most salaried individuals. If something is unaffordable for you as of now, you can purchase it by taking a loan and repaying back in smaller EMIs over a period of time.

The EMI is made up of 2 parts: i) principal amount being repaid and ii) accrued interest. Or to put it simply, a part of the home loan EMI goes towards interest payment while the other part is used to reduce the outstanding loan principal.

The EMI amount is fixed for the full tenure. But the initial EMIs contribute more towards the interest component of the loan. And in the later years of the loan tenure, the EMIs contribute more towards repayment of the loan principal.

And what are the factors that impact EMI?

• Loan amount: Higher the loan amount, the higher the EMI.
• Repayment tenure: Longer the loan tenure, the lower will be your loan EMI.
• Loan interest rate: Higher the interest rate of a loan, the higher will be your EMI
• Down payment amount: This is an indirect factor. The bigger the downpayment, the lower will be the loan amount. And in turn, the lower will be the monthly EMI.

Home Loan EMI Calculations

Let’s see how home loan EMI works.

Suppose you take a home loan of Rs 50 lakh for 25 years at 8%.

We use the excel formula PMT to calculate home loan EMI.

Monthly EMI = PMT (Monthly Interest Rate, Tenure in Months, Loan Amount)

This is the loan EMI calculator excel sheet formula

Using the following values in the PMT formula in excel:

• Loan Amount = Rs 50 lakh
• Loan Tenure in Months = 25 years * 12 = 300 months
• Monthly Interest Rate = 8%/12 = 0.666%

We get, PMT (0.006667, 300, 5000000) = Rs 38,591.

That is, the monthly EMI is Rs 38,591 per month (in EMI).

Now let’s see what part goes towards principal repayment and what part goes towards interest payment?

Month 1:

• Outstanding Loan Principal at the beginning of month = Rs 50 lakh
• Interest to be paid for month = 50 lakh * 8%/12 = Rs 33,333
• Principal payment for month = EMI – Interest Payment = 38,591 – 33,333 = Rs 5258
• Outstanding Loan Principal at the end of month = Rs 49,94,742

Month 2:

• Outstanding Loan Principal at the beginning of month = Rs 50 lakh – Rs 5258 = Rs 49,94,742
• Interest to be paid for month = 49,94,742 * 8%/12 = Rs 33,298
• Principal payment for month = EMI – Interest Payment = 38,591 – 33,298 = Rs 5292
• Outstanding Loan Principal at the end of month = Rs 49,89,450

And this continues for one by one for all the months in the loan tenure.

Every month, the Interest payment for the month goes down while the principal repayment goes up.

If you look at the annual loan schedule below, you will realize that the interest component paid is higher during the initial years. During the latter years, the principal component is higher. With each passing month’s EMI, the interest paid decreases and the principal repaid increases. Many people know this. But many don’t. Do spend some time studying the table below:

So don’t think that if it’s a 25-year loan, then you would have paid back half your loan in 12-13 years. The actual amount you have repaid would be about 23-24% only.

You can see the bulk of the EMI payments during the initial years goes towards interest payment. If you have a home loan, just ask your lender for the home loan amortization table. It will indicate exactly your outstanding home loan amount at any point in time. Or you can easily get a quick idea of the same using online loan amortization table calculators.

Home Loan EMI Calculator (Free Excel Download) + Home Loan Amortization (Free Excel Download) 2023

To make it easy for you, here is a free excel based Home Loan EMI Calculator that also shows you the loan amortization schedule.

You just need to provide inputs like the loan amount, loan tenure and interest rate. And it will instantly tell you the loan EMI.

And if you don’t know what Loan Amortization Schedule is, then here is a short explainer – A loan amortization schedule shows the monthly/annual breakup between the principal and interest components of the home loan. It is something similar to the schedule shown in the table earlier.

So you can download this calculator free here:

Home Loan EMI Calculator Amortization Schedule (Free Excel Download 2023)

The calculator will also give you the EMI principal and interest calculator breakup in excel.

Now let’s move forward.

Impact of interest rate HIKE on your loan EMI & repayment schedule

Since most home loans are floating rate loans, the actual loan rate changes over the course of the loan depending on whether RBI policy rates are being hiked or cut.

If the interest rates are hiked and the lender passes on the hike to borrowers, then your home loan rate will increase.

This will have either of 2 impacts:

• Increasing the tenure but the loan EMI remains the same
• Increases the EMI but the loan tenure remains the same

Impact of interest rate CUT on your loan EMI & repayment schedule

This will have either of 2 impacts:

• Loan tenure decreases but the loan EMI remains the same
• Decreases EMI but the loan tenure remains the same

In general, the lenders have 2 options in case of loan interest rate changes: Either i) change the EMI and keep the tenure the same, or ii) keep the EMI constant and change the loan tenure.

Many people are looking to maximize the tax benefits of home loans. But if you wish to manage your home loans wisely without compromising other goals, do read this detailed post on managing home loans.

And it goes without saying that you might be excited to take home loan due to rate cuts, but it’s always advisable to assess other factors as well and not just depend on the loan interest rates. Also, read why taking personal loan for downpayment is not advisable.

Further readings:

• Monthly EMI for Rs 25 lakh home loan
• Monthly EMI for Rs 30 lakh home loan
• Monthly EMI for Rs 50 lakh home loan
• Monthly EMI for Rs 40 lakh home loan 
• Monthly EMI for Rs 1 crore home loan
• Shorter loan tenure saves money in interest paid
• How much home loan can you get?
• Short story of Indian real estate
• Don’t use all savings for house downpayment

So that was how you can use the Home Loan EMI calculator (2023) India to calculate the EMI on your home loan. You can easily use this loan EMI calculator excel sheet download to calculate your home loan EMIs and create a loan amortization schedule.