How to Calculate Equal Monthly Payments

Using a Math Equation

Excel 2007
Excel 2003
Macros Excel 2007
Macros Excel 2003
Excel 2010
About Us
Free Ebook
Offline Training

Excel Books

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:



Reference






Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Excel Books