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.

## No comments:

## Post a comment