EMI Calculator Excel


Download the Practice Workbook

Overview of the EMI Calculation

The Equated Monthly Installment, or EMI, is payment for a traditional set monthly payment loan, where you’re paying a portion of the principal and the interest each month. You can use the following equation to determine the EMI payment:

EMI = (P * r * (1 + r)^n) / ((1 + r)^n - 1)

EMI= Equated Monthly Installment

P = principal loan amount

r = monthly interest rate (annual interest rate divided by 12 and converted to decimals)

n = total number of monthly installments

How to Create a Loan EMI Calculator in Excel

Method 1 – Using the PMT Function to Create an EMI Calculator

We can see the Loan Amount (pv), Interest Rate (rate), and Number of EMIs (nper) in the following dataset.

Data for EMI calculation

  • Use the following formula in cell H4 to find the EMI Amount.

To change the annual interest rate to a monthly interest rate, we divide the interest rate by 12 in the formula. PMT returns a negative value by default, so we put a – in front to change it to a positive one.

Using the PMT function to make EMI Calculator Excel

Method 2 – Creating a Reducing Balance EMI Calculator

  • Select the cell C10 and enter the following formula:
  • Drag the fill handle down to find the EMI for all months.

Using the Mathematical formula to calculate EMI

  • Enter the value of your loan amount in cell F9.
  • Choose cell E10 and enter the following formula to determine the portion of the interest paid each month:

Calculating Interest for EMI Calculator

  • Enter the following formula in cell D10 to calculate the portion of the principal paid each month:

Calculating Principal for EMI Calculator

  • Select cell F10 and insert the following to determine the leftover principal value:

Calculating Remaining Balance for EMI Calculator

  • Select F10 and drag down the fill handle to AutoFill the column.

Calculating the Remaining Balance for the rest of the cells

  • Drag down the Fill Handle from E10.

Calculating the Interest for the rest of the cells

Calculating the Principal for the rest for EMI Calculator Excel

Method 3 – Creating an EMI Calculator with Prepayment

  • Enter the following formula to find the current Balance in cell F11:

Calculating the Net Balance after Prepayment

  • Choose cell C12 and enter the following formula:

Calculating the EMI with Prepayment Option

  • AutoFill the column.

Autofill in column C

  • Choose cell D12 and use the following formula:

Calculating the Interest using the IPMT function

  • Drag the fill handle to the remaining cells in column D.

Autofill in column D

  • Enter the following in E12:
  • Drag the fill handle down to copy the formula for the rest of the cells.

Calculating the Principal

  • Insert the following in F12:

Calculating the Balance

  • Drag the fill handle down.
  • You will get the month’s EMI, the interest paid, the principal paid, and the remaining principal in the table.

Autofill in column F to create EMI Calculator Excel

How to Create a Reverse EMI Calculator in Excel

  • Go to Data and select Forecast.
  • Choose What If Analysis and select Goal Seek from the drop-down menu.

Selecting the Goal Seek from Data tab

  • In the Goal Seek box, put the EMI amount in cell H4 into the Set Cell field. In the To value box, enter the target EMI value (such as 100). Select the cell $D$6 containing the Number of EMIs for the By changing cell option.
  • Click OK.

Choosing values in the Goal Seek window

  • The Number of EMIs changed to 22 for the fixed EMI Amount.

Final output on reverse EMI Calculator Excel

Things to Remember

Understanding EMI: Equated Monthly Installment, or EMI for short, is a set monthly payment made by a borrower to a lender on a particular date. EMIs are frequently used to pay back loans, including personal, auto, and home loans.
Input Data: You must collect the required input data before you can calculate the EMI in Excel. The sum of the loan, the interest rate, and the length of the loan (in months) are typically included. Depending on the loan terms, you might also need to take into account things like any down payment, processing costs, or prepayment options.
Loan Interest Rate: Make sure to enter the interest rate as the monthly rate rather than the annual rate when entering it. Divide the given annual interest rate by 12 to get the monthly interest rate.
Rounding the EMI: Use the ROUND function to make sure the EMI is rounded to the correct value. For instance, to round the calculated EMI in cell H4 to two decimal places, use the formula =ROUND(H4, 2).
Visualizing the EMI Schedule: To see the EMI payments over the loan term, you can create a repayment schedule. Calculate the principal and interest portions of each EMI payment using Excel’s functions. Make a table to show the schedule, which should include the due date, the principal, the interest, and the remaining loan balance.
Sensitivity Analysis: To perform a sensitivity analysis on the EMI calculation, use Excel’s data tables or the goal seek function. You can then see how different variables, like interest rates or loan amounts, affect the EMI amount. You can better understand the adaptability and affordability of various loan options with the aid of sensitivity analysis.

Frequently Asked Questions (FAQs)

What details do I need to use Excel’s EMI calculator?

To use the EMI calculator in Excel, you typically need to enter the loan amount, interest rate, and loan tenure. The calculator then calculates the monthly installment based on those specifications.

Is it possible to modify Excel’s EMI calculator?

You can modify the EMI calculator in Excel to meet your unique needs. Depending on your requirements and Excel skill level, you can change the formulas, format them, or add new features.

What is computed by the Excel’s EMI calculator?

The Equated Monthly Installment (EMI) calculator in Excel determines the amount of your fixed monthly loan payment. This is known as the Equated Monthly Installment (EMI). Additionally, a thorough breakdown of each payment’s principal and interest components is given.

EMI Calculator in Excel: Knowledge Hub

<< Go Back to Finance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF