Search
Close this search box.

How to calculate Loan EMI?

emi calculator loan emi in excel

Share

There are so many EMI calculators available today that we hardly care to understand the basic maths behind these calculations.

I have met quite a few people who think of EMI calculation as a black box. That is clearly not the case.

The calculations are quite simple.

And if understand these calculations well, it will much easier to assess the impact of various inputs (principal, interest rate and loan tenure) on your monthly EMI.

Have you ever gone into the maths behind you EMI?

If you haven’t, let’s try to understand the EMI calculation.

How EMI works?

Just to be clear, I will talk about Reducing Balance loans (and not flat interest rate loans). Under reducing balance loans, the outstanding principal goes down with each EMI.

To put it in another way, a portion of the EMI goes towards interest payment while the remaining portion is used to reduce the outstanding principal.

Interest Payment for the month = Outstanding principal at the beginning of the month * Interest rate/12

Principal payment for the month = EMI – Interest Payment for the month

Principal Outstanding at the end of the month = Principal outstanding at the beginning of the month – Principal Repayment for the month

Let’s see how these calculations work.

Suppose you have taken a home loan of Rs 50 lacs at 9% p.a. for a loan tenure of 20 years.

EMI for such a loan will be Rs. 44,986 per month.

Let’s see these calculations in action.


First month

Principal outstanding at the beginning of the month = Rs 50 lacs

Interest Payment for the month = Outstanding principal at the beginning of the month * Interest rate/12 = 50 lacs * 9%/12 = Rs 37,500

Principal payment for the month = EMI – Interest Payment for the month

= 44,986 – 37,500 = Rs 7,486

Principal Outstanding at the end of the month = Principal outstanding at the beginning of the month – Principal Repayment for the month

= Rs 50 lacs – Rs 7,486 = Rs 49,92,514


Second Month

Principal outstanding at the beginning of the month = Rs 49,92,514

Interest Payment for the month =  Rs 49,92,514 * 9%/12 = Rs 37,444

Principal payment for the month = EMI – Interest Payment for the month

= 44,986 – 37,444 = Rs 7,486

Principal Outstanding at the end of the month = Principal outstanding at the beginning of the month – Principal Repayment for the month

= Rs 50 lacs – Rs 7,486 = Rs 49,84,971

Now, let’s look at how the repayment will look like in the first year.

loan emi home loan personal loan calculator

There are a few things that you must notice.

  1. Interest payment for the month is going down every month. On the other, principal repayment is going up every month.
  2. The reason is, when principal repayment is made every month, the outstanding principal goes down. Since the interest for the next month is calculated on the outstanding principal, interest component goes down.
  3. Moreover, since the interest component of the EMI goes down, principal repayment component automatically goes up.

An additional point to note is that, despite a year of EMI payment, the outstanding principal has hardly changed. It has gone down from Rs 50 lacs to Rs 49.06 lacs. A change of only Rs 93,636.

To appreciate this point better, let’s look at how the principal outstanding will change every year over the loan tenure.

loan emi excel sheet calculator

If you break down the principal repayment into 4 blocks of 5 years each, here is how principal prepayment would look like.

First 5 years: 11.29%

Next 5 years (6th to 10th year): 17.68%

11th to 15th year: 27.68%

Last five years (16th to 20th year): 43.34%

Let’s look at another piece of data.

loan emi calculator download

You can see the bulk of the EMI payments in the initial years is going towards interest payment. The reason is same as discussed i.e. higher principal outstanding. During the latter part of the loan tenure, since the outstanding loan amount has gone down substantially, a greater portion goes towards principal repayment.

If you are wondering why your outstanding loan amount has not gone down much even though you have been paying EMIs for many years, you have your answer.

How to calculate loan EMI?

You can use Microsoft Excel to calculate loan EMI for any reducing balance loan.

You need to use PMT function.

PMT(Monthly Interest Rate, Loan Tenure in Months, Loan Amount,0)

In the example discussed,

Monthly Interest Rate = 9%/12 = 0.75%

Loan Tenure in Months = 20 years * 12 = 240 months

Loan Amount = 50 lacs

You can also use Goal Seek functionality in excel and calculate EMI without using these method. The key is to make the principal outstanding in the last month to be zero. However, that is unduly complicated.

If you want to know the mathematical formula, you can use the following

emi mathematical formula

where,

P is the Principal Outstanding

r is the monthly interest rate

n is the loan tenure in months

Alternatively, there are many calculators available in the market that you can use.

The best and the simplest one is EMICalculator.

In addition, respective banks websites also have loan EMI calculators on their websites. For instance, you can use the calculators available on SBI website or ICICI Bank website.

How do the interest rate cuts affect your repayment schedule?

When the loan interest rate changes, the bank has two options:

  1. It can change the EMI and keep the loan tenure same.
  2. It can keep the EMI constant and change the loan tenure.

Typically, banks exercise the second option (keeping EMI constant).

Let’s assume if the interest rate goes up. In such a case, as we have seen earlier, a greater portion of the EMI will have to go towards interest payment and less will be left for principal repayment.

Since the principal repayment has slowed down, it takes longer to repay the loan.

That’s why an increase in interest rates will increase the loan tenure (if the EMI is kept constant).

On the other hand, a decrease in interest rate will lead to reduction in loan tenure.

If you want to calculate the new tenure for your loan, you can use the following excel formula

NPER (Monthly Interest Rate, EMI Amount, -1* Current Loan Outstanding, 0,0)

For instance, after 60 months of loan repayment under our example, if the interest rate would have risen from 9% p.a. to 10% p.a., the remaining loan tenure would have to be increased from 180 months to 207 months.

On the other hand, if the interest rate had been cut to 8% p.a. , the loan tenure would have gone down from 180 months to 160 months. Of course, this is assuming that the EMI stays constant.

What effect will prepayment have?

What do you think?

If you part-prepay the loan, the loan outstanding will go down. What will this lead do?

  1. Since the loan outstanding has gone down, your interest component of the EMI will go down.
  2. With that, the principal component of EMI will go up. And thus, loan repayment will get expedited.

The loan will get repaid much faster.

For instance, if you repay Rs 5 lacs after 6 years of loan repayment (assuming interest rates didn’t change in the interim), the loan will get repaid in another 150 months (as per the original schedule, it would have taken 168 months). Therefore, you save about 18 EMIs.

If you understand these basic concepts behind EMI calculations, it will be much easier to grasp the impact of interest rates, prepayment etc on your loan repayment schedule.

Please understand the outcome may vary slightly if you use a different set of inputs. However, the logic stays the same. You will see similar pattern with any set of inputs.

If you found the post, do share with your friends and family too.

9 thoughts on “How to calculate Loan EMI?”

  1. Excellent Article and thanks to Mr. Deep. For EMI Calculation, we have to use the concept of ‘PVIFA’ (Present Value Interest Factor of Annuity) as this is the Loan Amount which we are taking first (Rs. 50 Lakhs in the above example). We know the Interest Rate as 9% and Loan Term of 20 years which means we can estimate the EMI (A) using the formula:

    PVIFA = A [(1 + r)^n – 1]/[r(1 +r)^n]

    or ‘A’ (EMI) = PVIFA X [r(1 +r)^n]/[(1 + r)^n – 1]

    The above formulae is based on ‘Annuity’ Principle as loan is paid every month/every year. I hope deep will simplify the above equation and explain in laymen terms as ‘EMI’ is the first component in determining the whole loan process as he always explains that even a common man can understand the complicated formulae’s!!

    1. Hi Sarvana,
      I surely need some maths lessons from you. 🙂
      Thanks for breaking down the equation for me and the readers.
      Sincerely appreciate your inputs.
      Just that I am not sure the readers will appreciate or want to read such complex mathematics.
      The intent was to make them understand how EMI based loans work and how they should think about the impact of various inputs. Not so much to explain the formula.
      The formula was merely mentioned for the sake of completeness.
      I will surely consider your suggestion. Thanks!!!

      1. Thanks for your Appreciation and hope your explanation will satisfy even a common man. Unfortunately, even highly educated folks don’t know the difference between EQUITY which is Principle and INTEREST component in the EMI.

        When Interest rates are reduced, the greedy bank don’t automatically reduce the EMI. We as a consumer should first calculate the EMI’s with the new rate, take our computation to our bank manager and we have to pressurise the bank manager to reduce the EMI amount with the same term or retain the same EMI amount with a reduced term.

        I am surprised even professionals do not pay much attention to loan process and they keep on paying the same EMI inspite of reduction in interest rates where banks are milking heavy profit from consumers.

        Dear Deep – I sincerely suggest you to write a book on ‘Personal Finance Plan’ for middle-class customers as they are one who are falling prey to greedy banks. If we come out with simple topics like Term Insurance avoiding ULIP Plans, Simple Health Insurance, PPF Investments, Sukisha Investments for Kids to take care of education and marriage, a few highly rated INDEX funds (Just 20% of portfolio for middle class) for retirement. Home Loan Mortgage process etc which ensures they start with a simple plan and try to stick to the plan thick and thin so that they learn the process of financial planning. Many Many Indians do not know enough about Financial Goals, Savings, Investments, Time Horizon, Risk Management, Taxation etc even though they have gone to college. Why can’t you write a simple book that explains the above in 1oo pages and sell it directly for Rs. 99:-) that will help everyone to have a secured financial life. Please remember the target audience is Middle class with monthly income ranging from Rs. 35000 to Rs. 55000 when both partners earn.

        Thanks
        Saravana

        1. Completely agree, Saravana.
          And thanks for the encouragement to write a book.
          I have thought about it but hold back for one reason or the other.

  2. Hi Deepesh
    Thank you for explaining. Just wanted to check if this is calculated on daily basis.
    In the same e.g if someone puts 5 lakhs just for 10 days and take it back and then again in next month he puts 5 lakhs for 10 days and take it back. Is it going to make any effect on lowering the interest part of the EMI? Is it useful in anyway?

    1. Deepesh Raghaw

      Hi Anug,
      Term Loans don’t work in this manner.
      You are talking about an overdraft facility. An overdraft facility works in this manner. And the interest is calculated only on the utilized amount.

  3. Its a very good explanation. Would you also be able to throw some light on how the Tax Saver home loans work. Those with OD facility.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.