When you’re dealing with your personal finance, you have to have clear knowledge about it. Not knowing general financial concepts can lead you to financial disasters. There are two types of financial terms to pay the **loan**. The first one is** Flat Rate Interest **and another is **Reducing the Balance Rate**. In both cases, you need to **calculate the EMI**. We can easily calculate the **EMI with the prepayment option** in **Excel**. This will save you a lot of time and energy. Today, in this article, we’ll learn **five **quick and suitable steps how to EMI calculator Excel sheet with prepayment option effectively with appropriate illustrations.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 5 Quick Steps to Calculate EMI with Prepayment Option in Excel Sheet

Let’s assume we have an **Excel **large worksheet that contains the information about the **EMI **calculation with the prepayment option. 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 prepayment option. We will do those **five **easy and quick steps, which are also time-saving. Here’s an overview of the dataset for today’s task.

Let’s follow the instructions below to learn!

### Step 1: Use PMT Function to Calculate EMI

First of all, we will calculate the **EMI **by using the PMT financial function. One can pay one’s payment every week, month, or year by using this function. The syntax of the 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.

Let’s follow the instructions below to calculate the **EMI **using the **PMT** function!

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

- Where
**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**. We use the**dollar ($) sign**for the absolute reference of a cell.

- Hence, simply press
**ENTER**on your keyboard. As a result, you will get the payment**($12,178.38)**as the output of**the PMT function**.

- Now,
**AutoFill**the PMT function to the rest of the cells in column**C**. - After completing the above process, you will be able to calculate the
**EMI**of the loan per month which has been given in the below screenshot.

**Read More:** **How to Make EMI Formula in Excel (with Suitable Example)**

### Step 2: Apply IPMT Function to Calculate Interest

After calculating the **EMI**, now, we will calculate the interest of payment by applying the IPMT function. The syntax of the 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.

Let’s follow the instructions below to calculate the **interest **of payment by using the **IPMT **function!

- First of all, select cell
**D11**and type the**IPMT**function in the**Formula Bar**. The**IPMT**function in the**Formula Bar**is,

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

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

- Further, simply press
**ENTER**on your keyboard. As a result, you will get the interest of payment**($933.33)**as the output of**the IPMT function.**

- Hence,
**AutoFill**the IPMT function to the rest of the cells in column**D**. - After completing the above process, you will be able to calculate the
**interest of payment**of the**EMI**calculation with prepayment per month which has been given in the below screenshot.

### Step 3: Use Subtraction Formula to Calculate Principal

In this portion, we will use the Mathematical subtraction formula to calculate the principal of the loan. This is an easy and time-saving task also. From our dataset, we can easily calculate the principal of the loan by subtracting the Interest from **EMI**. Follow the instructions below to calculate the balance using the **mathematical **function!

- First of all, select cell
**E11**to apply the mathematical subtraction formula**,**and write down the following formula in the**Formula Bar**. The formula is,

`=C11-D11`

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

- After that, press
**ENTER**on your keyboard, and you will get the**principal**of the first month. The**principal**for the first month is**($11,245.05)**.

- While performing the above process, you will be able to calculate the
**principal**per month which has been given in the below screenshot.

### Step 4: Apply Mathematical Formula to Calculate Due Balance

In this step, we will apply the Mathematical formula to calculate the due balance of the loan. This is an easy and time-saving task also. From our dataset, we can easily calculate the due balance using the mathematical summation formula. Follow the instructions below to calculate the balance using the **mathematical **function!

- First of all, select cell
**F11**to apply the mathematical summation formula. Hence, write down the following formula in the**Formula Bar**. The formula is,

`=F10+E11`

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

- After that, press
**ENTER**on your keyboard, and you will get the balance after the first month. The balance becomes**$128,754.95**after the**first month**.

- While performing the above process, you will be able to calculate the
**EMI with a prepayment option**per month. After the 12th month, you will be able to pay the total loan which has been given in the below screenshot.

**Read More:** **Personal Loan EMI Calculator Excel Format (2 Suitable Examples)**

### Step 5: Calculation of Paid Loan in Percentage

This is the final step to calculating the paid loan for each month in percentage in **Excel**. After calculating the **EMI **per month, the **interest **of payment per month, the **principal **of payment per month, and the paid balance per month. Now, we will calculate the paid loan in percentage by using those values. Follow the instructions below to calculate the paid loan in percentage using the **mathematical **function!

- First of all, select cell
**G11**to apply the mathematical summation formula. After that**,**write down the following formula in the**Formula Bar**. The formula is,

`=(F$10-F11)/F$10`

- Hence, press
**ENTER**on your keyboard. As a result, you will get the percentage of your paid balance after the first month. The percentage of your paid balance after the first month is**8.****03%**.

- While performing the above process, you will be able to calculate the percentage of your paid balance for each month. After the 12th month, you will be able to pay the
**100%**loan which has been given in the below screenshot.

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

## Things to Remember

👉 Each month, the loan debt is decreased as a result of your **payments**. The accumulated interest must decrease as the loan balance decreases.

👉** #DIV/0 **error occurs when the **denominator **is **0 **or the reference of the cell is not **valid**.

👉 The **#NUM!** error occurs when the **per** argument is less than 0 or is greater than the **nper** argument value.

## Conclusion

I hope all of the suitable steps mentioned above to **calculate the EMI calculator Excel sheet with prepayment option **will now provoke you to apply them in your **Excel **spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.