How to Create a Loan Calculator with Extra Payments in Excel – 2 Methods

The sample dataset will be used to create an Excel loan calculator with extra payments.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments


Method 1- Applying the IFERROR Function to Create a Loan Calculator with Extra Payments in Excel

Use the IFERROR function.

Steps:

  • Calculate the scheduled payment in C9.
  • Use the following formula.
=IFERROR(-PMT(C4/C6, C5*C6, C7), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Press Enter to see the scheduled payment in C9: $2,575.10.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Determine payment in C13.
=IFERROR(IF($C$9<=H12, $C$9, H12+H12*$C$4/$C$6), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Press Enter to see the payment for the first month in C13: $2575.10.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Determine the extra payment in column D.
=IFERROR(IF($C$8<H12-F13,$C$8, H12-F13), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Press Enter to see the extra payment for the first month in D13: $100.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Drag down the Fill Handle to see the result in the rest of the cells.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Calculate the total payment in column E.
  • Use the formula below.
=IFERROR(C13+D13, "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Press Enter to see the total payment for the first month in E13: $2,675.10.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Drag down the Fill Handle to see the result in the rest of the cells.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Determine the principal in column F.
=IFERROR(IF(C13>0, MIN(C13-G13, H12), 0), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Press Enter to see the principal for the first month in F13: $2,437.60.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Drag down the Fill Handle to see the result in the rest of the cells.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Calculate the interest.
  • Use the following formula.
=IFERROR(IF(C13>0, $C$4/$C$6*H12, 0), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Press Enter to see the value of the interest in G13: $137.50.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Drag down the Fill Handle to see the result in the rest of the cells.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Calculate the balance in column H.
=IFERROR(IF(H12 >0, H12-F13-D13, 0), "")

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Press Enter to see the value of the balance in H13: $ 27,462.40.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Drag down the Fill Handle to see the result in the rest of the cells.
  • This is the output.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments


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

Use the PMT function, the IPMT function and the PPMT functions.

Steps:

  • Calculate the payment (PMT) in C9.
  • Use the following formula.
=-PMT($C$4/$C$6,$C$5*$C$6,$C$7)

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Press Enter to see the scheduled payment in C9: $2,575.10.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Add the value of payment to C13, which is equal to C9.
=$C$9

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Press Enter to see the payment for the first month in C13: $2575.10.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Drag down the Fill Handle to see the result in the rest of the cells.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

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

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Press Enter to see the extra payment for the first month in D13: $100.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Drag down the Fill Handle to see the result in the rest of the cells.

Suitable Solutions to Create an Excel Loan Calculator with Extra Payments

  • Calculate the total payment in column E by entering the following formula.
=C13+D13

Sample Data Set

  • Press Enter to see the total payment for the first month in E13: $2,675.10.

Sample Data Set

  • Drag down the Fill Handle to see the result in the rest of the cells.

Sample Data Set

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

Sample Data Set

  • Press Enter to see the interest for the first month in F13: $137.50.

Sample Data Set

  • Drag down the Fill Handle to see the result in the rest of the cells.

Sample Data Set

  • Calculate the principal in column G. Enter the formula.
=-PPMT($C$4/$C$6,B13,$C$5*$C$6,$C$7)

Sample Data Set

  • Press Enter to see the value of the principal in G13: $2437.60.

Sample Data Set

  • Drag down the Fill Handle to see the result in the rest of the cells.

Sample Data Set

  • Calculate the balance in column H by using the following formula.
=H12-G13

Sample Data Set

  • Press Enter to see the value of the balance in H13: $ 27,562.40.

Sample Data Set

  • Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Sample Data Set


Notes:

  • Use a minus (-) sign before the PPT, IPMT, and PPMT functions. The value from the formula will be positive and easier to calculate.
  • Use an absolute cell reference.

Download Practice Workbook

Download the free Excel workbook here.


 

Related Articles


<< Go Back to Loan CalculatorFinance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
Md. Araf Bin Jayed
Md. Araf Bin Jayed

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo