## How to calculate EMI in Excel ?

Finally real estate rates have come down and now according to experts this is an ideal time to buy a home/property. I know most of you must have started hunting for a property. Here is one small help from Netbhet for all house hunters.

Today I am going explain you a simple function in Microsoft excel which helps you to calculate EMI. It’s really a very handy tool.

The function in Excel for calculating EMI is called PMT.

How to calculate EMI in excel ?

1. Select any cell on spreadsheet and write =pmt(

2. A full formula for PMT function will appear on screen with all the attributes that are required to calculate EMI.

=pmt(rate,nper,pv,[fv],[type])

3. Detailed description of all attributes is as follows -

rate = Rate of interest per month
Nper = Number of periods (total tenure of loan. In our case it is number of months)
Pv = Total loan amount (Principal value)
[fv] and [type] are optional values ( square brackets denotes optional values)

4. Insert all the values and press enter.

5. Your EMI amount will appear in the selected cell.

(Note – The dollor sign (\$) or any other currency sign depending on computer’s regional settings may appear in front of the EMI amount calculated by excel. You need to ignore that sign. If the PV entered is in Rupees then the EMI calculated will also be in Rupees)

Example -

If you want to find EMI for 2 lakh at 12% annual interest for 10 years you enter following :

= pmt(12%/12, 10*12, 200000)

- Here an annual rate of interest is converted into monthly rate.
- 10 years of tenure is coverted into total number of months.

Unfortunately, it is not always possible for you to access computer or Excel.

So here is a formulae for calculating EMI. Save this formula on your mobile phone and you can do the calculation using “mobile calculator” anytime and anywhere.

Formula for calculation of EMI

The formula for calculation of EMI given the loan, term and interest rate is:

EMI = [(p*r) (1+r)^n ] / [ (1+r)^n – 1 ]

p = principal (amount of loan),
r = rate of interest per month (annual rate/12)
n = no. of instalments in the tenure