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

## Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

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

## 2 Ways to Calculate EMI in Excel

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.

**Similar Readings**

**SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option****How to Make Two Wheeler Loan EMI Calculator in Excel (2 Easy Ways)****Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option**

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

## 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, so these 0s are showing.

## 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. And please visit our website **ExcelDemy** to explore more.