When you commit yourself to a long-term home loan, then many start to feel to repay the home loan at the earliest. Sharing with you prepay home loan calculator which can be downloaded at free.

One of the longest borrowing commitments is a home mortgage. Of course, individuals look for ways to prepay it as soon as possible. Thus, allow me to share with you the numerous choices for prepaying your mortgage as well as an excel spreadsheet to determine the overall impact.

Let us first understand which options an individual has to opt for and their impact on the home loan repayment schedule.

### # Regular repayment

In this case, let us take an example you have opted for Rs.50 lakh of a home loan. The tenure is 25 years. If we consider the rate of interest at 8.5%, then the monthly EMI will be Rs.40,261.

If you are paying this EMI for the next 25 years, then you will pay a total of Rs.70,78,409 as an INTEREST during the whole loan tenure. If we consider the principal of Rs.50 lakh also, then in total you are paying Rs.1,20,78,409.

This is a normal procedure many follow and can be applied to those who don’t want to commit either regularly or once in a while to prepay the home loan.

What if we alter the tenure here?

10-Yrs Loan Tenure – EMI Rs.61,993 and the total interest payable is Rs.24,39,142.

15-Yrs Loan Tenure – EMI Rs.49,237 and the total interest payable is Rs.38,62,656.

20-Yrs Loan Tenure – EMI Rs.43,391 and the total interest payable is Rs.54,13,879.

25-Yrs Loan Tenure – EMI Rs.40,261 and the total interest payable is Rs.70,78,409

Hence, you noticed that if the loan tenure is long-term, even though the monthly EMI will reduce but in the end, you will pay more interest to the lender.

### # Prepay an additional equal amount along with regular EMI

Let us take an example of 25 years loan, Rs.50,00,000 loan amount with 8.5% as interest. We noticed above that the EMI will be Rs.40,261.

a) What if we pay an additional Rs.5,000 (around 10% of EMI) throughout the whole tenure along with the regular EMI of Rs.40,261?

The loan will be over by around 18 years rather than the original 25 years!!

The total interest payable in this case will be Rs.47,82,338 rather than the original total interest of Rs.70,78,409. Savings of around Rs.22,96,071!!

b) If you pay Rs.10,000 additional amount along with regular EMI, then the loan will be over by around 14 years. The total interest payable will be Rs.36,84,545 instead of the original total interest of Rs.70.78,409. Savings of around Rs.33,93,865!!

c) If you pay Rs.20,000 additional amount along with regular EMI, then the loan will be over by around 11 years. The total interest payable will be Rs.25,64,751 instead of the original total interest of Rs.70.78,409. Savings of around Rs.45,13,658!!

This seems to be an effortless way. As your income increases on yearly basis (the minimum of around 5%), then considering this effortless way will actually reduce your home loan burden in a big way.

### # Prepaying once a year

Many even though their salary increases on yearly basis, find it difficult to increase their loan repayment mainly because they feel their expenses also increase in the same ratio. Hence, for few, the loan repayment option is only whenever they get a lump sum as a yearly bonus from the employer. In such a scenario, what will be the impact on your home loan repayment?

a) If you prepay Rs.1,00,000 a year from the beginning of loan tenure, then you will end up in paying the total interest of Rs.38,61,811. The loan tenure will end in around 15 years rather than the earlier 25 years. The savings in interest is Rs.32,16,598!!

b) If you prepay Rs.2,00,000 a year from the beginning of loan tenure, then you will end up in paying the total interest of Rs.27,00,323. The loan tenure will end in around 11 years rather than the earlier 25 years. The savings in interest is Rs.43,78,087!!

b) If you prepay Rs.3,00,000 a year from the beginning of the loan tenure, then you will end up paying the total interest of Rs.20,73,178. The loan tenure will end in around 9 years rather than the earlier 25 years. The savings in interest is Rs.50,05,231!!

### # Prepay an additional equal amount along with regular EMI + Prepaying once a year

In this option, you pay an additional amount along with regular EMI and also once a year you repay the lump sum.

a) Let us assume you are repaying regular Rs.5,000 additionally along with regular EMI and also once a year prepaying Rs.1 lakh as a lump sum, then the loan will finish in 13 years instead of the earlier 25 years. The total interest payable is Rs.32,99,543. The total interest saving is Rs.37,78,866!!

b) Let us assume you are repaying regular Rs.5,000 additionally along with regular EMI and also once a year prepaying Rs.2 lakh as a lump sum, then the loan will finish in 11 years instead of the earlier 25 years. The total interest payable is Rs.25,65,721. The total interest saving is Rs.45,12,688!!

c) Let us assume you are repaying regular Rs.5,000 additionally along with regular EMI and also once a year prepaying Rs.3 lakh as a lump sum, then the loan will finish in 9 years instead of the earlier 25 years. The total interest payable is Rs.21,20,931. The total interest saving is Rs.49,57,479!!

You noticed from all of the above available options that prepaying an additional equal amount along with the EMI and once-a-year lump sum seems to be a far better option.

