EMI Calculator with Prepayment Option in Excel Sheet (with Easy Steps)

We will calculate the EMI with the prepayment option from our dataset using Excel’s PMT, and IPMT financial formulas. PMT stands for Payment, and IPMT is used to get the interest of payment. We will apply these financial functions to calculate the EMI calculator Excel sheet with a prepayment option.

Step 1 – Use the PMT Function to Calculate EMI

The syntax of the PMT function is:

=PMT(rate, nper, pv, [fv],[type])

Where the rate is the interest rate of the loan, 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 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.

Select cell C11 and use the following PMT function in that cell.

=PMT(E$4/E$6,E$5*E$6,E$7)

E$4 is the Annual Interest Rate, E$6 is the number of payments per year, E$5 is the number of years, and E$7 is the original price of the car.

Press Enter.

AutoFill the PMT function to the rest of the cells in column C.

Step 2 – Apply the IPMT Function to Calculate the Interest

The syntax of the IPMT function is:

=IPMT(rate, per, nper, pv, [fv],[type])

Where the rate is the interest rate per period, per is a specific period; must be between 1 and nper, nper is the total number of payment periods in a year, the pv is the current value of a loan or investment, [fv] is the nper payments future worth, [type] is the payments behavior.

Select cell D11 and use the following IPMT function in the Formula Bar.

=IPMT(E$4/E$6,B11,E$5*E$6,E$7)

E$4 is the Annual Interest Rate, E$6 is the number of payments per year, B11 is the number of months, E$5 is the number of years, and E$7 is the original price of the car. We use the dollar ($) sign for the absolute reference of a cell.