Home Loan Calculation Method & EMI Prepayment [EXCEL]

One of the most asked questions on my YouTube Channel Videos are – What is the Home Loan Calculation Method and What is the Loan EMI Prepayment Formula?

Home Loan Calculation Method & Formula is very simple using Home Loan Excel Calculator I have provided below. By making Home Loan Prepayments you can Save Home Loan Interest since loan prepayments help you to reduce outstanding principal amount balance that is remaining. Reducing tenure while making prepayments is better than reducing EMI of home loan to save maximum interest you pay to the bank.

DOWNLOAD HOME LOAN EMI CALCULATOR

Click below button to DOWNLOAD Home Loan Calculator (EMI & Prepayments) in Excel:

Check out More online Calculators here including Income Tax, PPF, SIP, Home Loan Calculator and many more..

Watch below video to know more about Home Loan EMI Calculation method and prepayment calculator in excel.

Home Loan Calculation Method & Prepayment Excel Video

Watch more Excel Videos Here

To know the Home Loan Calculation Method and Formula, you should know How Home Loan EMI is Calculated, What are the components of your Loan EMI (Principal & Interest Amounts), How you can save home loan Interest by making prepayments. Let’s see all this one by one.

What is Home Loan EMI

• The full form of EMI is Equated Monthly Installment. 
• This is the amount you pay every month for the home loan you have taken. 
• EMI depends on the home loan interest rate, tenure (for how many years you are taking loan) and also it depends on your yearly Income, since you should be able to afford the resulting EMI of your home loan from your Income. 
• In above video, I have explained how home loan EMI is calculated using PMT formula of Excel.
• Your EMI consists of 2 things: Principal Amount & Interest Amount
• Principal Amount is less initially and it increases with tenure. This means, as you pay your EMI, principal amount with every EMI will increase
• Interest Amount is high initially and it decreases with tenure, This means, more interest is taken from you initially compared to when your loan is about to get over
• This proves that you should try to make prepayments during the initial months of your loan to save maximum home loan interest compared to making prepayments during the end of loan

Below is the screenshot showing Principal Amount less and Interest Amount high during the initial months of loan:

As seen in above screenshot, Principal amount (less initially) is increasing with tenure and Interest Amount (high initially) is decreasing with tenure.

Love Reading Books? Here are some of the Best Books you can Read: (WITH LINKS)

More Recommended Books Here

Home Loan Calculation Method

You can easily use PMT formula of Excel to calculate home loan EMI. Here is the formula:

EMI = PMT(rate, nper, pv, [fv], [type])
rate – Yearly Interest rate / 12 / 100
nper – Number of months remaining (tenure remaining)
pv – Current outstanding Principal value
fv – 0
type – 0

As you can see above, rate is the yearly home loan interest rate divided by 12 to get monthly interest rate and then divided by 100. nper is the number of months remaining in loan, pv is the outstanding principal amount remaining in your loan.

Here’s the screenshot of the PMT formula resulting the EMI example:

Principal Amount = Rs. 30 Lacs
Interest rate = 7%
Tenure = 10 Years (120 Months)
EMI = Rs. 34,832.54

Home Loan Interest Rate

• As mentioned above, home loan interest rate is one of the factors affecting the interest you’ll be paying throughout your loan tenure. 
• Higher the interest rate, more Interest you’ll be paying. Less Interest rate means you’ll pay less Interest throughout your loan tenure. 
• You should research and try to find your loan with less interest rate to pay less interest for your home loan. 
• Currently the home loan interest rate in India can range anywhere between 7% – 10% per year. If you get home loan below this range, it’s a good bet for you.

ALSO READ: Home Loan with Variable Interest Rates

How Home Loan Calculator can help you

The home loan excel calculator link I have provided you at the bottom of this post will help you to see your EMI for various combinations of loan principal amount, interest rate and tenure.

I have also provided the option of loan prepayments incase you want to calculate how home loan prepayments will help you reduce loan interest you’ll be paying throughout the loan tenure. You can download the calculator and check your EMI with combination of above inputs. Below are some features of this home loan calculator:

• Calculate your home loan EMI with 3 inputs: Principal Amount, Interest rate & Tenure
• Check your Principal Amount and Interest Amount for every EMI you pay throughout your loan tenure
• Make prepayments on any given month and see the reduction in your home loan interest and the interest amount you’ll be saving
• Comparison of the total Amount you’ll be paying throughout your loan tenure with and without loan prepayments
• Also, you can see the total amount you’ll be saving in loan tenure once you make any home loan prepayment

How to Save Home Loan Interest

• Home Loan Interest can be saved by making loan prepayments when your loan is already disbursed
• If you are looking to save loan interest before you apply for loan, you should go with lowest interest rate and lowest tenure
• While you go for lowest tenure, your EMI will be higher since you are taking loan for less period of time
• Another way to save loan interest is the increase your EMI every year by some percentage, in which case your loan tenure will be reduced that reduces home loan interest

Home Loan Prepayment Examples & Formula

As mentioned above, you can save home loan interest paid by you by making prepayments.

