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:

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

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*D6)*((1+D6)^D7)/((1+D6)^D7-1)`

**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:

`=(D6*D7)*((1+D7)^D8)/((1+D7)^D8-1)`

- 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**:

`=-PMT(D6,D7,D5)`

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**:

`=-PMT(D7,D8,D6)`

- 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**