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

**Table of Contents**Expand

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

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

- Now calculate the total duration in months. For that use the following formula in cell
**D7**.

`=C7*12`

- After that, press
**ENTER**.

- Now use the following formula in cell
**D9**to calculate**EMI**.

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

- Then press
**ENTER**.

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

- How to Calculate EMI in Excel
- How to Make EMI Formula in Excel
- How to Calculate EMI for Bike Loan in Excel

**<< Go Back toÂ Excel for FinanceÂ |Â Learn Excel**