In today’s world, loans are an inseparable part of our lives. Sometimes, we need to use the help of loans and instalments to buy our essential products. In this article, we will show you how to create an **Excel** loan calculator with extra payments.

## Download Practice Workbook

You can download the free **Excel** workbook from here and practice on your own.

## 2 Suitable Examples to Create an Excel Loan Calculator with Extra Payments

It is an important issue for the loan payer and receiver to determine and calculate the exact amount of instalment or payment per month regarding the payable amount and interest rate. Using **Excel** in this regard will help you to determine your payable amount correctly. In loan instalments, extra payments help to repay the loan earlier. In this article, we will use **the IFERROR function **in the first solution and a combination of **the PMT functions**, **the IPMT function,** and **the PPMT function** in the second approach to create an **Excel loan calculator with extra payments**. We will use the following sample data set to create an **Excel** loan calculator with extra payments.

### 1. Applying IFERROR Function to Create an Excel Loan Calculator with Extra Payments

We can create an **Excel** loan calculator with extra payments by applying **the IFERROR function**. The steps for this method are as follows.

**Step 1:**

- Firstly, calculate the scheduled payment in cell
.*C9* - To do this use the following formula by applying
**the IFERROR function.**

`=IFERROR(-PMT(C4/C6, C5*C6, C7), "")`

- Then, press
and you will get the scheduled payment in cell*Enter*which is*C9,***$2,575.10**.

**Step 2:**

- Now, determine payment in cell
using*C13***the****IFERROR function.**

`=IFERROR(IF($C$9<=H12, $C$9, H12+H12*$C$4/$C$6), "")`

- After that, press
and you will get the payment for the first month in cell*Enter*, which is*C13***$2575.10**.

- Finally, use the
**AutoFill**to drag down the formula to the lower cells in column.*C*

**Step 3:**

- Thirdly, determine the extra payment in column
for which you will use*D***the****IFERROR function.**

`=IFERROR(IF($C$8<H12-F13,$C$8, H12-F13), "")`

- Then, press
and you will get the extra payment for the first month in cell*Enter*which is*D13,***$100**.

- Finally, use the
**AutoFill**and drag the formula to the lower cells in column.*D*

**Step 4:**

- Here, calculate the total payment in column
.*E* - For this purpose, use
**the IFERROR function**formula below.

`=IFERROR(C13+D13, "")`

- Then, press
and you will get the total payment for the first month in cell*Enter*, which is*E13***$2,675.10**.

- Lastly, use the
**AutoFill**for dragging the formula to the lower cells in column.

**Step 5:**

- Now, determine the principal in column
by using*F***the IFERROR function**.

`=IFERROR(IF(C13>0, MIN(C13-G13, H12), 0), "")`

- Then, press
and get the principal for the first month in cell*Enter*, which is*F13***$2,437.60**.

- Lastly, use the
**AutoFill**and drag the formula to the lower cells of the column.

**Step 6:**

- In this step, calculate the interest in column.
- Apply the following formula from
**the IFERROR function**.

`=IFERROR(IF(C13>0, $C$4/$C$6*H12, 0), "")`

- Then, press
and get the value of the interest in cell*Enter*, which is*G13***$137.50**.

- Finally, use the
**AutoFill Tool**to drag the formula to the lower cells in column.*G*

**Step 7:**

- In the final step, calculate the balance in column
by using*H***the IFERROR function**.

`=IFERROR(IF(H12 >0, H12-F13-D13, 0), "")`

- Then, press
and get the value for the balance in cell*Enter*, which is*H13***$ 27,462.40**.

- Lastly, use the
**AutoFill Tool**and drag the formula to the lower cells in column**H**. - You can see that, after the
installment, you will be able to repay the loan with extra payments.*12th*

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

### 2. Combining the PMT, IPMT, and PPMT Functions to Create an Excel Loan Calculator with Extra Payments

If the loan amount, interest rate, and the number of periods are present, then you can calculate the required payments that will fully repay the loan by using **the PMT function**. **PMT **means payment in finance. We will use **the PMT function** to create an **Excel **loan calculator with extra payments. Along with **the PMT function**, we will also demonstrate the use of Excel’s interest payment function (**the IPMT function**) and principal payment function (**the PPMT function**) in this procedure.

**Step 1:**

- Firstly, calculate the payment (
**PMT**) in cell.*C9* - To do this, apply the following formula using
**the PMT****function**.

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

- Then, press
and you will get the scheduled payment in cell*Enter*which is*C9,***$2,575.10**.

**Step 2:**

- Now, accommodate the value of payment in cell
, which is equal to the value of cell*C13*.*C9*

`=$C$9`

- After that, press
**Enter**and you will get the payment for the first month in cell, which is*C13***$2575.10**.

- Finally, drag the formula to the lower cell in column
using the*C***AutoFill.**

**Step 3:**

- Thirdly, put the value of extra payment in column
, which is equal to the value of cell*D*.*C8*

`=$C$8`

- Then, press
and you will get the extra payment for the first month in cell*Enter*which is*D13,***$100**.

- Finally, to fill up the lower cells of that column, use
**AutoFill**.

**Step 4:**

- Here, calculate the total payment in column
by applying the following formula.*E*

`=C13+D13`

- Then, press
and you will get the total payment for the first month in cell*Enter*, which is*E13***$2,675.10**.

- Lastly, use the
**AutoFill**feature to drag the formula to the lower cells in column.*E*

**Step 5:**

- Now, determine the interest in column
with*F***the IPMT function**formula below.

`=-IPMT($C$4/$C$6,B13,$C$5*$C$6,$C$7)`

- Then, press
and get the interest for the first month in cell*Enter*, which is*F13***$137.50**.

- Lastly, use the
**Autofill**to fill the lower cells with values in column.*F*

**Step 6:**

- In this step, calculate the principal in column
while inserting*G***the PPMT function.**

`=-PPMT($C$4/$C$6,B13,$C$5*$C$6,$C$7)`

- Then, press Enter and get the value of the principal in cell
, which is*G13***$2437.60**.

- Finally, use the
**AutoFill**and fill the lower cells with values.

**Step 7:**

- In the final step, calculate the balance in column
by using the following formula.*H*

`=H12-G13`

- Then, press
and get the value for the balance in cell*Enter*, which is*H13***$ 27,562.40**.

- Finally, use the
**AutoFill****Tool**to drag the formula to the lower cells in column.*H* - You can see that, after the
instalment, you will be able to repay the loan with extra payments.*12th*

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

**Notes:**

- Use a minus (-) sign before
**the PPT function**,**the IPMT function**, and**the PPMT function.**In this way, the value from the formula will be positive and therefore easier to calculate. - Use
**absolute cell reference**where the input value is fixed or unchangeable for the lower cells. Otherwise, you will not get the desired result.

**Conclusion**

That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to create an **Excel** loan calculator with extra payments by using any of these methods. Please share any further queries or recommendations with us in the comments section below.

## Related Articles

**SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option****Student Loan Payoff Calculator with Amortization Table in Excel****Excel Simple Interest Loan Calculator with Payment Schedule****Home Loan EMI Calculator with Reducing Balance in Excel****Car Loan Amortization Schedule in Excel with Extra Payments****Create Loan Amortization Schedule with Moratorium Period in Excel**