EMI Calculation is a rather complex math with the denominator being the integration of a series (or something of that sort). It is fairly complex to take a pen and paper and calculate. Well, that is if you do not have access to Excel or a similar spreadsheet. In a spreadsheet, it is a very straight-forward calculation to arrive at an EMI.

In this article am explaining how to calculate EMI and amortization schedule of your loan using MS Excel. Also, you can see by changing the EMI how your tenure changes. I’ve attached a sample sheet here with the calculation.

**This is the EMI formula in Excel**

`=PMT(D4/12,D6,-D3)`

*Where
D4 has the per annum interest
D6 has the tenure in months
D3 has the loan amount.*

So what if your loan is quarterly diminishing (or compounding, as they say) even though you are paying monthly. In that case the formula changes a bit.

`=PMT(D4/4,D6,-D3)/3`

*Where
D6 has the tenure in quarters (i.e. # of years multiplied by 4)
D3 and D4 remain unchanged.*

Note the interest changed from D4/12 to D4/4 and tenure from Years * 12 to Years * 4. Also, there is a division by 3 at the end to make it monthly. Similarly you can extend it to any compounding interval.

With slight changes you can use this to calculate the return of a SIP, assuming an average return over the tenure you plan to invest.

The attached sheet has more than just the EMI formula. It’ll generate the amortization schedule for you, given a tenure so that you can play with the EMI amount to reach at a preferred tenure.

**Update:** Came to know later that Microsoft has similar template for amortization schedule and loan analysis. You can get it here.

E= P x r x (1 + r)n / ((1+r)n -1)

Here p=principal amount

r = interesr rate per month(ex: if interest rate per annum is 10% then 10/(12*100))

n= tenure in months

What will be the formula to calculate interest rate if we know loan amount,Tenor and EMI.

e.g. if L.A.=100000

EMI=8885

Tenor=1 year (12 emis)

know i want to calculate interest rate that should be 12%

It would be like this:

=RATE(12,-8885,100000)*12

i.e.

RATE (nper, pmt, pv)

nper – number of installments (i.e. 12 x No. of Years)

pmt – EMI

pv – Principal

With optional parameters the full formula is like this: RATE (nper, pmt, pv, [fv], [type], [guess])

The result is the monthly rate and hence multiplying by 12 will give the annual rate.

I want to know the EXCEL function for calculating month of repayment provide EMI afforded,ROI and amount is given

Praveen

Dear sir.

i need Housing loan prepared exel sheet caluclatter………… pls

i’ve sent you a note asking for details of the loan

i want to EMI Calculation formula and explanation please reply….