However, to execute such an option, you have to be ready to sacrifice your certain monthly income and also yearly income. This can be possible only if you have a consistent commitment and a zeal to repay the home loan at the earliest.

Caution – Many salaried continue the home loan with the intention that there is a tax benefit in retaining a home loan. It is a complete MYTH.

Let us take an example Mr.A is having a home loan and yearly he is paying Rs.1.5 lakh as interest to the banker. Mr.B does not have a home loan. Let us take an example Mr.B investing in PPF yearly Rs.1.5 lakh. Let us assume that both are under the 30% tax slab.

In Mr.A’s case, just to save the tax of Rs.45,000, he is DONATING Rs.1,05,000 to the banker in the form of interest.

However, in Mr. B’s case, he is saving Rs.45,000 and also creating a wealth of Rs.1,50,000 by investing in PPF.

You can play with all these options using the below shared Prepay Home Loan Calculator.

A loan in any form is always not good. Living a debt-free life is far more important than living in a debt especially just to save the tax.

I am not saying that one must not opt for a home loan. However, my point is that don’t retain a home loan for the sake of tax saving.

There may be another thought process that is mainly propagated by financial experts to invest somewhere else (especially in equity) rather than repaying it eagerly. However, do remember that all these are sellers in one way or another way. They show some fancy returns of the past (cherry-picking the time horizon) and lure you to assume that if it happened in the past, then it MUST happen in the future also. It MAY or MAY NOT.

In the end, wealth creation is nothing but having a calm and decent sleep rather than worrying about our debt.

However, the choice is YOUR’S!!

### 34 thoughts on “Prepay Home Loan Calculator – Download Free Excel Sheet”

1. JAGDISH PATEL

Very nice calculator, Thanks very much…. Inspire me for prepayment of loan and early closing of loan
Thanks again and again………

2. Really good template, lots of insight can get from this template. I can plan for repayment very effectively. Many new things I got to know from this template. God bless you!!!

3. Thanks for your sheet. It really gives great insights. Just having two queries,

* Can i provide the Monthly EMI value instead of auto calculation? i tried but still its using the auto calculated value only.
* Additional annual prepayments made only once but its automatically copying to schedules like 6 months. Not sure why.

1. Dear Naveen,
1) Can you elaborate more?
2) You can enter manually also if the frequency is irregular.

4. Balasubramanian S

Thanks for this nice sheet. It will be nice if you can create a provision to add Loan Start date. The sheet is missing the value of interest from Loan start date to First payment date.

5. Thank you so much I know this will help me a long way. Very well explained and the calculator is huge help.

6. Hi sir. Can you provide with same excel sheet for bank employees. They get simple interest for whole tenure. Thanks.

7. Thank you Basu! You made it easy to understand. I just want to understand a bit better. I have a home loan of 2400000 and I am paying 23900 per month and I started this loan say about 6 months back and it is for a period of 16 years. Can you tell me what will happen if I pay 200000 every year and how much tenure will reduce if I pay 200000 this month. Looking forward for your response sir.

8. Hi Basu,

I have taken home loan of 80 lacs in May 2016. I am paying EMI every month without fail. I have never paid extra EMI or additional prepayment till now. But now I want to pay extra amount let say 20,000 every month. By when my loan will get cleared then, please help.

1. Dear RJ,
It is hard for me to say without knowing the loan details. However, as the loan is almost around 7 years old, I don’t think it is profitable for you to prepay the loan every month. But still if you wish to reduce the burden of the loan, then you can go ahead. Regarding when it will get cleared, better ask your banker as they will be in a better position with your data than me.

9. Great Calculator and information, Came back to your blog after a long time.

Is it possible to update the calculator based on the following: It will be useful to many
1. Term length in months
2. Calculate based on the disbursement.

10. Dear Sir,

Thank you for the calculator. I have tried it with different parameters and found the following. Please correct me if I am wrong:
In the first example, ( Tenure : 25 years, Rate – 8.5%, Principle – 50,00,000, EMI will be Rs.40,261).
a) if we pay an additional Rs.5,000 (around 10% of EMI) throughout the whole tenure, the loan will be over by around 18 years with savings of around Rs.22,96,071.
But……
if we change the original Tenure to 18 years instead of 25 the EMI will be ? 45,272.87. Almost comparable to EMI + additional Payment (40,261+5000 = ? 45,261.35 ) and total interest payment will in both cases will not be significant.

1. Dear Ashutosh,
Yes, both are same. Mainly because you are increasing the EMI equal to what additional amount you wish to pay.

11. Vishnuchander

Good brief and idea about the prepayment procedure. Thank you very much for the explanation.
With regard to Excel sheet, Is there a Excel sheet where floating interest rate can also be accounted along with prepayment? It would be a perfect sheet to visualize all cases.

1. Dear Vishnu,
Whenever there is a change in rate, you just have to change the interest rate from that paricular month.

12. Good Calculator. But I think loan interest calculated based on Simple Interest not on Compound interest. And Indian Banks calculate Interest rate based on Compound Interest calculation.

Scroll to Top