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.”
- 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”.
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!
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
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?
Please can you call me for website creation
Sent a mail to your e-mail ID.
Great Naveen, Thanks a lot bro
very nice
Thank you
HELLO NAVEEN COULD YOU PLEASE SEND ME PERSONAL LOAN EMI CALCULATOR IN EXCEL FORMAT.
Personal loan
very nice mr naveen great job you are doing
this sheet is very help full to each and every person
thank you sir
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.
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
I want irregular emi payment calculetor.
Supose if I forgot to pay emi for a month then how to calculate interest?
Good support. wishes to continue
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.
naveen,
this software is awesome!!
I WAS DOWNLOADING TALLY——BUT YOUR SOFTWARE SAVED ME FROM DOWNLOADING!!
GREAT!!GREAT!!GREAT!!GREAT!!GREAT!!GREAT!!GREAT!!
Thanks for the appreciation.
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
Hi
I like your information which you share with us , I also read this similar tutorial from another platform I think from this post user’s can more details about :
https://blog.naveen.in/how-to-calculate-emi-download-excel-emi-calculator/291/
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
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
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.
Hi Naveen,
you are doing great job. but can i have the password to unprotected the sheets?
Dear Naveen.
It is very useful. would appreciate, if you can share the password to unprotected the sheets.
I protected it to defend plagiarism with random password. I can’t recall them…sorry!
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 ↓
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 🙂
mera lone kitna Kata aur kitna balence hai bataya jaye he net per dikhaya jaye
Hi Naveen, sorry for the typo mistake the i referred wrongly for the Rate of Interest column (I mean M6), sorry for inconvenience
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
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.
This is a great excel but i want to change my EMI adhoc schedule so can you share the password?
Dear Naveen
Pls send the EMI calculaer in excel format to my mail ID.
Your’s Lovingly
M.Karthick
Links are there at the bottom of post to download.
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
Weekly EMI DAte Calc formula for EXCEL
for MICRO FINACE RECOVERY WEEKLY BASE
HI
NAVEEN
GREAT CALCULATION USEFUL TO ALL
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.
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?
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
Hi Naveen,
I need the calculator with floating interest rate option. Please send me one
smita
Thank You
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
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
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
Plz share with me need this very badly
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.
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
It was a random password to protect the spreadsheet from plagiarism. I can’t recall it. Sorry!
Hi Naveen,
Do you have excel sheet for SBI Maxgain account calculation on daily basis ??
If yes, can you share it?
Thanks,
Anubhav
Do you such excel sheet. Then please share with me.
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