# How to Calculate the EMI in Excel – 2 Methods

## What Is the EMI?

Equated Monthly Installment, or EMI, is a fixed amount that a borrower must pay a lender on a monthly basis.

## What Is an EMI Formula?

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

The following information is given to calculate the EMI.

• Loan Amount of \$50,000.
• Yearly Interest Rate of 8%.
• Total Duration 2 years.

### Method 1 – Using the Excel PMT Function to Calculate the EMI

• Calculate the monthly interest rate using the following formula in D6.
`=C6/12`
• Press ENTER.

Calculate the duration in months.

• Enter the following formula in D7.
`=C7*12`
• Press ENTER.

Calculate the EMI using the PMT function.

• Enter the following formula in D9.
`=-PMT(D6,D7,D5)`
• Press ENTER.

Formula Breakdown

• D5 is the total Loan Amount.
• D6 is the Monthly Interest Rate.
• D7 is the Duration.

The output is a negative number. But the EMI is a positive number. To make the output positive, a minus (-) sign is used in the PMT function.

Read More: How to Make EMI Formula in Excel

### Method 2 – Calculate the EMI Manually

Use this formula.

`EMI = (P X R/12) X [(1+R/12) ^N] / [(1+R/12) ^N-1]`
• P = Principal Amount.
• R = Annual Interest Rate (%).
• N = Installment Numbers (Months).

Convert the annual interest rate into the monthly interest rate:

• Use the following formula in D6.
`=C6/12`
• Press ENTER.

• Calculate the total duration in months. Use the following formula in D7.
`=C7*12`
• Press ENTER.

• Use the following formula in D9 to calculate the EMI.
`=(D5*D6)*((1+D6)^D7)/((1+D6)^D7-1)`
• 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 with formulas. In C5, C6, and C7, enter:.

• Loan Amount
• Interest Rate
• Duration

EMI will be displayed in D9.

## Calculate EMI in Excel: Knowledge Hub

<< Go Back to Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF