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

This article will provide 2 easy methods to make an EMI formula in Excel, with examples of its use.

What Is an EMI Formula?

EMI stands for Equated Monthly Installment. It refers to a fixed amount paid by a borrower every month over a fixed period on a loan. It includes both principal and interest. The basic mathematical formula for EMI calculation is:

Where,

P = Principal Amount.

R = Annual Interest Rate (%).

N = Installment Numbers (Months).

In the sample dataset below, we have both the Yearly and Monthly values for the loan amount, interest rate, and duration of a loan. We’ll use this dataset to calculate the EMI.

Method 1 – Using a 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 represents the Loan Amount or Principal Amount (P), D6 the Interest Rate, and D7 represents the Duration or Number of Installments (N).

We’ve used the monthly interest rate, so there was no need to divide the interest rate by 12.

• Press Enter to get the output.

Example – Calculating a Car Loan EMI

Let’s see a practical example of how to apply the manual method to find the EMI for a car loan. Suppose a person took a $50000 car loan at a 5% rate for 3 years. The monthly payment / EMI amount can be calculated by using the following formula:

• Press ENTER to return an EMI of $1498.54.

The above formula can be used for any kind of loan.

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

Method 2 – Using the PMT Function to Make an EMI Formula

Alternatively, we can use the PMT function.

Steps:

• Enter the following formula in Cell D9:

The interest rate (D6), duration or number of months (D7), and the principal amount (D5) are provided as the arguments for the PMT function.

• Press Enter to get the result.

We have the same output as using the manual formula. So, our calculation is correct.

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

Example – Determining a Car Loan EMI

As a practical example using the formula, we’ll use the same data from the previous example.

• Enter the following formula in Cell D10:

• Press ENTER to return the result.

For the purpose of demonstration, we’ve used an identical example in both methods. However, you can use this formula to calculate EMI for any other type of loan.

Things to Remember

• To avoid negative output from the PMT function, use a minus sign or the ABS function before it.
• Additional 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

<< Go Back to Calculate EMI in Excel | Excel for Finance | Learn Excel