• While making prepayments, you can either choose to reduce EMI or Tenure
• Reducing tenure is the better option since it reduces the term for which you’ll be paying EMI
• Reducing EMI will still keep the tenure same, hence you’ll be paying more interest compared to reducing tenure option
• In case of variable interest rate, you can prepay your loan any number of times in a FY
• In case of fixed interest rate, banks impose some rules related to when your can make prepayment and how frequently the prepayments can be done. It depends on the bank from which loan is taken

Home Loan Prepayment Calculator Reduce EMI or Tenure Excel

When you make Home Loan Prepayments, reducing tenure is better compared to reducing EMI. That’s because, keeping the EMI same and reducing the tenure will make you pay EMI for less number of months compared to reducing EMI in which case you keep paying EMIs for original tenure.

Watch below video to understand this with the help of examples

Home Loan Prepayment Reduce Tenure Video

Watch more Excel Videos Here

Income Tax Saving using Home Loan

One important factor about home loan apart from you buying your own home is, home loan helps you Save Income Tax under Section 80C and Section 24.

• Home Loan Principal Amount you pay can be claimed under Section 80C (Maximum Rs. 1.5 Lacs in a FY)
• Home Loan Interest Amount you pay can be claimed under Section 24 (Maximum Rs. 2 Lacs in a FY)
• Maximum of Rs. 3.5 Lacs can be claimed by you in a Financial Year which helps you save Income Tax by reducing your Taxable Income.

How to Save Income Tax Video

Watch more Excel Videos Here

Income Tax Calculation FY 2024-25 Video

Frequently Asked Questions (FAQs)

What does an EMI mean?

EMI refers to the ‘Equated Monthly Installment’ which is the amount you will pay on a specific date each month till the loan is repaid in full. The EMI comprises of the principal and interest components which are structured in a way that in the initial years of your loan, the interest component is much larger than the principal component, while towards the latter half of the loan, the principal component is much larger.

What is a Home loan and how does it work?

Home loans are availed either for purchase of an under-construction or a ready property from a developer, purchase of a resale property, to construct a housing unit on a plot of land, to make improvements and extensions to an already existing house and to transfer your existing home loan.

When can I make a home loan application?

You can apply for a Home Loan whilst you are working abroad, to plan for your return to India in the future. You can apply at any time once you have decided to purchase or construct a property, even if you have not selected the property or the construction has not commenced.

When does my home loan EMIs start?

EMI’s begins from the month subsequent to the month in which disbursement of the loan is done. For loans for under-construction properties EMI usually begins after the complete home loan is disbursed but customers can choose to begin their emi’s as soon as they avail their fist disbursement and their emi’s will increase proportionately with every subsequent  disbursement. For resale cases, since the whole loan amount is disbursed in one go, emi on the whole loan amount start from the subsequent to the  month of disbursement

How does your home loan repayment work?

A home loan is usually repaid through Equated Monthly Instalments (EMI).The EMI comprises of the principal and interest components which are structured in a way that in the initial years of your loan, the interest component is much larger than the principal component, while towards the latter half of the loan, the principal component is much larger.

How can I save home loan Interest?

There are many ways to save home loan Interest. You can make prepayments in your ongoing loan to save interest, you can increase your loan EMI every year by some percentage to reduce your loan interest. If you are planning to take a home loan, you should go for less interest rate and less tenure to keep your loan interest less throughout your loan tenure.

Get EARLY Retirement!

Use Retirement Excel Calculator that will help you to Retire Early.
Click below button to download Retirement Calculator in Excel:

Watch how Retirement Calculator in Excel Works

Income Tax Calculator App – FinCalC

For Income Tax Calculation on your mobile device, you can Download my Android App “FinCalC” which I have developed for you to make your income tax calculation easy.

What you can do with this mobile App?

• Calculate Income Tax for new FY 2024-25 and previous FY 2023-24
• Enter estimated Investments to check income tax with Old and New Tax Regime
• Save income tax details and track regularly
• Know how much to invest more to save income tax
• More calculators including PPF, SIP returns, Savings account interest and lot more

Use Popular Calculators:

• Income Tax Calculator
• Home Loan EMI Calculator
• SIP Calculator
• PPF Calculator
• HRA Calculator
• Step up SIP Calculator
• Savings Account Interest Calculator
• Lump sum Calculator
• FD Calculator
• RD Calculator
• Car Loan EMI Calculator
• Bike Loan EMI Calculator
• Sukanya Samriddhi Calculator
• Provident Fund Calculator
• Senior Citizen Savings Calculator
• NSC Calculator
• Monthly Income Scheme Calculator
• Mahila Samman Savings Calculator
• Systematic Withdrawal Calculator
• CAGR Calculator

I’d love to hear from you if you have any queries about Personal Finance and Money Management.

JOIN Telegram Group and stay updated with latest Personal Finance News and Topics.

Download our Free Android App – FinCalC to Calculate Income Tax and Interest on various small Saving Schemes in India including PPF, NSC, SIP and lot more.

Follow the Blog and Subscribe to YouTube Channel to stay updated about Personal Finance and Money Management topics.