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

    Dear
    Naveen this calculator is very helpful but i required the half yearly calculator but in now first emi in 5 month and second 6 month in a year so how can calculate pls suggested

    Reply ↓
  2. Avinash S

    Hi Naveen,

    Thanks for this calculator!! I was really searching this on net. But can you help me Excel EMI Calculator with Ad Hoc Payments for floating rates?

    Reply ↓
  6. MOHAN RAJ

    HELLO NAVEEN COULD YOU PLEASE SEND ME PERSONAL LOAN EMI CALCULATOR IN EXCEL FORMAT.

    Reply ↓
  7. abdul razak

    very nice mr naveen great job you are doing
    this sheet is very help full to each and every person

    Reply ↓
  9. mohan

    Hi Naveen,

    This EMI_Calculator_with_Adhoc_Payments excel template is very useful. But I am not able to change the Loan amount, Its defaulted with 30L in the Balance column. Its asking for password to update that value. Can you please help me out here.

    Reply ↓
  10. Gayathri

    Hi Naveen,

    I have been looking for this information all along. Many many thanks for sharing EMI calculation excel sheets.
    I am interested in the calculations for subsequent loan disbursements from the second month. Please share the password to unprotect the sheet .
    Secondly, I am interested in working out the calculations for Pre EMI (only Interest portion payments for first few months and then tranche EMI until full disbursements.
    Look forward to hearing from you.

    Regards
    Gayathri

    Reply ↓
  11. abhit s k

    I want irregular emi payment calculetor.
    Supose if I forgot to pay emi for a month then how to calculate interest?

    Reply ↓
  13. Sreekanth

    Hi

    Thank you for the details.
    Please can you provide me the with floating rate option which will be helpful to calculate the remaining months of payments along with adhoc irregular part payments.

    Reply ↓
  14. KRITI

    naveen,

    this software is awesome!!

    I WAS DOWNLOADING TALLY——BUT YOUR SOFTWARE SAVED ME FROM DOWNLOADING!!

    GREAT!!GREAT!!GREAT!!GREAT!!GREAT!!GREAT!!GREAT!!

    Reply ↓
      1. N S Mahendra

        Dear Naveen,

        It was really a need for anybody to keep track of the EMIs they are paying and also upfront they can keep a tab on their cash flows.
        Appreciate your excellent work and shared with everybody.

        Also a small suggestion along with this the interest calculation should be available in number of days also as now a days all the Banks have got one or the other financial softwares installed and they will be calculating the interest rate for number of Days from the day its applicable.

        Thanks

        Reply ↓
  16. Mel

    I am trying to calculate loan on the reducing balancing method but payments are made on a fortnightly basis or monthly basis. The term of loan is 18 months in total which would require 36 payments for those persons paying fortnightly. Can you assist me in achieving this on your excel sheet that i downloaded.

    Thank you

    Reply ↓
  17. Dinesh Kumar Parik

    Hi Naveen,

    This EMI_Calculator_with_Adhoc_Payments excel template is very useful. But if you annual total of repayment of Actual Principal amount and Annual payment of Interest , that is much useful for all your user and if possible please provide month in front column .

    if possible please mailed us after necessary correction/ suggestion.

    and your calculation sheet in all three formats very fantastic , i loved it.

    Regards

    Dinesh Kumar Parik

    Reply ↓
  18. laxmish

    hi naveen,,
    indeed a very nice calculator… is it possible to remove the loan disbursement in every 6 months column.
    i wanted the same calculator for just 60 monthly installments with reducing interest. and also if in between if extra amount is paid. i want to the know balance loan amount.

    Reply ↓
  19. Abubakar

    Hi Naveen,
    you are doing great job. but can i have the password to unprotected the sheets?

    Reply ↓
    1. Rakesh Maniya

      Dear Naveen.

      It is very useful. would appreciate, if you can share the password to unprotected the sheets.

      Reply ↓
  20. Rajib Roy

    Hi Naveen

    Thank you for the details.

    very nice mr naveen great job you are doing
    this sheet is very help full to each and every person

    Reply ↓

    Reply ↓
  21. Sagar

    Hello, I have taken HDFC home loan for under construction property. So my loan amount is not fixed. It varies as per disbursements. This calculator thus, is not of much use to me.

    Do you have similar excel for these kind of case?

    Thanks 🙂

    Reply ↓
  22. vikas chavan

    mera lone kitna Kata aur kitna balence hai bataya jaye he net per dikhaya jaye

    Reply ↓
  23. Raghava

    Hi Naveen, sorry for the typo mistake the i referred wrongly for the Rate of Interest column (I mean M6), sorry for inconvenience

    Reply ↓
  24. Lakshmi

    Hi,

    Very helpful excel. But it would benefit more if there is a similar excel available for floating interest rates with part payments.

    Thank you

    Reply ↓
  25. Amit Sharma

    Dear Navin
    I want to create a excel for daily and weekly emi calculation please sugges me if you will creat the same what charges you will be taken inform me on my below mention mail id.

    Reply ↓
  26. Manivelu

    This is a great excel but i want to change my EMI adhoc schedule so can you share the password?

    Reply ↓
  27. karthick

    Dear Naveen
    Pls send the EMI calculaer in excel format to my mail ID.

    Your’s Lovingly
    M.Karthick

    Reply ↓
  28. Mohit

    Dear Naveen,

    I found your blog as well as the excel files very helpful in understanding the EMI concept. Primarily, I understood how the interest is being calculated.
    However, there is a point in which I am stuck. The rate of interest in my case is floating. Lets say the rate change from 10% to 10.5% after 10 installments of EMI. How will this change in ROI reflect on my remaining EMIs as well as on the interest generated?
    I couldn’t find this facility in existing sheets. Upon modifying them myself based upon my understandings, the results weren’t correct. It would be very kind of you if you could help me out with this situation.

    Thanks and Regards.

    Mohit

    Reply ↓
  29. Anandganesh D

    Weekly EMI DAte Calc formula for EXCEL

    for MICRO FINACE RECOVERY WEEKLY BASE

    Reply ↓
  31. atul shah

    I kow the loan amount. I also know th rate interest. I alo now the installment I ccan oay. But I wnat to find out Tenure. Can you give me FORMULA FOR THIS. you can send by email atulshah2201@gmail.com 9930009027
    Thanks.

    Reply ↓
  32. Brijesh Shetty

    Hi Naveen,
    Need your help.

    According to the bank, the way of calculations is a little different. Let
    me explain
    1) I have an EMI $36,176.74, based on the Period (20 yrs) and amount
    ($3,950,000.00)
    I have made a prepayment of $ 950000, but my EMI is kept constant, and my
    tenure is reduced (reduced to 10 years approx).
    2) Since its a floating rate the rates have changed 3 times in the year.
    However as of now EMI is still kept constant and the Tenure changes (goes
    from 10 to 12 years approx)
    3) Now i had the flexibility of changing my EMI to $ 12,500 per month. Now
    the Tenure again changes (to 15 years approx)

    In your sheet i am not able to accommodate the above changes.
    Do you have any updated sheet which can provide me with the flexibility of
    the 3 points mentioned above?

    Reply ↓
  33. Sathish

    Hi Naveen,

    Great job. Even I have the same query. My HL interest has been reduced thrice but the EMI amount was constant all these days. With this scenario, I could not re-calculate / re-build the excel sheet. If you could add additional column for change of interest that would be helpful.

    Also, in case of partial disbursement, the interest is calculated from date of disbursement. So, include a date column and calculate the interest based on the same.

    Thanks, Sathish

    Reply ↓
  34. smita

    Hi Naveen,

    I need the calculator with floating interest rate option. Please send me one

    smita

    Reply ↓
  36. Karthikeyan

    Sir I need clarification for my loan AMT. I have 12 lakhs credit I am paying daily 10000 for 150 days what will be the interest I have to give them if the interest rate is 800 per lakh per day please help me urgent

    Reply ↓
  37. Surjeet Kumar

    Hi,
    I have more than 40 cars and pay monthly EMI and i have to maintain monthly report for this
    but the reason is there I have to prepare monthly closing balance report.(etc; opening-monthly EMI0…Could you please advise me is there any formula for automatic Deduction from loan- monthly EMi which is link to calender or fleet list

    Reply ↓
  38. Vinamra Chandra

    Hi Naveen,

    For years I was overwhelmed by EMI calculations. I finally tried to understand from my friends at work then came to this page… Now I finally understand the mechanics.

    I have updated your template with variable rate of interest. If you are interested, I can share it with you.

    Regards,
    VC

    Reply ↓
  39. harish anand

    Hi naveen,

    Actually the excel calculator utility with name Download the Excel EMI Calculator with Ad Hoc Payments. has fixed value in balance field of Q11 column which is basis of all emi calculation,suppose i change my loan amount to greater than 3000000 lakh then i think the calculation is wrong.kindly have a look and suggest.

    Reply ↓
  40. Ashish Jaiswal

    Hi naveen,
    Thanks for nice xcel sheet. It’s really helpful. I need to change some data in adhoc payment sheet but it is password protected. Could you please provide me the password for the same.
    Thanks in advance.
    -Ashish

    Reply ↓
    1. Naveen Post author

      It was a random password to protect the spreadsheet from plagiarism. I can’t recall it. Sorry!

      Reply ↓
  41. Anubhav Jain

    Hi Naveen,

    Do you have excel sheet for SBI Maxgain account calculation on daily basis ??
    If yes, can you share it?

    Thanks,
    Anubhav

    Reply ↓
  42. Asim Shafique

    Dear Mr Naveen, Hope you are keeping well and safe, must congragulate you on supporting through excel based worksheets. May i request for your guidance and help for a spreadsheet to work out the Provision on the lease rent receivables based on categories ( Sub Standard 60-90 days, 91-180 Days for Doubtful, 181-209 Loss and 210 above is writeoff off category?
    I am trying to workout the same but not able, hope you can be of help? Best regards

    Reply ↓

Leave a Reply

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