How to calculate EMI using MS Excel

Posted by prateek on June 21, 2010

How many of you have planned to take a new home? or you have just taken a new one ??

I am quite sure all people of this category know the world EMI very well.Well I am going to

help you calculate your EMI using Microsoft’s Windows Excel . very easy & useful isn’t it ??

so lets see how you can..

To calculate EMI we have an inbuilt function in Excel called “PMT”

STEPS:

STEP 1:
1. In any empty cell in excel, write =pmt. As soon as you do this the formulae for the PMT
function will be displayed as follows..

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

All the components necessary to calculate EMI are displayed in the formula.There description
is as follows:

1. Rate: this is the rate of interest per month

2. Nper : no of periods(total tenure of Loan. In our case it is the no of months)

3. Pv : Total Loan Amount(principle value)

4. [fv] & [type] this payment is not
so now enter all the values in the PMT function & hit enter & you will get the monthly EMI
in that Cell.

LETS SEE AN EXAMPLE ..
=pmt(12% 12, 10*12, 200000)
=PMT (Rate, nper, [fv], [type]) This is an example showing how to put values to calculate EMI.

NOTE:

I don’t think a person have a computer with hm/her & every time, so what I would suggest
is to save the formula given below in a mobile note so that you can use it any time , anywhere!

the Formula is : EMI=[(p*r)(1+r)^n]/[(1+r)^n-1]
where p= principal (amount of loan)
r=Rate of interest per month(annual rate/12)
n=no of installments in the tenure.

hope this will prove useful to all who read….

Popularity: 1% [?]