Excel Loan Calculator with Extra Payments (2 Examples)

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.

Suitable Solutions 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), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

Step 2:

  • Now, determine payment in cell C13 using the IFERROR function.
=IFERROR(IF($C$9<=H12, $C$9, H12+H12*$C$4/$C$6), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

Step 3:

  • Thirdly, determine the extra payment in column D for which you will use the IFERROR function.
=IFERROR(IF($C$8<H12-F13,$C$8, H12-F13), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

Step 4:

  • Here, calculate the total payment in column E.
  • For this purpose, use the IFERROR function formula below.
=IFERROR(C13+D13, "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

Step 5:

  • Now, determine the principal in column F by using the IFERROR function.
=IFERROR(IF(C13>0, MIN(C13-G13, H12), 0), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

Step 7:

  • In the final step, calculate the balance in column H by using the IFERROR function.
=IFERROR(IF(H12 >0, H12-F13-D13, 0), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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)

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

Step 2:

  • Now, accommodate the value of payment in cell C13, which is equal to the value of cell C9.
=$C$9

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

Step 3:

  • Thirdly, put the value of extra payment in column D, which is equal to the value of cell C8.
=$C$8

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

Step 4:

  • Here, calculate the total payment in column E by applying the following formula.
=C13+D13

Sample Data Set

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

Sample Data Set

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

Sample Data Set

Step 5:

  • Now, determine the interest in column F with the IPMT function formula below.
=-IPMT($C$4/$C$6,B13,$C$5*$C$6,$C$7)

Sample Data Set

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

Sample Data Set

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

Sample Data Set

Step 6:

  • In this step, calculate the principal in column G while inserting the PPMT function.
=-PPMT($C$4/$C$6,B13,$C$5*$C$6,$C$7)

Sample Data Set

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

Sample Data Set

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

Sample Data Set

Step 7:

  • In the final step, calculate the balance in column H by using the following formula.
=H12-G13

Sample Data Set

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

Sample Data Set

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

Sample Data Set

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

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo