How to calculate EMI – Download Excel EMI calculator

By | June 21, 2009

EMI Being associated with bank for short-term, I often get this question from friends and relatives. How to calculate EMI? When you apply for loan, they will just tell you the EMI, but does not tell you how they are calculated unless you ask them specifically to show how they calculated it. EMI table is also called as amortization table, which shows the balance at each installment.

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.

It is easy to prepare an EMI table in Excel sheet. There is a financial function called PMT to calculate the EMI.

PMT(rate,nper,pv)

rate – Interest rate for the loan.
nper – Total number of payments for the loan.
pv – Present value/principal.

Prepare excel sheet with the following data and enter the PMT function in the cell “C7.”

EMI_Calculator

  • Rate = C6/C5, which divides rate of interest by number of months thus giving rate of interest per month.
  • Nper = C4*C5, which gives total number of months we need to pay equated installment.
  • Do not forget to take the loan amount in minus value, so that our EMI table shows positive values.

Now prepare table with Serial Number of Payment, Monthly Installment, Interest Amount, Principal Amount, and Balance. Take the first row with serial number “0” and in the balance amount point the cell to “Loan Amount” – type “=C3” Next Enter the serial number as 1. Monthly installments will remain same so take absolute cell reference (by adding $) which will not change when you drag the cells to auto increment. In “Monthly Installment” column, enter “=$C$7“. In the “Interest Amount” column, enter “=F10*$C$6/$C$5” which means we are dividing the balance amount at the end of the month by monthly interest rate (rate of interest/payments per year). As the rate of interest and yearly payments are not going to change, we used absolute reference using $ sign. In “Principal Amount” column type “=C11-D11” which deducts the monthly interest from monthly EMI and whatever the balance available shows as payment towards the principal amount. In “Balance” column enter, “=F10-E11“, which means we are deducting the principal amount in the EMI to ascertain the original balance at the end of the month upon which we are going to calculate interest for the next month. Now select the row and drag it until balance becomes “0”.

EMI_Table
That’s all! We made our own EMI calculator in Excel. This Excel EMI calculator is created for a tenure of 20 years. If you are just calculating for 5 years, i.e., 60 months, 61st month shows values red colored and in minus range. So you need to take up to “0” in the balance column. Ignore the values in RED. If you want to calculate for more years, then just select the last row and drag it to extend it.

Download the Simple Excel EMI Calculator.

Download the Excel EMI Calculator with Ad Hoc Payments.

Download the Excel EMI Calculator with Multiple Reimbursements.

Download the Excel EMI Calculator with All the Three Sheets.

Update – Oct 16, 2009: Mr. Parixit Namdhar, one of my blog readers, suggested that instead of entering the formula “=$C$7” in column “C” entering “=IF($B11>($C$4*$C$5),0,$C$7)” solves the minus values problem. This formula makes the minus values zero making it easily understandable and looks neat. Thanks a lot Parixit!

Update – April 22, 2012: Mr. Ravi Krishna, one of my blog readers, updated the Excel sheet with ad hoc payments and multiple reimbursements, which a lot of readers were asking. Thank you very much Krishna!

589 thoughts on “How to calculate EMI – Download Excel EMI calculator

  1. Shobhan

    Please publish a new calculator incorporating adhoc payments, with Multiple Reimbursements and interest rate changed multiple times. Thanks.

    Reply
  2. Prabhu

    Hi Naveen,

    This is by far the best and the most thoroughly thought out EMI calculator. This served my exact need.

    Regards,
    Prabhu

    Reply
  3. Rummana

    Hi,
    Can you add a cloumn for pre-payment adjustment……….

    Reply
  4. Indro Kumar Jalan

    Please publish a new calculator for compound interest calculation on monthly, quarterly, half yearly and yearly basis. It will also be very useful for us.

    Reply
  5. Shaks

    Nice work. but not much useful unless there is “pre-payment” column which is a must.
    Thanks

    Reply
  6. devashis

    can it be possible to include change in interest rate also , i.e with floating interest rate there is a change in interest rate in the middle of loan tenure also

    Reply
  7. Raja

    hi naveen. i ve gone through your excel sheet. it was nice and well done… keep it up your good work..

    Reply
  8. Navneet

    Thanks Naveen for putting that much of effort in your EMI Calculator which is really a very handy tool, but i need your help current version is reducing tenure while doing ad-hoc or part payment while i am looking for EMI reduce option, would really appreciate if you can make such changes in your EMI Calculator.

    Thanks alot…..

    Reply
  9. Raj

    Dear Mr. Naveen,
    It’s very nice calculator. i need your help to calculate hoan for under cunstructon home. before One year i took home loan Rs. 17,0000 for under cunstructon home. I took 3 dispersement of Rs. 3,00,000/3 months. I paid Rs. 16575/month. Now i want to check how much i paid Interest and how much amount adjsuted in principle amount. can you please advice?

    Reply
  10. sachin

    Thanks ..It was really good calculator but would appreciate if we could have something where variable changing rate of interest can also be considered from time to time as banks vary that ..for example 2 years back it was 9% , and today its 11 ..so along with adhoc payments if we can have that option as well, it would be gr8.

    Reply
  11. V RAMACHANDRA SHENOI

    sir
    On an analysis of an EMI schedule, interest is recovered first, and pricipal is accounted at a snail pace.
    The interest figure is not comfortable to read.
    Also Interest payment above 150000 do not get any benefit.
    Let me call this as interest prominent.
    Can you make this principal prominent????
    Meaning higher principal initially, and lower interest component.
    Can any one of you enlighten.
    I have something to say about it.
    First try to explain to me.

    Reply
    1. surendra

      the car loans are bottom down
      like what you said the principal part reduces fast
      home loans are bottom up
      that is the initially years we pay interest only.
      this is because car depreciates faster the banker dont want interst on it
      house appreciates so banker wants interst the principal reduces slowly,
      in case of default the bank gets profit from you and sells it to another friend of yours again make profit with same” cycle” name changed!!!
      got it?
      surendra

      Reply
  12. V RAMACHANDRA SHENOI

    sir
    The EMI schedule is an amortisation chart, which gives details of apportionment of principal and interest and liquidate the liability at the end of chosen period,
    It is true to have EMI value by input of data.
    How is the EMI value derived.
    I have an answer, but I wish to have it from you.
    vrshenoi

    Reply
  13. V RAMACHANDRA SHENOI

    Sir
    Can you explain the EMI fixation in terms of Deposit Products???
    I have something to say???
    ready to hear???
    v ramachandra shenoi

    Reply
  14. Babloo

    Hi,

    Thanks for the great calculator. Can you please update the sheet with an option for Tranche EMIs which is a useful offering?

    Thanks

    Reply
  15. shrikant satale

    can it be possible to include change in interest rate also , i.e with floating interest rate there is a change in interest rate in the middle of loan tenure also

    Reply
  16. Anu

    I want to change the values in Loan Disbursement to check the payment schedule for me .. can u pls send me a unprotected sheet .. it would be really useful

    Reply
  17. prasad

    i want to do monthwise payment of loan after payment it should be automatically update in loan amortization

    Reply
  18. venkat

    hi,
    I am looking for a emi calcualtor where i converted my credit card outbalance to 6 month emi with principal 24811 interest 1.49 for 6 months and the emi are 4399,4392,4384,4377,4369,4361
    it has 12.63 service tax and pre-closure of 2% of outstanding principal plus service tax. . . could you help what calculator we need to refer to find the split of monthly principal,interest and outstanding for pre-closure and which month we can go ahead for pre-closure . please suggest

    Reply
  19. Sunil Choudhary

    Hi Naveen

    Thanks for efforts…

    I am not able to find sheets which are with daily reducing interest rate. All sheets are seems to be with monthly rests only.

    Regards

    Sunil

    Reply
  20. NABAJIT PAUL

    Vary helpful for calculating interest as well as installments.

    Reply
  21. Meera

    Hi Naveen, Thanks a lot for this Excel… It helped us a lot… Thank you so much for the wonderful work with this excel… We are really grateful since this helps us understand the loan and prepayment and disbursement Impact… Execellent works

    Small request:
    Just that i am not able to update the Loan disbursement column and we had irregular disbursement based on builders demand… Could you please make the Loan disbursement column unlocked… Since it can’t be updated as per need.. that would really help a lot as different people have different disbursement schedules…

    Thank you so much,
    Meera

    Reply
    1. Naveen Post author

      Sorry….I won’t be able to help. I just made the simple EMI calculator. Another guy extended it and made those advanced calculators.

      Reply
    1. Naveen Post author

      Those were protected with a random string to prevent plagiarism, so I don’t know the password…sorry!

      Reply
  22. Vijaya

    Excel sheet is protected with password, can you please share the same, so that i can see the formula how this is derived

    Reply
    1. Naveen Post author

      Those were protected with a random string to prevent plagiarism, so I don’t know the password…sorry!

      Reply
  23. Ajikumar

    Naveen,

    It’s awesome calculator and very nice features. Good work!!

    The sheet – EMI Calc with Ad Hoc & Multiple, the subsequent Loan Disbursement is not getting zero even if I filled the disbursement amount fully or paid in a single instance. Would be nice if you fix and upload it. thanks!

    Reply
  24. Sammar

    Hello Naveen,
    Can you please add home loan calculator with partly prepayment option for reduced EMI and keeping loan tenure same i.e. after i do partly pre payment of my loan, it should reduce monthly EMI keeping loan tenure same.
    Please email me that calculator in excel.

    Many thanks

    Reply
  25. Krishna

    This is very nice calculator, user friendly, really I am looking for the same. Can you please share the password that you using to protect the sheet

    Thanks

    Reply
  26. Ravindra Vyas

    Great work naveen,

    Very very helpful thanks for all your work and your team work.

    Reply
  27. kavita sharma

    but i am not able to modify the disburse amount… in loan disbursment coloum…its say its protected….please help

    Reply
    1. Naveen Post author

      You can use it for two wheeler finance too. It can be used for any type of loan disbursed under EMIs.

      Reply
  28. Amitkumar

    Hi,
    Excel with name EMI Calculator with Multiple Reimbursements I am not able to change Loan Disbursement amount after sr no 1, it is telling sheet is protected. please help me to resolve this.

    Reply
  29. Nikhil

    Hi Naveen,
    first of all thanks for designing excellent calculators.
    I need one more calculator if you can help me out

    Balloon Calculator- where I can enter loan amount, ROI, Tenure in months and Balloon amount (Last EMI) – so that the result would reflect as ‘EMI’ with amort schedule.

    Reply
  30. Jay

    Hi Naveen, Adhoc Payment xls is not changin the amount. If i change it to 20Lakhs, the balance still shows 30 lakshs and calculate the same however the top portion its getting updated. Please check for me.

    Reply
  31. danny

    Hi Naveen, Could the spreadsheet be updated for Fixed interest rate for X number of years & variable (Floating) interest rate when ever the interest rate changes during the Loan term… Thanks Danny

    Reply
  32. Rahul Jain

    Must appreciate the way you have made it so that non-finance people like me can benefit…

    Reply
  33. Sandip

    Great Work. I was Looking for that i Find it Here that Excel Sheet Which I want. Wish u Best Luck.

    Reply
  34. Rajeev

    Hi Navin,

    Good Work !!!!!

    Can you also provide Home Loan Eligibility Calculator.

    calculating EMI is good but If we can check our eligibility by putting few details in excel sheet by our own then it would be awesome.

    Please think over it….

    Reply
    1. Naveen Post author

      It depends on lot of factors like your earnings, loan amount, EMI years, past credit score, and also bank has their own terms in determining the loan eligibility. So, it is not possible to make a calculator for that. Check out CIBIL’s Loan Calculator: https://www.cibil.com/loan-calculator

      Reply

Leave a Reply to Sunil Choudhary Cancel reply

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