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.

very nice

Thank you

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

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 :

http://www.naveen.info/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