How to Calculate EMI in Excel?

With these simple steps, we are going to learn how to calculate the EMI. The EMI is the Equated Monthly Installment. It is easy to calculate the EMI in Excel. It can be used to calculate the EMI of your mortgage, car loan, or any other loan.

It is important to calculate the EMI to be able to manage your household budget. The lower the monthly installment, the more money you have for other expenses.

How to calculate EMI?

Layout the information you already know, without EMI calculation.

emi data set

Click on the column that is beside the monthly installment and type in =PMT(rate,nper,pv,[fv],[type])

emi calculator

=PMT(E8/E7,E7*E6,-E5) is the formula here.

Note:

  • The rate is interest divided by the payment per year,
  • nper is loan terms multiplied by the count of payments,
  • pv was the loan amount with a minus symbol in front. The minus symbol means that this is the money you own.

You can download a free EMI calculator template here.

emi calculated

This is the result. Your monthly installment will be $12,567.41. This is the calculated EMI for $1,5m for 25 years with an annual interest rate of of 8.98%.

How to calculate the total loan cost and total interest based on EMI?

But what is the total cost of such a loan? Let’s calculate the total loan costs and total interest in such a situation.

emi total cost
The total cost is EMI * years * months. The formula is =E9*E6*E5 in my example.
Total interest is just the total cost-loan amount (=E11-E5).

See also  How to create blood alcohol calculator in Excel

The total cost of such a loan would be over $3,77m and the total interest over $2,27m.

How to calculate the loan amount based on a given EMI?

This is how to calculate EMI with Excel. What if you know your target EMI and would like to know the loan amount you will be able to borrow?

Go to the Data Tab on the Ribbon and click on What-If Analysis and choose Goal Seek.

emi goal seek

A new dialog window appears. Let’s assume you can afford a $15,000 monthly installment. Goal Seek parameters will be:

  • Set cell: the cell containing your EMI (E9 in this case).
  • To value: we agreed on a value of $15,000.
  • By changing cell: the cell with the loan amount (E5 here).

emi checking loan amount

A new loan amount is calculated.

emi total loan calculated

You can borrow $1,790,345.28 under the given loan conditions and assume a monthly payment of $15,000.