EMI Calculation Explained – Excel

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.

You can download it here: EMI Explained

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.

7 thoughts on “EMI Calculation Explained – Excel

  1. 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

  2. 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%

    1. 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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">