In this Excel blog post, we’ll discuss the topic of the EMI (Equated Monthly Installment) calculator in Excel and show how to build several different types of EMI calculators using Excel, such as a Reducing Balance EMI calculator, one that includes a prepayment option, and even one that calculates the reverse of the loan’s monthly payments. An EMI calculator can be very helpful when managing personal finances or analyzing loan repayment schedules. After reading this Excel blog post, you can create your own EMI calculator in Excel.

**Table of Contents**Expand

**Download Practice Workbook**

**Overview of EMI Calculation**

The Equated Monthly Installment, or EMI, is a set monthly payment made by borrowers to lenders to recover their loans. It includes both the loan’s principal balance and accumulated interest. EMIs, which are calculated based on the principal amount, interest rate, and loan tenure, enable borrowers to effectively plan their finances and create budgets. Over the loan term, EMIs typically gradually shift from higher initial interest payments to higher principal repayments. You can use the following equation to determine EMI:

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

Here,

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 our
**Loan Amount (pv)**,**Interest Rate (rate)**, and**Number of EMIs (nper)**in the following image. We can determine the monthly Loan Amount using this information.

- First, type the following formula in cell
**H4**to find the EMI Amount.

`=-PMT(D5/12,D6,D4,0,0)`

This formula makes use of **the PMT function**, which determines the **EMI **amount automatically by sequentially considering the interest rate, the number of EMIs, and the loan amount. To change the annual interest rate to a monthly interest rate, we divide the interest rate by 12 in the formula. To maintain a positive value, I also used the negative sign.

### 📌 Method 2: Creating a Reducing Balance EMI Calculator

- Select cell
**C10**first, then enter the following equation:

`=($D$4*$D$5/12)*((1+$D$5/12)^$D$6)/((1+$D$5/12)^$D$6-1)`

- Now drag the fill handle down to find the EMI for the rest of the month. All the EMI values are the same as expected.

- We’ll now determine the remaining principal sums. Enter the value of your loan amount in cell
**F9**. - The interest rate for each installment will then be determined.
- Choose cell
**E10**and enter the following formula:

`=$D$5/12*F9`

- The actual Principal payment in the EMI amount will then be determined. Enter the following formula in cell
**D10**to achieve this:

`=C10-E10`

- Select cell
**F10**as a result. Enter the equation here to find out the remaining value that will be a cover-up by paying EMI:

`=F9-D10`

- After that, fill the series using
**AutoFill**by dragging down the fill handle. - First, the values will be the same.
- But we display accurate results in the image below.

- Drag the Fill Handle tool down to obtain the Interests similarly.

- Therefore, utilize
**AutoFill**to return the remaining**Principal**amounts.

### 📌 Method 3: Creating an EMI Calculator with Prepayment

- First, calculate the current balance after deducting the prepayment or downpayment value from the actual loan.
- Enter the following formula to find the current Balance in cell
**F11**:

`=D4-D8`

- First, choose cell
**C12**and enter the following formula there using**the PMT function**.

`=PMT(D$5/D$7,$D$6*$D$7,F$11)`

Here the interest rate of the loan is expressed as the **rate**, the **nper **is the total number of payments per loan, the **pv **is the present value, i.e., the total value of all the loan payments at present, [fv] is the future value, i.e., the cash balance one wants to have after the last payment is done, and [type] specifies when the payment is due.

- The remaining cells in
**column C**should now be filled with**the PMT function**. You will be able to calculate the loan’s monthly EMI after completing the above steps, as shown in the image below.

- Choose cell
**D12**, then type the formula with**the IPMT function**in the Formula Bar:

`=IPMT(D$5/D$7,B12,$D$6*$D$7,F$11)`

In this equation, **E$4** stands for the annual percentage rate, **E$6** for the number of payments made annually, **B12 **for the number of months, **E$5** for the number of years, and **E$7** for the principal. A cell’s absolute reference is denoted by the dollar ($) sign.

- Drag the fill handle to the remaining cells in
**column D**as a result. - You will be able to calculate the interest on the EMI calculation with prepayment per month after completing the above process, as shown in the image below.

- Write the following formula in the Formula Bar after first selecting cell
**E12**to use for the mathematical subtraction formula. Enter the following equation:

` =C12-D12`

Here, **C12 **is the loan’s EMI, and **D12 **is the initial month’s interest.

- Drag the fill handle down to copy the formula for the rest of the cells.

- To apply the mathematical summation formula, first choose cell
**F12**. Put the following formula in the Formula Bar as a result. The equation is

`=F11+E12`

Here **F11 **represents the Initial **Balance **after prepayment, and **E12 **represents the amount of **Principal **in the EMI in the first month.

- Now drag the fill handle down. Now the EMI calculator using the prepayment option is available in Excel.

## How to Create Reverse EMI Calculator in Excel

- The first step is to navigate to
**Data**>>**Forecast**>>**What If Analysis**>> select**Goal Seek**from the drop-down menu.

- Second, we’ll change the
**EMI Amount**in the**Goal Seek**box to set the goal and change the value of the**Number of EMIs (nper)**. The**EMI amount**in cell**H4**will therefore be chosen in the**Set Cell**field. In the**To value**box, we entered a target EMI value of**100.**Select the cell**$D$6**containing the**Number of EMIs**and hit Enter for the**By changing cell**option.

- In the final image, the
**Number of EMIs**is changed to**22**for the**EMI Amount**. As expected, after reducing the**EMI Amount**the number of EMIs increased.

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

**1. What details do I need to enter Excel’s EMI calculator?**

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

**2. Is it possible to modify Excel’s EMI calculator?**

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

**3. What is computed by Excel’s EMI calculator?**

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

## Conclusion

To calculate Equated Monthly Installments (EMIs) for loans or mortgages, the Excel blog post “EMI Calculator Excel” provides a downloadable and adaptable tool. It gives precise results while letting users change the template to suit their requirements. For better loan management, the calculator manages a variety of loan types and generates amortization schedules. Overall, it is a helpful tool for Excel financial planning that simplifies EMI calculations.

**<< Go Back to Finance Template | Excel Templates**