Here is a short article with few examples to help you to know and calculate EMI (equated monthly installment). This formula is specifically helpful for those re-paying their loans.
An EMI is the amount a person paying to the bank in every month against a loan taken.
I have given 2 scenarios and practices to help you to calculate the applicable EMI amount using Microsoft Excel. Before starting, confirm, you have all required information to calculating EMI. The total loan amount, Percentage of interest and loan duration (in months).
What is EMI and how to calculate Equated Monthly Installment
First, we will identify the possible EMI and cost associated with a short term loan.
Scenario 1 - Suppose, I am taking a short term loan of Rs.100,000 with an interest rate of 8% (diminishing rate) and the repayment duration for this loan is 36 month.
Here is the step to calculate the EMI:
1. Open MS Excel >>> Select ‘Insert’ menu >>>Select ‘Function” sub menu
2. In the opening box, choose ‘PMT’ from the list of ‘Select a function’ box and click ‘OK’
3. In the next box, provide the details: Rate = 0.08/12, Nper = 36, Pv = 100000, fv = 0, Type = 0
('Rate' is the 8% interest rate, 'Nper' is the loan duration in months, 'Pv' is the loan amount . There is no fair value so we are giving ‘zero’ for ‘fv’. We are again giving ‘zero’ for ‘type’ which indicating that the EMI will pay in each month without failure.)
4. You can now click OK button to get the EMI amount of Rs. 3,133.64, which you required to pay in each month to bank for next 36 months against your Rs.1 lakh loan with 8% interest rate.
Now, multiply the received Rs.3,133.64 with 36 months. (3133.64 x 36 months), you will get a total of Rs.112,811.04. This is the amount you finally paying back to your bank at the end of 36 months against your Rs.1 lakh loan. It shows that, you are paying an excess amount of Rs.12,811.04 as the cost of this loan.
In our second scenario, we will calculate the EMI for a long term loan and identify the excess amount one required to pay.
*************************************************
Scenario 2 – Suppose, I am taking a home loan of Rs. 25 lakhs with an interest of 12.5% in diminishing rate and the repayment duration is 400 months.
1. Open MS Excel > Select ‘Insert’ menu > Select “Function” sub menu
2. In the opening box, choose ‘PMT’ from the list of ‘Select a function’ box and click ‘OK’
3. In the next box, provide the details: Rate = 0.125/12, Nper = 400, Pv = 2500000, Fv = 0, Type = 0
('Rate' is the 12% interest rate, 'Nper' is the loan duration in months, 'Pv' is the loan amount . There is no fair value so we are giving ‘zero’ for ‘fv’. We are again giving ‘zero’ for ‘type’ which indicating that the EMI will pay in each month without failure.)
4. You can now click OK button to get the EMI amount of Rs.26,460.85/-, which you required to pay in each month to the bank for next 400 months against your Rs.25 lakh loan with 12.5% interest rate.
This is not an end. I am really coming to the point of revealing the treacherous secret each and every bank using to cheat poor loan holders and get huge profits. Now you have and EMI of Rs.26,460.85 in your hand. Multiply this EMI amount with total month of 400 (EMI Rs.26460.85 x 400 months). The result will be Rs.10,584,339.72/-. This mean, you are paying an amount of Rs.105 lakhs against the Rs.25 lakhs loan !!!! Man, it is five times to the original loan amount.
Is there any way to escape from this cheating? Yes there is. But, it required little homework. For an example, we are making a small change in the previous calculation. We are now shortening the Duration of repayment from 400 months to 240 months. Rest all are same (12.5% interest and Rs.25 lakhs loan amount) . Again, calculate using the above steps but this time, give 240 instead of 400 months with ‘Nper’ column.
Here is the magic. We are now getting the EMI of Rs.28,403.51/-. Now multiply the Rs.28,403.51 with total month of 240 (Rs.28,403.51 x 240 months) to get a total of Rs.6,816,843.30. It mean, shortening the duration from 400 months to 240 months with little increase in EMI amount, you are now saving a clear total of Rs.3767496.72/-
A truth to learn from this is, when increasing the EMI and decreasing the Duration, interest rate also coming down to a great extend. Never agree the longer duration any bank offering to you. If you have capacity of repaying an amount little more than what bank said to you as your EMI, you will be a clear winner with minimum lose of your money.
If you planning to apply for a loan, there are some important points to remember in the above context:
1. In case of interest rate increase, identify if you have any investments that getting less interest than your loan interest, stop that investment and repay the loan to reduce the duration and principal.
2. Always identify to select a bank, who does not impose any penalty for repaying loan with an amount more than the original EMI. This will help you to repay the loan when enough cash in your hand or in a single shot.
3. Always ask and get the payment schedule from the bank. This will help you to understand the amount flow to your loan and interest components time to time.
Best wishes to all those for a loan to study well and decrease the possibility of lose.
