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!

Related Posts:

  1. Savings account interest rate – Now calculated on a daily basis After making usage of any bank ATM free, RBI now...