A friend of mine from Jaipur bought a car in 2010 that was priced Rs 5.95 lakh. He made a down payment of Rs 1.5 lakh and decided on auto loan for the rest of the amount at 12% interest per annum for 4 years. He is currently paying an equated monthly installment or EMI of Rs 11,700 every month. But here is the catch. He has no idea if the amount is correct or not.

Just like my friend there are many people who do not have much knowledge on whether their lender is charging them the right EMI or not. If you too have been paying your EMI with any knowledge, we are here to help you out today. Here is a tutorial to help you calculate your EMI that you can cross-check what you have been paying.

There are two ways in which you can calculate your EMI, one is by using Microsoft Excel and the other is with a mathematical formula.

Using Excel

Excel spreadsheet is one of the easiest ways to calculate EMI. However, the function for calculating EMI in excel is termed as PMT. You need to deal with three variables viz,.

• Rate- rate of interest
• nper- number of periods
• pv- value of the loan or present value

The formula which you can use in excel is:

The formula goes this way:  =PMT (rate,nper,pv)

Let me show you how to work on it with the example of my friend.

The rate used in the formula is the monthly rate, and in this case:

12%/12 = 1% or 0.01

The number of periods represents the number of EMIs.

= PMT (0.12/12, 4*12, 445,000) = 11,718

The result will come in negative or red. It indicates the cash outflow of the borrower.

Here is another example. You pay a quarterly installment on a loan of Rs 10 lakh at 10% interest per annum for 20 years. For this case you will have to divide the rate by four and multiply the number of years by four.

The equated quarterly installment will hence be =PMT (10%/4, 20*4, 10,00,000).

Using Mathematical Formula

If for some reasons you are not able to gain access to your Excel spreadsheet, you can simply use a piece of paper or an electronic calculator to calculate your EMI. The mathematical formula you need is:

EMI = [P x R x (1+R)N]/[(1+R)N-1]

where,

P = the loan amount or principal,

R = interest rate per month [if the interest rate per annum is 11%, then the rate of interest will be 11/(12 x 100)], and

N = number of monthly installments.

You will get the same result as your Excel spreadsheet.

Calculate the EMI on your Loan in a Few Simple Steps