Indzara

Loan Comparison and EMI Payment Calculator (Excel template)

EMI Payment Calculator Excel Template is a simple template to help one in decision making on loans.

There are two version of this template, one using the Indian Rupees as the currency and the other one without any currency formatting. Both versions function in the exact same way and hence the following information applies to both versions

EMI Payment Calculator Excel Template
EMI Payment Calculator Excel Template

DOWNLOAD

  • Excel Template (Generic):
  • Excel Template (India):


Purpose of the template

  • This template calculates
    • 1) EMI
    • 2) Total Interest paid during the duration of the loan
    • 3) Last Payment Month
    • 4) Number of Payments
  • It allows you to compare three different loan scenarios based on their EMI, Interest, Last Payment Month and Number of Payments.
    • For example, you can compare loans from three different banks if they have different interest rates or loan period.
  • You can also compare how much savings you can get if you pre-pay your loan. You can enter up to 12 pre-payments for each loan.
  • You can print the loan comparison and the detailed payment schedule (Amortization schedule).

How to use the template

Step 1: Enter information about loans.

You can enter up to 3 loans if you would like to compare. If you just want to calculate EMI for one loan, please enter in only one of the scenarios.

Enter information about loans. You can enter up to 3 loans if you would like to compare. If you just want to calculate EMI for one loan, please enter in only one of the scenarios.

EMI Payment Calculator Excel Template Loan Information Input
EMI Payment Calculator Excel Template – Loan Information Input

In this example we are comparing three scenarios:

  • Loan of 20 lakhs at 11% interest rate
  • Loan of 20 lakhs at 10% interest rate
  • Loan of 20 lakhs at 10% interest rate with pre-payments made during the course of the loan

Step 2: Enter details of pre-payments if you are planning to pre-pay.

Otherwise, please leave this empty. The image below shows the pre-payments entered in Scenario 3.

EMI Payment Calculator Excel - Template Pre-Payments Input
EMI Payment Calculator Excel – Template Pre-Payments Input

Step 3: See the Summary section

See the Summary section where you can see and compare the EMI, Total interest, Last loan payment month and number of loan payments. We can clearly see how Scenario 3, where you make pre-payments, leads to interest savings.

Loan Comparison Summary - EMI Payment Calculator Excel Template
Loan Comparison Summary – EMI Payment Calculator Excel Template
Interest EMI Comparison - EMI Payment Calculator Excel Template
Interest EMI Comparison – EMI Payment Calculator Excel Template

Step 4: See the Loan Payment and Amortization Schedule

See the Loan Payment and Amortization Schedule section to see the month by month details on interest paid, principal paid and remaining balance.

Loan payment Amortization Schedule - EMI Payment Calculator Excel Template
Loan payment Amortization Schedule – EMI Payment Calculator Excel Template

Assumptions/Settings

  • Assumes monthly loan payments.
  • Loan Period can be up to 30 years.
  • EMI (Equated Monthly Installment) calculation is for the end of the period. In the example above, loan begins on June 1, 2013. First payment is due on July 1, 2013 (end of first period).
  • All payment dates are on the first day of each month.
  • EMI is rounded up to the nearest paisa (2 digits).
  • Assuming no penalty for pre-payments.
  • Pre-payments (also called as extra payments) can happen any time during the loan tenure.
  • If pre-payments exceed the remaining balance, the extra amounts (in the pre-payments) will not be added to the payments.

I hope you find this helpful when you are trying to make the decision on your loans (home loans, property loans or auto loans). There are many EMI calculators online however this template allows you to compare multiple loan scenarios in one view and also allows pre-payments to be entered.

Loan Comparison Payment Page1 - EMI Payment Calculator Excel Template
Loan Comparison Payment Page1 – EMI Payment Calculator Excel Template
Loan Information Input - EMI Payment Calculator Excel Template
Loan Information Input – EMI Payment Calculator Excel Template
Summary - EMI Payment Calculator Excel Template
Summary – EMI Payment Calculator Excel Template
 EMI Payment Calculator Excel Template - Loan Payment Amortization Schedule
EMI Payment Calculator Excel Template – Loan Payment Amortization Schedule

This loan calculator excel indian rupees is an emi excel template that also supports nzre loan table. If you are looking for a pre emi calculation excel sheet download you can try this template.

16 Comments

  • Great Template. Very user friendly with detailed info and simple user interface.
    Can you also add an PRE-EMI and FULL-EMI difference option in this template. This will be very useful to make a wise decision on Under construction property.

    Reply
  • HI could you please help me to sort out if anyone already taken a loan want another loan, How to calculate old and new emi in excel???

    Reply
    • Hello

      Two loans cannot be merged. However, you can enter details in the different scenarios and add the amounts separately.

      Best wishes

      Reply
  • A deep understanding and knowledge of EMI calculators. You must have a expertise in providing loans and yes, you said it truly, it really helps customers decide the best loan for them. There are various EMI calculators online that shows accurate and error free results.

    Reply
  • Wowwwwww..awesome information. a very informative article.this is very use full for me.Car has become the inevitable part of everybody’s life.

    saverable.com

    Reply
  • This spreadsheet is good for the emi calculation and it is used to calculate using Indian currency and other currency.Calculating the emi using emi calculator is the easiest and best way to know the emi.It gives the accurate calculation and it helps to apply loan from other countries.

    Reply
  • Hi, this spread sheet is good but can you make it calculate pre-emi also. Suppose if I have taken a loan of 20 years and the construction will be complete after 4 years then what will be the pre-emi amount and the emi after I get possession. This would really help me evaluate between different builders, schemes and loans.

    Reply

Leave a Reply

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