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

**Read More: **Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option

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

**Read More: **SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option

**Download the Practice Workbook**

## Related Articles

- Personal Loan EMI Calculator Excel Format
- Home Loan EMI Calculator with Reducing Balance in Excel
- Reducing Balance EMI Calculator in Excel Sheet
- How to Create Reverse EMI Calculator in Excel

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