# 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.

• Hit Enter.

• AutoFill the IPMT function to the rest of the cells in column D.

### Step 3 – Use a Subtraction Formula to Calculate the Principal

• Select cell E11 and insert the following:
`=C11-D11`

C11 is the EMI of the loan, and D11 is the interest of the first month.

• Press Enter.

• AutoFill to the rest of the column.

### Step 4 – Apply a Mathematical Formula to Calculate the Due Balance

• Select cell F11 and insert the following:
`=F10+E11`

F10 is the price of the car after giving the prepayment, and E11 is the total payment after the first month.

• Press Enter.

• AutoFill to the rest of the column.

### Step 5 – Calculate the Progress Towards Paying Off the Loan

• Select cell G11 and insert the following:
`=(F\$10-F11)/F\$10`

• Hit Enter.

• AutoFill the formula to the rest of the column.

## Related Articles

<< Go Back to EMI Calculator |Â Finance Template |Â Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF