Jun 21

How to calculate EMI – Download Excel EMI calculator

Tutorial, software Add 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 Excel EMI Calculator.

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.  Download the Version 2 from the below link.  Thanks a lot Parixit!

Download the Excel EMI Calculator V2.

Related Posts:

  1. Savings account interest rate – Now calculated on a daily basis After making usage of any bank ATM free, RBI now...
  2. Download streaming videos with Real Player 11 This week I have updated my Real Player to the...

\\ tags: , , ,


85 Responses to “How to calculate EMI – Download Excel EMI calculator”

  1. 1. jitendraNo Gravatar Says:
    June 28th, 2009 at 11:36 pm

    Dear Naveen

    Your EMI calculator is really helpful.

  2. 2. Ravindra MaratheNo Gravatar Says:
    July 9th, 2009 at 6:37 pm

    Hi Naveen, I found your EMI calculator on the web and found it very useful.
    Thank you.

    -ravi

  3. 3. ShakilNo Gravatar Says:
    July 13th, 2009 at 3:24 pm

    How to get monthly installment if you have only. Period & loan amount & interest rate…….? if you dont have PMT formula.

  4. 4. cp dagaNo Gravatar Says:
    July 14th, 2009 at 10:47 pm

    naveen G
    ur software saved my lot of valueable time.
    thanks for this social service!

  5. 5. yashpal rajputNo Gravatar Says:
    July 23rd, 2009 at 11:26 pm

    I had been looking for the method how to calculate emi in excel for a long time. Thank you very much that you have not put your password in the coloums.

  6. 6. s.c.koriNo Gravatar Says:
    July 30th, 2009 at 3:13 pm

    dear mr naveen pls send excel spread sheet for calculate home loans

  7. 7. NaveenNo Gravatar Says:
    July 31st, 2009 at 7:08 pm

    It is same like a personal loan….just enter the loan amount, rate, and tenure. That’s all.

  8. 8. yatishNo Gravatar Says:
    August 6th, 2009 at 3:50 pm

    Dear Mr Navin,
    Many many thanks for your efforts. all the best. thanks.

  9. 9. jyothi r nayakNo Gravatar Says:
    August 11th, 2009 at 9:07 am

    good you have done good effort

  10. 10. NaveenNo Gravatar Says:
    August 12th, 2009 at 8:42 pm

    Thanks for taking time to appreciate my work.

  11. 11. ManshaNo Gravatar Says:
    August 21st, 2009 at 3:26 pm

    Good effort. It was very useful

  12. 12. KapilNo Gravatar Says:
    September 1st, 2009 at 7:52 pm

    Its really very gud and useful.

  13. 13. Mukesh RoyNo Gravatar Says:
    September 7th, 2009 at 10:42 am

    Thanks for the EMI Calculator, But do you have any Excel sheet to calculate the Actual Area of Flat by adding different rooms.

  14. 14. NaveenNo Gravatar Says:
    September 7th, 2009 at 8:43 pm

    Hello Roy! If you can explain me your requirements, I will search, learn, experiment, and come up with an excel sheet which can automate your requirements.

  15. 15. BhaskarNo Gravatar Says:
    September 11th, 2009 at 11:47 am

    Naveen, great tool yar. Very helpful to know the balance :) . Thanks for sharing.

  16. 16. AbhishekNo Gravatar Says:
    September 11th, 2009 at 3:02 pm

    This calculator is really very helpful. thanx a ton!!
    Is this is the standard one, used by all banks?

  17. 17. SenthilNo Gravatar Says:
    September 14th, 2009 at 10:31 am

    Hi Naveen,

    Please let me know how could i recalculate my EMI ,if I pay 20 % of the principal amount after 12 months .

  18. 18. R.JhaNo Gravatar Says:
    September 22nd, 2009 at 3:34 am

    Excellant. Thanks a lot. Can you please also provide us saving or fixed deposite calculators..

  19. 19. NaveenNo Gravatar Says:
    September 22nd, 2009 at 6:42 am

    Thanks for appreciation. Sure…I will provide all useful calculators in a single Excel sheet when I get free time.

  20. 20. kamalNo Gravatar Says:
    September 22nd, 2009 at 9:04 pm

    many thanks Naveen ji,
    Its really easy and useful.
    thanks again

  21. 21. Srinivas SNo Gravatar Says:
    September 23rd, 2009 at 3:27 pm

    First of all thanks for this excellent sheet. However I also would like to know how to calculate the same for a diminishing loan……I dont know exactly what they (bankers) mean by diminishing loan but if you can help me on that, it would be great!
    thanks in advance!!!

  22. 22. SameerNo Gravatar Says:
    October 6th, 2009 at 6:07 pm

    Wonderfull

    Appriciated it had helped me solving my Problems regarding

    If you could also provide some info on Schedules and Variable Rate of Interest to Parameters like
    EFS
    EPI
    PMI
    PPF
    IOA
    IPI
    Which will still helpfull

    Keep up the Good Work
    Regards
    Sameer

  23. 23. Aditya FNo Gravatar Says:
    October 7th, 2009 at 3:19 pm

    Dear Naveen,

    Is there any formula where there is moratorium period (Late commencement of EMI) is there.

    Else the excel sheet is very useful.

    Regards,

  24. 24. nishikantNo Gravatar Says:
    October 9th, 2009 at 12:30 pm

    hi naveen
    ur EMI calci really saves a lots of time and more comprehensive.

  25. 25. Ravi RothkarNo Gravatar Says:
    October 10th, 2009 at 11:08 am

    Dear neevem
    we are looking for such a calculator from long time
    its realy a helpful

  26. 26. NaveenNo Gravatar Says:
    October 11th, 2009 at 8:22 am

    I am glad you find it helpful.

  27. 27. moulishwarNo Gravatar Says:
    October 15th, 2009 at 11:24 pm

    required excell EMI calculator

  28. 28. NaveenNo Gravatar Says:
    October 16th, 2009 at 6:32 am

    I think you haven’t noticed the link. It is at the bottom.

  29. 29. cr deshmukhNo Gravatar Says:
    October 28th, 2009 at 2:27 pm

    thanks for your service that to free.
    if you need my help in indore do mail me
    thanks
    cr deshmukh

  30. 30. NaveenNo Gravatar Says:
    October 29th, 2009 at 6:55 am

    Thanks for your kind comments.

  31. 31. ShankarNo Gravatar Says:
    October 31st, 2009 at 8:29 am

    Is any facility avail to insert EMI calculator to our blogs?

  32. 32. NaveenNo Gravatar Says:
    October 31st, 2009 at 10:49 am

    Just google the words “EMI calculator widget” and you will find some.

  33. 33. sowkarthickNo Gravatar Says:
    November 3rd, 2009 at 6:56 pm

    your calculator is very useful to me… thank you very much

  34. 34. AnkitNo Gravatar Says:
    November 3rd, 2009 at 6:58 pm

    Hi Naveen,
    Thanks for sharing the information about how to calculate EMI.
    Its really very helpful to know the remaining principal amount after each EMI.

  35. 35. puneetNo Gravatar Says:
    November 4th, 2009 at 11:35 pm

    hey this is reaaly good thing which is very helpful so that no banker can make fool of a consumer if this kind of things are easily avialable to a common people ……

    keep it up ………..

  36. 36. akNo Gravatar Says:
    November 5th, 2009 at 1:02 pm

    Hi Naveen, this is very nice and helpful… 1 suggestion… way not put another row after Row 7. This should have the total amount that you will be paying. That is C7*C5*C5.

    This will give a quick complete information and will help people decide better…

  37. 37. Pradip GoenkaNo Gravatar Says:
    November 6th, 2009 at 1:48 pm

    Hi Naveen,

    Very Useful calculation, looking for since long.
    Please provide if possible emi calculation on reducing balance.

    Regards

  38. 38. badriNo Gravatar Says:
    November 9th, 2009 at 11:45 am

    can someon tell me how to calculate EMI on simple calculator? Esp for longer term like 72 to 84 months. Once my friend did it on simple calculator, the next time he did it on his mind….was amazing. am sure there should be a simpler way to break down values

  39. 39. SatheesanNo Gravatar Says:
    November 9th, 2009 at 3:30 pm

    Dear Naveen
    Really useful work. Thank u for sharing. I was looking for such calculators for fixed deposits and monthly recurring deposits where compound interest is calculated either monthly or quarterly basis.best wishes
    with regards
    satheesh

  40. 40. NaveenNo Gravatar Says:
    November 15th, 2009 at 10:07 pm

    Thanks for your suggestion.

  41. 41. Pradip LayekNo Gravatar Says:
    November 17th, 2009 at 4:59 pm

    Dear sir,

    I am really grateful from your note. I really appreciate this kind of simple way to make people understand things. I would really like to be a part of your team

    thanks,

    Pradip Layek

  42. 42. BhimNo Gravatar Says:
    November 17th, 2009 at 5:25 pm

    Realy your excel calculator is realy helpful

    Thanx

  43. 43. Yasir SaeedNo Gravatar Says:
    November 19th, 2009 at 2:10 pm

    Dear Naveen,
    The information you have given is really helpful.
    Thanks, God bless you.

    Yasir

  44. 44. MurahariNo Gravatar Says:
    November 19th, 2009 at 3:51 pm

    Thank you very much naveen thanks a lot

  45. 45. SIJO THOMSONNo Gravatar Says:
    November 26th, 2009 at 2:04 pm

    Dear Naveen,

    The information you have given is really helpful.

    Thanks, God bless you.

    Sijo Thomson

  46. 46. prasanthNo Gravatar Says:
    December 2nd, 2009 at 2:40 pm

    Dude…

    Thanks a lot for your emi calculator….

    Best wishes.

    Prasanth.

  47. 47. ganeshNo Gravatar Says:
    December 5th, 2009 at 7:57 pm

    Hey,
    Thanks a lot Buddy, you have saved my time. Keep up with your social service.
    Thanks once again. God bless you:)

    Ganesh

  48. 48. anupNo Gravatar Says:
    December 10th, 2009 at 11:09 pm

    It is very informative tool in finance & Accounts

  49. 49. kanaNo Gravatar Says:
    December 12th, 2009 at 3:13 pm

    Sir
    Example
    Loan amount 15000
    Monthly Pay 1000 and 12% intrest

    I want to statement reduce to Monthly installment and intrest

  50. 50. sanjayNo Gravatar Says:
    December 16th, 2009 at 5:33 pm

    thanks a lot – it is really helpful to all.

  51. 51. NaksNo Gravatar Says:
    December 17th, 2009 at 2:15 pm

    Thanks alot. It is really helpful. It helped me in understanding saving I will make by switching bank :)

  52. 52. ShwetaNo Gravatar Says:
    December 18th, 2009 at 12:50 am

    Hi,

    Thanks for EMI calculator. It was very useful. Can you advise how EMI is calculated when we make part payment and when we try to fore-closure (when they ask for 2% surcharge)

  53. 53. maniNo Gravatar Says:
    December 20th, 2009 at 11:51 pm

    i have input data like loan amount, rate of interest, monthly installment amount. How to calculate loan term?

  54. 54. Ganesh (Chennai)No Gravatar Says:
    December 22nd, 2009 at 2:53 pm

    Naveen,

    Excellent, Please provide us some more useful tools like this. Is there way to arrive at Period, Loan amount and pre-closure period to gain maximum advantage!!!

    Thanks

  55. 55. shrikantNo Gravatar Says:
    December 25th, 2009 at 12:17 pm

    nice thnx
    i am bank employee

  56. 56. sruthiNo Gravatar Says:
    December 30th, 2009 at 5:09 pm

    hai naveen.
    U r EMI calculator is very helped me a lot and saved my time.
    I need some suggestions regarding experimenting with EXCEL sheets.

  57. 57. ravi kant sharmaNo Gravatar Says:
    December 30th, 2009 at 11:46 pm

    dear sir,
    great ,really helpful for me.thanks a lot.

  58. 58. Vikas HNo Gravatar Says:
    January 4th, 2010 at 9:58 am

    Hi Naveen,

    Great software…….. helped me a lot in getting bifurcated details on the loan payment.
    Thanks a lot again…….

    Kind Regards,
    Vikas

  59. 59. vinitaNo Gravatar Says:
    January 5th, 2010 at 8:50 pm

    Hi Naveen,
    Thanks. found it very useful.

    regards,
    vinita

  60. 60. JimNo Gravatar Says:
    January 7th, 2010 at 8:58 am

    Thanks a lot Navin for this wonderful emi calc.
    I have a question how to calculate EMI for loan rates esp. car loans where we have differential rates. for eg. 8%(1st yr), 10%(2nd yr), 10%(3rd yr) and RBI rate from fourth year. In that case which values to change?

  61. 61. SrinivasaRao GuduruNo Gravatar Says:
    January 10th, 2010 at 12:07 am

    Mr.naveen
    Thank you very much for your effort in making the HL widgets and spread sheets ,aking our life easy.
    How to use it when frequently changing ROI? pls clarify.

  62. 62. JamunaNo Gravatar Says:
    January 12th, 2010 at 6:07 pm

    Thanks Naveen for ur EMI calculator…… very helpful for us

  63. 63. BhaskarNo Gravatar Says:
    January 13th, 2010 at 7:31 pm

    Hi Naveen,

    First of all let me thank you very much for the valuable calculator.

    I have taken loan for 20 yrs for X amount @ 10%.
    After 3 years the rate of interest has been reduced to 8%, how do I know my Principal and Interest with this new rate of interest.

    Waiting for your valuable reply.

    Regards,
    Bhaskar.

  64. 64. NidhilNo Gravatar Says:
    January 13th, 2010 at 11:53 pm

    Halllo Naveen,

    This is very useful , thanks for sharing this information ,

    Appreciate your good work …

    Thanks
    Nidhil

  65. 65. AMIT VERMANo Gravatar Says:
    January 14th, 2010 at 11:48 pm

    hi,
    Dear Mr Navin,
    its great, i mean ur emi calculation sheet , is wonderful.
    can u incorporate one more column, so that impact of additional repayments or monthly some lumpsump payment can be shown on, in excel sheet
    thanks
    amit

  66. 66. Dhirendra Singh RautelaNo Gravatar Says:
    January 19th, 2010 at 2:36 pm

    Dear Mr. Navin,
    Its great, its very helpful for me.

    Thanks,
    Dhirendra Singh

  67. 67. Sanskar TanmorNo Gravatar Says:
    January 25th, 2010 at 11:51 am

    Hi Naveen,
    Thanks a lot for ur gr8 work.
    I had the same calculator which i used to use online.
    But yesterday, I was trying to do it offline at home.
    I stuck somewhere.
    I got the answer.

    Thanks a lot.
    Keep on providing such work for offline use. :)

  68. 68. dharmaboopathyNo Gravatar Says:
    January 27th, 2010 at 4:15 pm

    thank you very much Naveen,! helpful to all

  69. 69. Deepak KumarNo Gravatar Says:
    January 30th, 2010 at 9:05 am

    HI Naveen,

    This is good work. Can someone post a similar worksheet but accounting for variable rate of interest. So, now the worksheet should have one more column called “Rate of Interest” and could change from month to month.

    Thanks much!

  70. 70. PrasadNo Gravatar Says:
    February 2nd, 2010 at 1:30 pm

    Naveen,
    Gr8 job !!!

    however, can you come-up with EMI calculator for floating interest rate?
    the current EMI calculator provides EMI only for fixed rates.

    Thanks,
    Prasad

  71. 71. ArunNo Gravatar Says:
    February 8th, 2010 at 6:56 pm

    Naveen,

    Wonderful piece of work done here. I was searching for something like this before I start my work.

    I will add on Variable Interest rates, Multi Disbursement (for new houses) and once done will post it here with a link

    Thanks Again

  72. 72. AsweenNo Gravatar Says:
    February 9th, 2010 at 10:40 pm

    It’s a wonderful calculator. Helped me understand the EMI concept nicely.
    Thanks you very much Naveen

  73. 73. ShubhojitNo Gravatar Says:
    February 11th, 2010 at 10:05 am

    The calculator is useful but m looking for one which allows me to enter principal prepayment anywhere between the tenure and recalculates the balance with reduced tenure.

  74. 74. khalid shaikhNo Gravatar Says:
    February 12th, 2010 at 9:39 am

    if it can be down lode on mobile then its a best facility for us

  75. 75. KEDUSEHONo Gravatar Says:
    February 17th, 2010 at 5:45 pm

    THE EMI FORMULA IS PERFECT.

  76. 76. AniketNo Gravatar Says:
    February 21st, 2010 at 11:18 am

    Hey Naveen,

    Your EMI calculator is very useful.
    But this calculator is as per current bank calculations ?

  77. 77. JayaramNo Gravatar Says:
    February 22nd, 2010 at 4:45 pm

    Dear Naveen

    Excellent!

    Your EMI calculator is really helpful. Keep on providing if anything is new. This is really a social service! All the best for all of your future efforts. God bless you:)

    Regards.

  78. 78. sunny umaNo Gravatar Says:
    March 4th, 2010 at 11:01 am

    if one installment is paid at the time of disbursement then by this formula EMI get wrong on monthly reducing .

  79. 79. HansrajNo Gravatar Says:
    March 5th, 2010 at 5:53 pm

    Gr8 job, which you have done. But I was wondering that how to use this Excel file to find out the emi for banks like sbi who keep 8% interest rate for the first year and 9.5% or so for the remaining years.
    What i mean to say is, how to calculate the EMI for two different rate of interests where we know the duration of both the rates in months

  80. 80. mustufaNo Gravatar Says:
    March 6th, 2010 at 2:33 pm

    i am working releted to banks.i need to 3 type of emi caculater. how to caculet rate of intrest, how to caculet loan amt., how to caculet IRR,

  81. 81. TarsemNo Gravatar Says:
    March 10th, 2010 at 4:32 pm

    Sir, How to calculate in case of pre-closure loan – principal balance and interest saved pre-closure.

  82. 82. MuthurajNo Gravatar Says:
    March 11th, 2010 at 8:56 pm

    Thanx Naveen,its a very useful tool.

  83. 83. HituNo Gravatar Says:
    March 12th, 2010 at 2:51 pm

    But I was wondering that how to use this Excel file to find out the emi for banks like HDFC who are giving me the per lac emi for 3yrs, 4yrs and 5 yrs. Also i want to know abt sbi who keep 8% interest rate for the first year and 10.5% or so for the remaining years.

    same doubt where Hansraj quoted few days before.
    What i mean to say is, how to calculate the EMI for two different rate of interests where we know the duration of both the rates in months

  84. 84. ramNo Gravatar Says:
    March 12th, 2010 at 5:04 pm

    hello,

    I want to download the emi calculator, plz advise

  85. 85. NaveenNo Gravatar Says:
    March 12th, 2010 at 8:37 pm

    Haven’t you noticed the links at bottom to download the Excel file?

Leave a Reply