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:

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

Prepare the arguments for the PMT function.

• Calculate the monthly interest rate using the following formula in D6.

• Press ENTER.

Calculate the duration in months.

• Enter the following formula in D7.

• Press ENTER.

Calculate the EMI using the PMT function.

• Enter the following formula in D9.

• Press ENTER.

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.

Here,

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

• Press ENTER.

• Calculate the total duration in months. Use the following formula in D7.

• Press ENTER.

• Use the following formula in D9 to calculate the EMI.

• Press ENTER.

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 with formulas. In C5, C6, and C7, enter:.

• Loan Amount
• Interest Rate
• Duration

EMI will be displayed in D9.

Download Practice Workbook

Download the Excel file to practice.

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