How to calculate EMI in Excel

Continuing with my last post on finding out how much you will make using compound interest, trying to put down the simple method you can use to find out how much Equated Monthly Installment (aka EMI) you need to make for the loan you bought.

Erstwhile, it was predominately for Home loans but now with culture shift and new generation opting for easy loan features for buying the consumption goods (read as high end mobiles, cars etc) - its even more important to know the calculation and find out the real amount you might be paying up.

So let's quickly get into the Excel formula called PMT

=PMT(RATE,NPER,PV,FV,TYPE)

Here,
Rate stands for rate of interest applicable on the loan
NPER stands for total number of monthly installments/ loan tenure
PV stands for present value/ loan amount/ principal amount
FV stands for future value or cash balance once last payment has been made. This can be omitted and the value will be counted as zero (0).

Type is zero (numerical 0) or 1 – this indicates when the payment is due. If payment is due at the end of the period, the type will be equal to zero. If the payment is due at the commencement of the month, then the type will be set as 1.

Important to remember is, keep both Rate and NPER in same terms - which basically mean, if NPER is in month, ensure Rate is also monthly.

Let's take a quick example: You bought a Car worth Rs 7,00,000 by paying making a down payment of Rs 1,00,000 and took an auto loan for the rest of the amount at 12% interest per annum for four years. What would be your EMI amount which you need to pay each month?

Here: Rate is 12% per annum, NPER = 4 years, PV = 6,00,000 (this is loan amount)
I am converting both Rate and NPER to months in example below, as amount needs to be paid each month

Formula in Excel would read as below and answer should be Rs 15,800 which you need to pay each month for 4 years towards the car loan
=PMT(12%/12,4*12,600000,0,0)

Output would be negative, as that is the monthly outflow you are paying.

Once you get hang of it, it's pretty easy to calculate and find out the liability/debt you are entering in. Hope this helps you in making better financial decisions.