How to Make EMI Formula in Excel (with Suitable Example)

Get FREE Advanced Excel Exercises with Solutions!

While you are making a loan then it’s a good decision to calculate the EMI amount first. Because it will give you the idea that how much time and rate will be feasible for you to pay the loan. This article will provide you with 2 easy methods to make an EMI formula in Excel with example.


What Is an EMI Formula?

EMI stands for Equated Monthly Installment. EMI is a fixed amount for a loan that is 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]

Where,

P = Principal Amount.

R = Annual Interest Rate (%).

N = Installment Numbers (Months).


First, get introduced to our dataset. It shows the loan amount, interest rate, and duration of a browser. Also, I have placed the interest rate and monthly installment numbers.


1. Using Manual Formula to Calculate EMI in Excel

In our first method, we’ll apply a manual formula using the basic mathematical equation to find EMI.

Steps:

  • Activate Cell D9 and insert the following formula into it-
=(D5*D6)*((1+D6)^D7)/((1+D6)^D7-1)

Have a look at the formula stated in the earlier section you can relate to that easily,

here D5 represents the Loan Amount or Principal Amount (P), D6 the Interest Rate, and D7 represents the Duration or Number of Installments (N).

The only change is that I’ve taken the monthly interest rate so did not need to divide it by 12.

  • Then just hit the Enter button to get the output.

Using Manual Formula to Calculate EMI in Excel


Example: Calculating Car Loan EMI

Now we’ll show a practical example of how to apply the manual to find the EMI for a car loan. Let, a person took a $50000 car loan at a 5% rate for 3 years. So the monthly payment or the EMI amount for him will be calculated by using the following formula-

=(D6*D7)*((1+D7)^D8)/((1+D7)^D8-1)
  • Press the ENTER button and you will get the EMI of $1498.54.

Using Manual Formula to Calculate EMI in Excel

You can use the above formula for any kind of loan too.

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


2. Using Excel PMT Function to Make an EMI Formula

In this section, we’ll use a function to make an EMI formula. That is the PMT function. The previous manual formula was a bit complex and lengthy but the PMT function is quite easy and fast to use. Let’s use it for our dataset.

Steps:

  • Write the following formula in Cell D9–
=-PMT(D6,D7,D5)

Provided the interest rate (D6), duration or number of months (D7), and the principal amount (D5) as per the sequence of the PMT function.

  • Finally, just press the Enter button to get the result.

And have a look, we have got the same output as we got using the manual formula. That means our calculation is right.

Using PMT Function to Make an EMI Formula in Excel

Note: the PMT function returns a negative output as it represents a due amount. To make it positive, I have placed a negative sign before the formula.


Example: Determining Car Loan EMI

Now, let’s see a practical example using the formula. For that, we’ll use the same data from the previous example.

  • Type the following formula in Cell D10–
=-PMT(D7,D8,D6)
  • Later, just hit the ENTER button to finish.

For the simplicity of the article, I’ve shown an identical example in both methods. Apart from this, you can use this formula to calculate EMI for any other type of loan.


Advantages of Calculating EMI in Excel

  • It will help you to choose the best option from different lenders while taking loans.
  • You will be able to compare lenders who are offering the best deal.
  • You will be able to choose the duration and amount according to your capability.
  • By calculating EMI in Excel, you can manage a better and more transparent transaction history.

Things to Remember

  • To avoid negative output from the PMT function, use minus or the ABS function before it.
  • The other charges like service charges and due payment charges are not included in the calculation.
  • Don’t forget to convert the annual rate to a monthly rate and the duration to months.

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


Conclusion

That’s it for the article. I’ve tried showing you the EMI formula in detail with example. I hope the above procedures will be good enough for you to understand the process of making an EMI formula in Excel with example. Feel free to ask any questions in the comment section and give me feedback.


<< Go Back to Calculate EMI in Excel | 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.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo