How to calculate EMI – Download Excel EMI calculator

By | June 21, 2009
589 Comments

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. praveen kumar

    Hi
    Easy way to create a Excel Loan Calculator(MS2007)

    1. In Excel, right-click on the tab of sheets at the bottom (‘Sheet1, Sheet2, etc’).
    2.Select ‘Insert’
    3.In the popup box, choose the ‘Spreadsheet Solutions’ tab
    4. Double-click the ‘Loan Amortization’ option

    Thats all..!!..:)..

    Reply ↓
    2. Suresh

      Thank you very much!!!
      for interest calculation very useful tks praveen

      Reply ↓
    3. balasubramanian

      I am looking for softeware for preparing projection for term laon

      Reply ↓
    4. V Sairam

      Thanks!! Mr. Naveen Kumar. It was very simple and useful. Is there a way to remove the $ sign?

      Reply ↓
    8. arti

      cool solutions..i dint know this…share some more facts regarding the use of excel…amazing 🙂

      Reply ↓
  3. VIKASD

    Hey nice work man…i was unaware of loan calculation available in excel…nice post..thanx Praveen & Naveen…

    Reply ↓
  4. Ashish

    Thanks Praveen

    But can we change the $ sign in the Rs format

    Reply ↓
  6. Ajit Shah

    Hi,

    Thanks for the excel file.

    Can you please add all details of how computation is made, which is currently in your blog page, as a “readme” worksheet? It will help while using the excel file offline.

    Ajit

    Reply ↓
    1. sandesh

      Dear Ajith,

      Tht is informative and friendly working sheet, if u want to know the formula, please break the password and get the functional formulas.

      SAN

      Reply ↓
  7. A Hameed

    Great and Interesting, please let me know I have taken a loan amount of Rs. 18 Lakh and tenure is 15 years with Interest Rate is 11.5%. I started my EMI payment from Sep’2011. Assume if I pay back or clear Rs. 5 Lakh to bank. What will be my monthly EMI? Could you please send me the excel sheet and merits.

    A Hameed

    Reply ↓
    1. Arvind

      Hi Hamid
      Your EMI will reduce from 21 K to 15K approx .
      Your original loan 18 L . You must have paid 10 emi as of now hence the principal remaining 17.6 L . Now when you Pay 5 L then pricipal remaining 12.6 L . Hence for this the remainin time within 15 yeard i.e 15yesars – 10 month already paid will be 15 K .
      bye

      Reply ↓
  9. steve

    Hi Naveen
    Thanks for the good work. Keep developing more tools for the indian context.

    Reply ↓
  12. Subash

    This helped me a lot to understand in a simple way and thanks for developing this.

    Reply ↓
  14. pvravi

    your emi cal is very good, is it possible to change the roi during any period.
    say first 6 months 10 % roi may go up to 10.25% after 8 months roi may come down 9.85%
    but the loan period should not change.

    Reply ↓
  15. Alok Kumar

    Hi frnd ,
    I am searching the calc ( xls) for daily basis reducing prepayment plan….. ny one can help me …plzzzz.

    Reply ↓
        2. kuldeep

          what is the procedure to calculate the principal amount in pre-emi installment plan

          Reply ↓
  17. Shivaji Kate

    This Calculater help me in every problem to calculate lots of thing

    Reply ↓
  18. Prashant Bongale

    thanks Navin excel sheet good. easy to understand instead of calling bank & check this out

    Reply ↓
  21. Janardan D. patekar

    Its very helpful excel magic for me.
    Thank you sir.

    Reply ↓
  22. Mayank

    thanks Navin excel sheet good. easy to understand instead of calling bank & check this out

    Reply ↓
  23. Sharan

    U have copied from the excel templates and just removed the dollar sign. I want to know what abt 2.5 yrs… can u change it.

    Reply ↓
  24. Akhil Singh

    I downloaded all the files however it is asking for a password before opening… Whr is it?

    Reply ↓
    1. Naveen Post author

      It won’t ask password for opening. Instead, it asks when you modify the cells which are protected and they should not be edited. All the details you need to edit are on the top.

      Reply ↓
  25. Rahul Shah

    I am looking for a loan of around 30 years with adhoc and multiple reimbursement from time to time . Let me know can the excel sheet support the same.

    Reply ↓
  26. Manjunath

    It is really helpful. Thank you Mr. Naveen for creating such a wonderful excel sheet.

    Reply ↓
  27. Gumnaam

    Hi Naveen.
    Really nice and convenient excel tools. Helped me tremendously in making some plans for taking a loan.
    -Gumnaam

    Reply ↓
  28. TSB

    can u provide the amortization for Home loan using reducing balance method? in excel sheet

    Reply ↓
  29. Saravanan

    Hi Naveen,
    Thanks for EMI excel sheet calculator. Very helpfull for home buyers.

    Do you have varaible inerest rate calculation along with pre-payment. For exambel, When I start the loan, the interset rate is 10.25% and it increased to 10.75 after 3 months of payment, and now this is get change to 10.5% after six months. I also made one time (ad-hoc) prepayment. I could not get exectly how much prinicipel and interest is there for the remaining months. or howmany months and amount increased/decreased due to interset rate change.
    Can you help me if you have done already this kind of calculation in excel.
    Thanks,
    Saravanan.V (vsarav@yahoo.com)

    Reply ↓
  31. CIBIN

    Hi,

    Can you give the password for un-protecting these sheets?

    Thanks.

    Reply ↓
    1. Naveen Post author

      I just entered random keys to protect it to avoid plagiarism. They were random keys and I can’t recall them.

      Reply ↓
  32. Anvesh

    Hi Naveen,

    I have a FD of 50000 for 5 year and taking a loan on that 30000 at 2% and paying 3000 PM can you give me excel for Maturity verses repayment

    Reply ↓
  33. Prabir

    Is it possible to provide the calculation on monthly basis? We have some loans which are for twelve months with two months moratorium, i.e. repayable in 10 months.

    Reply ↓
  35. Alhad

    Hi Naveen,
    For floating interest rates, is it possible to modify the EMI calculator to take care of following:
    1) Change in interest rates,
    2) Pre-payment is done, and loan period is kept same. (EmI is changed)
    Or
    2) Pre-payment is done, and loan period is changed, EMI is changed.
    3) pre-payments considered with option2.

    Reply ↓
  38. Rajesh

    Thanks a ton naveen, the xls sheet has given me gr8 help.
    Can please add a column for sum of all Interest paid.

    Reply ↓
  41. Phi

    Hi Naveen, Great calculator… If I were to miss a months or two months payment…how do i re-calculate..as the spreadsheet wont let me delete the payment due to the cell being locked.

    Reply ↓
  43. YOGANDER SIROHI

    HI NAVEEN, GOOD CALCULATOR. ACTUAL M APPLYING FOR HOME LOAN AND I HAVE CALCULATED THE EMI THROUGH YOUR CALCULATOR DATS GREAT, THANKING U DEAR

    Reply ↓
  44. Rohit

    I have the same question which few people have already asked but you did not answer them yet. Do you have a sheet which can take care of floating interest rate changes .

    1. Change in interest rates,
    2. Pre-payment

    Reply ↓
  45. Ajay

    Hi,
    I have same question as above,what if,
    1.Partial imursement of loan,
    2.Change in interest rate
    3.frequent Pre payment

    Please share if anything with this is available.

    Reply ↓
  46. Balachander K

    Hi Naveen,

    Can you come up with the multiple ROI with various dates should calculates the exact Interest & Principle component. For Eg. – Please find the below table

    Amount of Loan 2300000
    Annual Interest Rate 11.50% At the time sanction
    Annual Interest Rate 11.25% With effect from 1st May, 2012
    Annual Interest Rate 11.00% With effect from 10th Sep, 2012
    Number of Payments 234
    1st EMI start date is 16th Mar, 2012 (Feb 16th -Mar 15th)

    Reply ↓
  47. Vivek

    Is it possible to add i column for month and Years like jan-2012?

    Regards
    Vivek

    Reply ↓
  48. Puneet Rai

    Hi Naveen
    Thanks a lot for the calculator.Really nice tool .Just had one query
    How to calculate Emi for changing interest rates.
    How to reduce Emi by prepayment while duration of loan repayment remains same

    Reply ↓
  49. Kathiravan

    First i really thanks for ur initiative,

    i need the background formula working of PMT in excel formulae,

    reply awaited

    Reply ↓
  50. riddhish

    Dear Mr. Naveen,

    i am using your loan calcullator since long, now i need a calcullator for fixed principal repayment.

    suppose my loan term is 5 years, principal repayment will be same for 60 months and interest will change. so my emi amount will change every year

    Reply ↓

Leave a Reply

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