Lesson#198: How to make a Loan EMI calculator in Excel

An EMI (Equated Monthly Installment) calculator is a useful tool for calculating the monthly payments on a loan, and Excel is a great platform to create one. Here is a step-by-step guide on how to make an EMI calculator in Excel:

Step 1:

Open a new Excel spreadsheet To start, open a new Excel spreadsheet and create the following columns: “Loan Amount”, “Interest Rate”, “Loan Term (in months)”, “EMI”.

Step 2:

Enter the loan details In the “Loan Amount” column, enter the total amount of the loan. In the “Interest Rate” column, enter the annual interest rate of the loan. In the “Loan Term (in months)” column, enter the duration of the loan in months.

Step 3:

Use the PMT function In the “EMI” column, enter the formula “=PMT(B2/12,C2,-A2)” (without quotation marks). This formula uses the PMT function to calculate the EMI. The formula has three arguments: the interest rate per month, the number of payments, and the loan amount. The interest rate per month is calculated by dividing the annual interest rate by 12. The number of payments is the loan term in months, and the loan amount is the total loan amount.

Step 4:

Apply the formula to the rest of the cells. Once you have entered the formula in the first cell, you can easily apply it to the rest of the cells by dragging the fill handle down to the rest of the rows.

Step 5:

Format the EMI cells To make the EMI more readable. You can format the cells to show the amount as currency. To do this, select the cells with the EMI, right-click, and select Format Cells. In the Number tab, select Currency and set the Decimal places to 2.

Step 6:

Add additional columns You can also add additional columns to show the total interest and the total amount to be paid over the loan term. To calculate the total interest, use the formula “=C2B2A2/100″, and to calculate the total amount to be paid, use the formula “=A2+D2”.

Note:

The above formula calculates the EMI for a fixed interest rate for the entire duration of the loan. If you want to incorporate reducing the balance rate, you need to use different formulas and can consider using a Macro.

In conclusion, creating an EMI calculator in Excel is a straightforward process. This can save you time and effort when working with loan information. By using the PMT function and a few basic Excel functions, you can easily calculate the EMI of a loan and other details such as total interest and the total amount to be paid.

You can use this calculator as Home Loan Calculator, Personal Loan Calculator Car Loan Calculator, etc.

Here is an EMI calculator for you. Download from here.

Download