Amortization Schedule with Balloon Payment and Extra Payments in Excel

What Is an Amortization Schedule in Excel?

An amortization schedule is a table-format repayment plan for monthly bills, loans, or a mortgage. Each payment is subdivided into principal and interest, and the outstanding amount is shown after each payment.

What Are Balloon Payments and Extra Payments?

A balloon payment is a loan form where the monthly payments are lower, with the last payment being significantly larger. Typically used before 2008, the final payment in the system is typically the loan principal while the monthly payments only cover the interest.

Extra payments are one-off payments on the loan to lower the loan amount since it shortens the duration of the loan and thus can reduce the total interest.

Step-By-Step Procedure to Make an Amortization Schedule with Balloon Payment in Excel

Step 1 – Establish Input Fields

Define the input cells to make an amortization schedule with a balloon payment.
Put the Annual Interest Rate, and we’ll use a sample rate of 5%.
Insert the Loan duration in years, where we’ll put 1.
Insert the Payments Per Year, which is 12 for a monthly repayment plan.
Put the Loan Amount (the sample will use $20,000).

Step 2 – Make a Schedule for the Amortization

Select the cell where you want to calculate the total payment for the amortization schedule. We selected cell C11.
Put the following formula into that cell.

=IF(B11<=$C$6*$C$7, PMT($C$5/$C$7, $C$6*$C$7, $C$8), "")

Press Enter to see the result.

Drag the Fill Handle down to duplicate the formula over the range or double-click on the Plus (+) symbol.

You will get the payment for each month over the range C11:C22.

How Does the Formula Work?

⇒ PMT($C$5/$C$7, $C$6*$C$7, $C$8): This will return the total period’s payment for a loan.

⇒ IF(B11<=$C$6*$C$7, PMT($C$5/$C$7, $C$6*$C$7, $C$8), “”): This will first compare whether the period is under the loan year or not and then similarly returns the periodic payment.

Using the IPMT Function to Calculate the Interest

Select cell D11.
Enter the following formula.

=IF(B11<=$C$6*$C$7, IPMT($C$5/$C$7, B11, $C$6*$C$7, $C$8), "")

Hit Enter.

Drag the Fill Handle down or double-click on the Plus (+) icon.

Here’s the result, showcasing the interest paid in each payment.

Calculate the Principal Amount Using the PPMT Function:

Select cell E11 and insert this formula.

=IF(B11<=$C$6*$C$7,PPMT($C$5/$C$7, B11, $C$6*$C$7, $C$8), "")

Press Enter.

Drag the Fill Handle down or double-click on the Plus (+) symbol.

We can see the principal amounts in cells E11:E22.

Compute the Remaining Balance

Select cell F5.
Put this formula into the cell.

=C8+E11

Press the Enter key to see the result in that cell.

Select cell F12 and put the following formula there.

=IF(B12<=$C$6*$C$7, F11+E12, "")

Press Enter on your keyboard.

Drag the Fill Handle down to repeat the formula across the range or double-click on the Plus (+) sign to AutoFill the range.

This will calculate the remaining balance for each period.

Step 3 – Make a Summary of the Balloon Payment/Loan

Select the cell to compute the Total Payments for the loan. We selected cell F5.
Insert the following formula.

=-SUM(C11:C358)

Press Enter to see the result.

Select cell F6 and put in the formula for computing the total interest:

=-SUM(D11:D358)

Hit Enter.

Final Template

This is the final template for the amortization schedule with a balloon payment. You can use the template and change the input cells as per your requirements.

Step-By-Step Procedure to Make an Amortization Schedule with Extra Payments in Excel

Step 1 – Specify Input Fields

Put the Annual Interest Rate, and we’ll use a sample rate of 5%.
Insert the Loan duration in years, where we’ll put 1.
Insert the Payments Per Year, which is 12 for a monthly repayment plan.
Put the Loan Amount (the sample will use $20,000).
Finally, the Extra Payment is $50. We’ll consider a simple example of extra payments every pay period.
We named each cell with its own named range (removing spaces) to make the template easier the read.