Easy way to calculate EMI payments

Here are a few points to explain how EMI works:

1.       Equated Monthly Instalment: An EMI is a fixed sum of money that you pay the bank each month in order to gradually pay the entire loan off. The entire amount of the loan is called the principal. The bank charges interest on the principal for lending money. The principal–the entire amount of money that one borrows from the bank–is used to calculate the EMI payments.

2.       What EMI consists of: Every EMI payment includes a portion of the principal as well as the interest amount. So, as you pay each instalment of the EMI, the principal as well as the interest you owe reduces over time.

3.       Monthly, not annual interest: Every EMI is calculated on the basis of the monthly interest rate. The interest on your loan is calculated as a percentage of your principal. This percentage is called the interest rate. The bank usually calculates the interest on a per-year basis. For example, an 8% yearly interest rate translates to an interest fee of 0.66% per month. It is easy to calculate. Simply divide the annual interest rate (8%, in this case) with the total number of months in a year (12). You will get the monthly rate.

4.       Loan tenure: The EMI is paid on a monthly basis. So, for the sake of calculation, your loan tenure is considered on a monthly basis. So, it is essentially the number of months during which you have to pay off the loan with interest. For an example, if the loan is taken for a period of seven years, then the number of months would be 84.

5.       Calculating EMIs: Now that we have all the essentials ready, it is time to calculate the EMI. There are two ways – one involves an excel sheet, the other involves a complicated calculation. The Excel way is easier to understand.

In Excel, you can simply use the tool called PMT. Simply open a new sheet, go to the ‘Formulas’ section in the Menu, and select ‘PMT’ among the financial formulae. You will be asked to punch in some data. Input the monthly rate, ‘Nper’ or the number of months you will be paying the loan, and enter the principal amount in the ‘Pv’ section. Punch in ‘0’ in the ‘Fv’ section and select the ‘Payment at the end of the period’ option. That’s it. Excel will display your monthly EMI due.

6.       Some points to remember: The interest rate is a percentage. So, when you input the data, always divide it by 100. So, for example, if the interest rate is 8%, then in Excel, you will write 0.08, not 8. Secondly, while paying for a loan, you always pay by the end of the month. So, we select the ‘Payment at the end of the period’ option. This is important; otherwise, your EMI calculation will go wrong.

7.       Example:​ If you take a loan of Rs 10 lakh from a bank to buy a car. The bank charges 8% interest per year for seven years. The principal would be Rs 10,00,000. The rate of interest per month will be 0.66% and the number of months will be 84. So, the EMI for the loan will be Rs 15,546.39 per month.

8.       Understanding how EMI works: Each EMI consists of a portion of the principal and the interest. In the first month, the interest paid will be the result of the monthly interest rate of 0.66% and the total principal. This will be Rs 6,600. This means, Rs 8.946.39 of the EMI of Rs 15,546.39 is the portion of the principal. So, you still have to pay Rs 9,91,053.61 of the principal. Now, for the next month, the interest will now be calculated as a percentage of this amount. Thus, the interest payment for the second month will be Rs 6,540.95. The remaining amount of the EMI – Rs 9,005.44 – is the principal repayment. This will further reduce your total principal left to be repaid. In this manner, the interest component of the EMI will keep decreasing each time, while your principal owed to the bank will reduce. The EMI, though, will remain the same each month.