How EMI is calculated!!

I decided to purchase a house in Banaglore (an extremely tough task) and the first thing that struck me is equated monthly installment or EMI. This is the single most important parameter while taking any kind of loan. This is the amount outgo every month from your personal finances which will cover both the principle as well as interest. I talked to few people and everyone is bit confused on how EMI is calculated. It is really simple and just few steps would enable you to calculate EMI at your end.

So here is a rather simply formula for calculating EMI.

You would wonder why EMI is called "equated", the reason is that EMI is nothing but loan amount plus total interest divided by loan tenure. If that is the case then why this complicated formula. The reason is because as you keep paying EMI, some portion of EMI goes as interest but some portion goes as principal repayment. So if you pay an EMI of Rs10,000 for a house loan, not the entire Rs 10,000 would go as interest payment, but some portion goes as principal repayment, which essentially reduces the principal on which further interest is calculated. It is extremely important to understand what goes for interest and what goes for principal repayment. It is very clear (for mathematically inclined) that when Loan Amount goes up, so does the EMI. Similarly if the interest goes up again so does the EMI, but if 'n' (loan tenure) goes up, EMI reduces. A note of caution, a low EMI for longer period does not necessary means a good bargain. A good bargain depends on your requirements as well as the total interest you pay over the entire loan tenure. Another thing to keep in mind is whether the reduction in loan amount happens on monthly basis or yearly basis. Any loan which reduces the principal on monthly basis should be given preference. A monthly reduction implies less interest payment from next month onwards, definitely a huge savings. Also usually interest rates comes in flavors of fixed and floating rates. A floating rate changes based on market's prime lending rate (PLR). A fixed rate stays fixed for the tenure of the loan. For a longer period of loan, my personal preference is always fixed interest rate, even if it is 1-2% higher, at least the monthly outgo is fixed, so planning of your outflows can be planned pretty well. I personally think that similar to rupee averaging for mutual funds, the floating rate almost remains same as fixed rate over a long tenure of loan. [The floating rate will go up and down and hence your monthly outgo]. And for short tenure loan, in a high interest regime, go for floating rate, but in a low interest regime choose fixed rate.

EMI calculation using Excel

I mentioned the formula used to calculate an EMI, but putting values in the formula and calculating it is a cumbersome task. So I did some research in Microsoft Excel and found out that it can done quite easily in excel. EMI calculation in Excel Step 1: Open the excel sheet and locate the fx button Step 2: In the pop-up menu, click on Financial Catergory

Step 3: In the Function Name click 'PMT'

Step 4: A box will appear as shown, fill in the values mentioned and voila you get the EMI.

Interest Component of the EMI Just choose the IPMT function instead of PMT Prinicpal Component of EMI Just choose the PPMT function instead of PMT

EMI Calculation
Let say you take a loan of Rs.100, 000/- at 10% rate of interest. It simply means that every year you will pay 10% interest on the principal amount. You will also pay a fixed monthly amount towards the principal. How much each of these two will be? Lets try to calculate. Loan = 100000/Tenure = 5 years Rate = 9%

Interest you need to pay for full year = 100000*9% = 9000/Monthly interest payment = yearly payment / 12 = 9000 / 12 = 750/So, every month you contribute 750/- towards the interest. For principal, you need to pay a fixed amount every month such that it grows to 100000/-(loan amount) after 5 yrs(loan tenure) while earning 9%(our loan rate) interest on it. You can calculate it using MS Excel (PMT() function). This comes out to be 1326/-. So, you need to pay 1326/- per month so that you can pay 100000/- of principal amount in 5 years. Now, add the two components (payment towards interest + payment towards principal) and you get your EMI as 1326+750=2076/-. The concept of EMI explained above was simple. Every month you pay a fixed amount towards both interest and principal. But since your interest component is only based on the principal remaining, the breakup of your EMI changes every month depending on the principal left to pay till date. In this case, for the first month, the interest component of the EMI will be 750 (=100000*9%/12) and the remaining part of EMI(i.e. 1326/-) will be the principal component. Next month your interest will be charged on reduced principal(100000 1326). So, the interest payment will go down a little(you just paid 1326/- towards principal last month!) and hence the principal component of the EMI will go up. So, to find out the break up the EMI every month, you calculate the total principal paid till date. Then you calculate the interest component on the remaining principal. This will be the interest part of your EMI. The remaining part of the EMI goes towards the principal payment. See the example below.

Here is a simple example that explains how the repayment of your EMI reduces your loan amount during repayment period leading up to the end of the loan tenure. Here the loan amount is 100000, which is lent at a interest rate of 12% with a loan tenure of 12 months. The monthly EMI is calculated at the annualized rate of 12% and amounts to Rs.8,885 per month with the total interest component amounting to Rs.6619. You will notice that the Interest repaid decreases with each passing month and the principal repaid increases with each passing month. This means that with a larger loan amount of say 5 L with a longer tenure of 20 years, the interest component will be the greater portion of the EMI, which will reduce leading up to the loan tenure, while the reverse is true for the principal component. Amortization Table
Month no. 1 2 3 4 5 6 7 8 9 10 11 12 Outstanding amount 100,000 92,115 84,151 76,108 67,984 59,779 51,492 43,122 34,668 26,130 17,507 8,797 Interest paid this month 1,000 921 842 761 680 598 515 431 347 261 175 88 Principal paid EMI Payment for this month this month 7,885 8,885 7,964 8,885 8,043 8,885 8,124 8,885 8,205 8,885 8,287 8,885 8,370 8,885 8,454 8,885 8,538 8,885 8,624 8,885 8,710 8,885 8,797 8,885

How is EMI calculated?

The Equated Monthly Installment (EMI) of a loan is calculated according to the following formula. EMI = n (P x i) (1+i) n (1+i) - 1 Where, P is the loan amount i is the monthly interest rate (i.e. the yearly interest rate divided by 12) n is the loan tenure in months For example, if you have a Personal Loan of 5 Lakhs (500,000) for an yearly interest rate of 13% and a tenure of 5 years, then, P = 500,000 i = (13/100)/12 = 0.010833 n = 5 x 12 = 60 EMI = 60 (500,000 x 0.010833) (1+0.010833) 60 (1+0.010833) 1 = 11376.54 Thus the EMI of the loan is Rs. 11,377

Is there any formula to calculate the number of EMIs after each part payment, EMI amount remaining unchanged?
Formula wanted to calculate the no. of EMIs as and when there is a part payment against a loan, the amount of EMI remaining fixed from the beginning to end.

Best Answer
Equated Monthly Installment (EMI) = payment per period = P* (i/pyr)*(1+(i/pyr))^n/((1+(i/pyr))^n-1) P = principal i = annual interest as decimal, (10% = 0.1) pyr = periods per year of compounding n= total number of compounding periods If you want an amortization table to include additional principal payments, download the spreadsheet below. The months you make additional pmts, add it to the appropriate cell. Say you pay 500 extra in month 4, modify cell d12 from =B12-C12 to =B12-C12+500 Balance will go to 0 or negative quicker The payment number where balance becomes <=0 minus the current payment number is the number of payments left