How to Calculate EMI in Excel: All Methods to Calculate EMI

EMI or Equated Monthly Installment is the amount of money you pay each month against the loan you have taken at the decided rate of interest for a certain period of time. It mainly consists of a certain portion of the principal amount plus the rate of interest.

Mathematically, it can be calculated as;

EMI=Pr(1+r)n(1+r)n-1

Where:

• P = Principal loan amount
• R = Monthly interest rate (annual rate ÷ 12)
• N = Loan tenure in months

How to Calculate EMI in Excel?

By using this simple method and formula explained below, you can calculate EMI in Excel.

1. First, create a table format as shown below, 

Here you can see the first table presenting the Principal Loan Amount (P), Rate of Interest (I), and tenure as Number of EMIs (t). In the second table, you can see the EMI amount, total interest, and total payable. It is where we will calculate EMIs using the formula.

2. Locate the cell next to the EMI amount, in our case H4, and use the following formula to calculate the EMI amount.

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

In this formula, we are dividing interest by 12 to convert it into a monthly interest rate. And PMT by default gives a negative answer, that’s why I am using a negative sign (-).

3. Next, to calculate total interest and total payable, use the formula =H6-D4 and =H4-D6 in cells H5 and H6, respectively.

How to Calculate Loan EMI in Excel for Reducing Balance

The reducing balance EMI calculator tells how much the portion of the principal plus interest is paid each month and what the remaining balance is over time. It can also be called EMI Amortization on Excel, which helps in scheduling the expenses by keeping monthly EMIs in check.

1. Create a Reducing Balance Table as shown in the figure below;

2. In the EMI column for the first month, Cell C10, enter the following formula to find EMI:

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

3. Autofill for the remaining month by dragging it downward:

4. In the first row of the table, under the remaining, enter your loan amount in F9, and to determine the portion of interest paid each month, enter the following formula in the first cell of the monthly interest E10:

=$D$5/12*F9

5. To calculate the portion of principal paid each month, locate the first cell of Principal for the first month, which is D10 here, and enter the formula =C10-E10.

6. Select the cell for the remaining balance for the first month (F10 here), and enter =F9-D10 to calculate the remaining principal value.

7. Autofill in all columns by dragging  the hand:

How to Calculate Prepayment EMIs in Excel?

Prepayments are made to reduce the burden on EMIs, and it has a significant effect on the Principal amount, interest rate, and monthly EMIs while calculating. How to calculate prepayment EMIs on Excel is as follows:

1. Create a Prepayment EMIs Table as shown in the figure below;

2. Enter the remaining balance after prepayment in the first cell under balance (F11 here) using the formula

=D4-D8

2. Calculate EMI for the first month in C12 using the PMT formula:

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

Autofill by dragging below,

3. Now, to calculate interest in cell D12(Interest on first month), use the following formula:

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

Drag to autofill for the remaining months:

4. Now, calculating principal, in the cell for the principal of the first month, as E12 in our table, add the following formula:

=C12-D12

Drag to fill for the rest of the months.

5. And last one to calculate the outstanding balance, in the balance Cell (F12), add this formula:=F11+E12

Similarly, drag to autofill

That’s how you calculate the Prepayment EMIs and their balance in Excel.

NOTE: The formulae written here are according to the relevant information in to respective cells. They may vary depending on where you put the required information or in which cell you put your information in the Excel sheet.

Important Tips To Calculate EMI in Excel

There are some recommendations that will ensure your EMI calculations are fast and accurate. These are the most appropriate methods to form tables, formulas, as well as to prevent common mistakes:

• Enter Material Inputs with Absolute References: When entering such formulae as EMI, enter the dollar signs, that is, $, into the principal, rate, and tenure columns to hold the values. This will prevent errors in filling up columns automatically, as there will be no movement of the references.
• Divide the monthly rate by the annual interest rate: To determine the monthly interest rate, it is always important to divide the annual interest rate by 12. If you forget this, your values of EMI will be incorrect since loans are normally calculated on a monthly basis.
• Negative Output of Handle PMT Function: The PMT function default is to give a negative value, which is an outflow of money. You can use the minus sign with the formula to solve the issue.
• Ensure that the table structures are made simple: Make the table simple and easy to understand. Always put the given information, such as Principal, Interest, and tenure in a separate or first table and then the calculations in other tables, such as EMI, balance, and so on.
• Using Autofill Formulas Well: There is no need to put formulae separately in each cell. Just put the formula in the first cell of the column and pull the fill handle to autofill the other cells. It is a saving and easier method.
• In case of Reducing Balance: Enter the entire principal in the first cell of the remaining balance. Then, calculate the monthly interest on the current balance, subtract it from the EMI in order to pay the principal, and update the balance accordingly. 
• Take Advantage of Prepayments: First, subtract the prepayments from the principal. Check whether you are paying less interest by using the adjusted principal in the PMT or IPMT functions.
• Check with real data: Once you create the table and put in the given data, make sure to double-check it to prevent any kind of mistake.
• Consider rounding and formatting: To make the formatting easier to understand, format cells such as currency, and work with ROUND functions in order to avoid difficulties with fractions in money calculations.

FAQs: EMI Calculation on Excel