How to Calculate EMI in Excel (2 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


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).

How to Calculate EMI in Excel: 2 Effective Ways

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.

Read More: How to Make EMI Formula in Excel


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.

Read More: How to Calculate EMI for Bike Loan in Excel


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, these 0s are showing.


Download Practice Workbook

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


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.


Calculate EMI in Excel: Knowledge Hub


<< Go Back to Excel for Finance | Learn Excel

 

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo