March 5, 2014

Calculating EMI in Excel Using Math Equation

How to Calculate Equal Monthly Payments Using a Math Equation

We have learnt how to calculate the equal monthly payments or instalments using the PMT function. In fact, using the same function we have also prepared a complete loan amortization schedule. Many people, specially the accountants and math oriented finance personnel, wish to understand the background of the calculation. So we demonstrate in the video below how tocalculate the equal monthly payments for a loan based on a mathematical equation.
Normally we use the Excel financial PMT function by inputting the parameters as required. Some people may find this difficult to understand. Now you can derive a mathematical equation using the loan amount, interest rate and the number of payments and the mathematical formula can be written as :
EMI = (LxI) x (1+I)^N / [(1+I)^N] – 1
Here L is the loan amount, I is the interest rate, N is the number of payments or periods.

Entering this equation in an Excel cell has to be done carefully using appropriate brackets otherwise we end up with the wrong calculation.. How the entry of the math equation is done is shown in Excel training video below:

Further reading:
How To Calculate EMI