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

## How to Calculate EMI with Prepayment Option in Excel Sheet: Easy Steps

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!

- First, select cell
**C11**and write down the**PMT**function in that cell. The**PMT**function is,

`=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.

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

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

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

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

**Download Practice Workbook**

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

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

## Related Articles

- Personal Loan EMI Calculator Excel Format
- Home Loan EMI Calculator with Reducing Balance in Excel
- How to Make Two Wheeler Loan EMI Calculator 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**