EMI Calculator Excel

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.

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:

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.

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:

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

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

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

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

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

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:

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:

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

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