How to Calculate EMI in Excel (2 Effective Ways)

Equated Monthly Installment, also known as EMI, is a fixed payment that a borrower is compelled to pay a lender each month. To calculate the EMI, you can use the EMI calculator on the lender’s website or a financial calculator. However, it is also possible to calculate the EMI using Microsoft Excel. In this article, I will show you 2 effective ways to calculate EMI in Excel with ease. So, without further discussion, let’s dive straight into it.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


What Is EMI?

Equated Monthly Installment, or EMI, is a fixed amount that a borrower must pay a lender on a monthly basis. The principal amount and the interest amount are both included in the payment. The initial amount borrowed is known as the principal, and the cost of borrowing is known as the interest. The EMI is computed to allow for full repayment of the loan during the loan period.


What Is an EMI Formula?

EMI is a fixed amount against a loan paid by a borrower every month over a fixed period. It includes both principal and interest. The basic mathematical formula for EMI calculation is:

EMI = (P X R/12) X [(1+R/12) ^N] / [(1+R/12) ^N-1]

Here,

  • P = Principal Amount.
  • R = Annual Interest Rate (%).
  • N = Installment Numbers (Months).

2 Ways to Calculate EMI in Excel

Suppose the following information is given to calculate EMI.

  • Loan Amount of $50,000.
  • Yearly Interest Rate of 8%.
  • Total Duration 2 years.

1. Using Excel PMT Function to Calculate EMI

You can calculate EMI in Excel quite easily using the PMT function. But before using the function, you have to prepare a few arguments for the PMT function.

  • First, calculate the Monthly Interest Rate (usually interest rate is given year-wise) using the following formula in cell D6.
=C6/12
  • Then press ENTER.

After that, you need to calculate the duration in months.

  • For that, insert the following formula in cell D7.
=C7*12
  • Then press ENTER.

Now it’s time to calculate EMI using the PMT function.

  • To calculate EMI, insert the following formula in cell D9.
=-PMT(D6,D7,D5)
  • Then press the ENTER

Using Excel PMT Function to Calculate EMI

Formula Breakdown

  • D5 is the total Loan Amount.
  • D6 is the Monthly Interest Rate.
  • D7 is the Duration.

The output of the PMT function is a negative number. But EMI is a positive number. To make the output positive, a minus (-) sign is used for the PMT function.


Similar Readings


2. Manually Calculate EMI

If you know the formula to calculate EMI, you can calculate it without using the EMI function.

The formula to calculate EMI is:

EMI = (P X R/12) X [(1+R/12) ^N] / [(1+R/12) ^N-1]

Here,

  • P = Principal Amount.
  • R = Annual Interest Rate (%).
  • N = Installment Numbers (Months).

Now before calculating EMI, you have to convert the annual interest rate into the monthly interest rate.

To do that,

  • Use the following formula in cell D6.
=C6/12
  • Then press ENTER.

Calculate EMI in Excel Manually

  • Now calculate the total duration in months. For that use the following formula in cell D7.
=C7*12
  • After that, press ENTER.

Calculate EMI in Excel Manually

  • Now use the following formula in cell D9 to calculate EMI.
=(D5*D6)*((1+D6)^D7)/((1+D6)^D7-1)
  • Then press ENTER.

Calculate EMI in Excel Manually

Formula Breakdown

  • D5 is the total Loan Amount.
  • D6 is the Monthly Interest Rate.
  • D7 is the Duration.

EMI Calculator

In the last worksheet of the attached Excel file, you will find an EMI calculator. In the calculator, all the necessary formulas are already there. You just need to insert the following three pieces of information in cells C5, C6, and C7 respectively.

  • Loan Amount
  • Interest Rate
  • Duration

After providing this information, you will get the EMI amount in cell D9.

Since currently there is no value, so these 0s are showing.


Conclusion

To summarize, I have discussed 2 ways to calculate EMI in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo