When i took Home Loan, one of the problems that i faced was to find a right EMI Calculator. The EMI Calculator available on the web are not compatible with Home Loans. There is no option to change Interest rate, EMI or add pre-payment amount during home loan tenure. With the introduction of Marginal Cost of Funds based Lending Rate, the interest rates will become more dynamic. In short, interest rates will change more frequently. As a borrower, i find it difficult to check the financial impact of any change in Home Loan.
One of the most common queries that i receive is on the impact of the change in interest rate after payment of conversion fees. For example, recently one of the readers was in a dilemma whether to pay conversion fees and reduce the interest rate from 9.75% to 9.55%. This option may not be useful always. Every decision should be backed by the financial calculations. EMI itself is a complex subject and has a major impact on your financials. A common perception is that lower EMI and higher home loan tenure is beneficial whereas in reality it is suicidal in nature. It increases the interest outflow drastically. You can check some of the do’s and don’t related to EMI in my post, 5 Golden Rules of Home Loan EMI. At the macro level, you can always ask for amortization schedule from the bank but it is an operational nightmare.
The following EMI Calculator or Amortization Calculator can be used for all types of loan. I tried and tested with Personal Loan, Auto Loan, Consumer Loan, Business Loan etc. The details required and calculation is same for any type of loan.
Please click on the following link to download EMI Calculator
In case, you don’t want complex calculator and would like to just calculate the relation between EMI and Interest rate. In this case, you can use XIRR function of excel. This is very basic level calculation and does not take into account the micro details.
How to use EMI Calculator?
For the benefit of readers, i am explaining how to use this EMI Calculator. Though it is very simple but you can do a lot of calculations. It can also help in decision making. Towards the end, i will explain whether it is beneficial to pay conversion fees and reduce the interest rate or not.
1. Download the EMI calculator from the link shared in this post.
2. You may enter the month, loan amount, interest rate and installment amount in cell no B12, C12, D12, and F12 respectively.
3. Drag cell no B12, D12 and F12 till amount in column I i.e. “Balance Loan” becomes zero or negative. For example, i entered the loan amount, interest rate and installment amount as 75L, 9.55%, and 1L respectively. In this case, “Balance Loan” becomes negative in 115th Month. Therefore, home loan tenure is 115 months.
4. Assuming there is a change in home loan interest rate from 20th month to 9% from 9.55%. Therefore, home loan tenure will reduce to 112 months.
5. Further, you increased EMI to 1.25L from 40th Month. The tenure will further reduce to 94 Months.
6. In 50th Month, you made a prepayment of 5L. Enter it in cell no G61. The home loan tenure will reduce to 89 months from 94 months.
Therefore, we observed that this EMI calculator is quite flexible. You can yourself check the impact of the change in interest rate, EMI amount, and prepayment. As i mentioned, it will work with all types of loan.
How to check whether it is beneficial to pay Conversion Fees and reduce Interest Rate?
This is one of the most common queries. When the interest rates are on a downward slope then old/existing borrowers are locked at higher interest rate. A bank may demand conversion fees to increase the spread on BPLR / RPLR or reduce markup on base rate. It decreases the interest rate for a borrower. Before you accept any such offer, it is important to know interest rate offered to new borrowers. You should not agree to anything less than interest rate offered to new borrowers. Secondly, you need to check the interest rate offered by the market leaders. I share the same in my post, Cheapest Home Loan Rates. There is a possibility that after payment of conversion fees, your bank is offering the interest rate same as offered to the new borrowers. At the same time, it might be higher than the interest rates offered by market leaders.
For example, one of the leading housing finance company provides home loan at fixed interest rate for first two years. Currently, after two years the interest rate increases to 11.5% – 12% after a shift from fixed to floating. Now they offer to reduce the interest rate to 10.20% on payment of conversion fees. In my opinion, it is not beneficial. The reason being cheapest interest rate offered is 9.55%. In such scenarios, it is beneficial to balance transfer or shift home loan to bank/HFC offering lowest interest rate.
Coming back to the main topic :). You can use EMI calculator to check whether conversion offer is beneficial or not. Continuing with the same example. Assuming you accepted the offer in 55th month to reduce the interest rate to 8.5% from 9%. You paid Rs 10,000 for same. You should first check the interest amount without offer i.e. column E from 55th month to 89th month. It is 5,22,061. After that, you change the interest rate to 8.5% from 55th to 89th Month. In this case, home loan tenure will the remain same after a change in interest rate. The new interest payout is 4,87,979. Therefore, you will save Rs 34,082 in interest but you have to pay Rs 10,000 to avail the offer. Therefore, the net benefit is Rs 24,082. In other words, conversion fees offer is beneficial for the borrower. Again i am cautioning that it may not be financially beneficial always. You should accept to reject only after calculation.
Words of Wisdom: Through this post, we have learned that how we can check and calculate so many permutations and combinations. We need not rely on the bank for all these calculations. EMI calculator is very useful decision-making tool for all types of loan. You should not forget to add any penalty or fees in cost. It should be reduced from the benefit. Last but not the least, in the case of prepayment, you should also include home loan tax deduction to check the net benefit. Normally, we only check top level calculation rather taking 360-degree approach.
Copyright © Nitin Bhatia. All Rights Reserved.
Share this Post: