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.
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
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-
P = Principal Amount.
R = Annual Interest Rate (%).
N = Installment Numbers (Months).
2 Ways to Make an EMI Formula in Excel (with Example)
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.
- Activate Cell D9 and insert the following formula into it-
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.
Example: 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-
- Press the ENTER button and you will get the EMI of $1498.54.
You can use the above formula for any kind of loan too.
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.
- Write the following formula in Cell D9–
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.
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: 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–
- 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.
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. Visit ExcelDemy to explore more.
- SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option
- How to Make Two Wheeler Loan EMI Calculator in Excel (2 Easy Ways)
- Reducing Balance EMI Calculator in Excel Sheet (Create with Easy Steps)
- Personal Loan EMI Calculator Excel Format (2 Suitable Examples)
- EMI Calculator with Prepayment Option in Excel Sheet (with Easy Steps)