Home » Maths Tricks » How to calculate EMI?

EMI Calculation

In our daily life we face enormous application of mathematics. Calculation of equated monthly installments (EMI) for car or home loan is one such common application of mathematics.

EMI or equated monthly installments is the most popular form of loan payment.  It is a fixed amount of repayment made every month towards the loan, which includes payment towards both principal and interest. Most of us always believe the bank executives blindly on the figure which they quote as EMI.

This post is to explain the mathematics behind EMI and how to calculate it in excel using inbuilt excel function. If you wish to learn shortcut to find the time required to double a sum of money invested at certain rate of interest, you can apply Rule of 72.

EMI Calculation Formula

EMI= P x r x (1 + r)^n / ((1+r)^n -1)

Here p = principal amount (loan taken)

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

n= tenure in months

For example,

EMI = 100000*0.01*(1+0.01)^24 /((1+0.01)^24 -1) =  4707

Where,

p = loan taken = 1,00,000

r = interest rate per month = 1% = 0.01

n= tenure in months = 2 Years = 24 months

This formula assumes, EMI payment is made at the end of each period (month). This is also called EMI in arrears. If EMI is paid at the beginning of each period it is called EMI in advance.

Further additions will be done on EMI for any other processing fee or possible charges which may be applicable as per the rules of financing institutions (bank).

Calculation of EMI in excel

In excel it is very simple to calculate EMI. There is an inbuilt formula for EMI calculation called PMT

PMT(rate,nper,pv)

Where,

Rate – Interest rate for the loan.

nper – Total number of payments for the loan.

PV – Present value/principal or loan taken.

FV – Future value (you can omit it)

Type – we have to put the value either 0 or 1. If payments are made at the beginning (EMI in advance) of each period, 1 is used. If EMI payments are made at the end of the period (EMI in arrears) put 0. If omitted 0 is taken a default value. Just open a new excel file and try it yourself.

Lot more can be discussed about EMI. Please share your knowledge, doubts or experiences with EMI calculations by posting comments below.

About

Hi, I'm Vineet Patawari. I fall in love with numbers after being scared from them for quite some time. Now, I'm here to make you feel safe with numbers and help you come out of Maths Phobia!

comments

  1. Pretty part of content. I simply stumbled upon your weblog and in accession capital to claim that I get in fact enjoyed account your weblog posts.
    Anyway I’ll be subscribing on your feeds or even I fulfillment you access constantly rapidly.

  2. Samir says:

    Will you please send me the expression for advance emi in excel sheet.i am unable to do that thanks

    Regards
    Samir roy
    8879987159

    • Vineet Patawari says:

      As mentioned in the post, while putting down the EMI formula (PMT) in excel, we’ve to mention TYPE – here we have to put the value either 0 or 1. If payments are made at the beginning (EMI in advance) of each period, 1 is used. If EMI payments are made at the end of the period (EMI in arrears) put 0. If omitted 0 is taken a default value.

  3. Manoj swami says:

    Hello every1.
    I have a problem,please help me with a solution of following…

    An amount of 9270 is to be returned in 3 equal installments. If rate of intrst is 3%per annum then find each installment value.

    Give a shortcut formula also if available please.

    • The Answer is 3091.287589

      • Manoj swami says:

        How did u calculate this. ? Please explain…

      • Mr. Manoj Swami

        I have Found out a new formula for this to do, not only this we can do lot or all calculations relating to EMI calculator, I think you did not visited my Blog please visit and Download my new type of EMI Calculator

        My Blog Link is: http://emicalculator3types.blogspot.in/

        Main thing in this blog is my New Type of EMI Calculator download it, if you do not download the EMI calculator wast of visiting my Blog, I have given so many links on the top of my blog all are same, so download from any one of the link and one more thing if you see the Normal Formula for calculating which i have given in the blog it is totally different formula because i have found out the formula again which is already exist instead of taking the existing formula from books or Website

      • There is no shot cut formula for 3 months. There is shot cut formula for 2 months but i don’t no the formula, i saw the formula in some book.

  4. Romin says:

    Is there formula for reverse calculation? Eg: EMI, P, R are known. Need to find the tenure.

  5. There are there 3 of EMI Calculators

    Download 3 Types of EMI Calculators: http://emicalculator3types.blogspot.com/

    Formula for Calculate EMI is:

    p x (((i/12) + 100)/100)^t
    ________________________________________________________________________

    1 + (((i/12) + 100)/100) + (((i/12) + 100)/100)^2 + ——— + (((i/12) + 100)/100)^t-1

    Where:

    P is Principal

    I is Rate of Interest per Annam

    T is Tenure

  6. NANDEESH H N says:

    I had a bad experience with a bank. Out of the total amount refunded every year, they were bifurcating it into principal refund and interest refund. Per year their interest was about Rs 10000/= more than what it should have been. I persisted with my calculation and after many months they admitted that there was a bug in their software and thanked me for getting hold of it.
    Better to be careful with the calculations.

    I had suggested a thumb rule which is quite useful in comparing different loan propositions and possible EMI.

    The thumb rule is
    EMI is slightly more than P * (r/2 + 1/n) where P = principal, r = rate of interest per month, n = number of months.

  7. purna says:

    nice one………..!!!!!!

  8. NANDEESH H N says:

    A rough estimate of EMI can be arrived at without calculator/excel as follows:

    EMI = p * (r/2 + 1/n)

    In your example: p = 100,000, r = 0.01, n = 24 then EMI = 100000 * (0.005 + 0.04) = 4500.

    Your actual calculation yielded 4707.

  9. NANDEESH H N says:

    A rough estimate of EMI can be arrived at without calculator/excel as follows:

    EMI = p * (r + 1/n)

    In your example: p = 100,000, r = 0.01, n = 24 then EMI = 100000 * (0.01 + 0.04) = 5000.
    This estimate is always slightly higher than the actual figure.

    Your actual calculation yielded 4707.

  10. [...] This post was mentioned on Twitter by Vineet Patawari, Quicker Maths. Quicker Maths said: How to calculate EMI?: Calculation of EMI In our daily life we face enormous application of mathematics. Calcula… http://bit.ly/fRZDgP [...]

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> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Google+ Plus Follow on Twitter Like On Facebook Stumbleupon

Get Maths Tricks by Email

Enter your email address:

Who is behind QuickerMaths?

I am Vineet Patawari - PGDM (IIM Indore), ACA, B.Com(H). My passion for Mathematics, specially Vedic Maths encouraged me to start QuickerMaths

I believe that if trained properly using powerful tools like Vedic Maths, the immense intellect of human mind can be ignited instantly - find out more

Recent Comments

  • gold chain necklace for women Every weekend i used to pay a quick visit this web page, as i want... – 23Aug14
  • hearthstone arena cheat Pretty part of content. I simply stumbled upon your weblog and in accession capital to... – 20Aug14
  • John 155 green plus blue = yellow red plus black = brown so 89 + 56 – 20Aug14
  • anu thanks u sir i understand the easy method – 17Aug14
  • prudhvi the answer for clock is 75min...i.e.,1hr 15min – 17Aug14