Amortization Schedule with Balloon Payment and Extra Payments in Excel

While working with banking procedures we use Microsoft Excel. Excel has many useful tools or formulas to compute any calculation. While taking a loan from a bank, the balloon payment bills are on-time handles for each of five unknowns, including the amount of the balloon payment. With a printed amortization plan and an additional payment alternative. In this article, we will demonstrate the steps for making an amortization schedule with the balloon payment and extra payments in Excel.


Download Template

You can download the template and make changes.


What Is an Amortization Schedule in Excel?

An amortization schedule is a table format repayment plan specifying monthly bills on a loan or mortgage over a period of time. Each payment is subdivided into principal and interest, and the outstanding amount is shown after each payment.

An amortization schedule with the balloon payment and extra payments in excel is a very necessary plan while working with the loan department.


What Is a Balloon Payment and Extra Payments in Excel?

A balloon payment is yet another payment that is bigger than normal at the end of the loan period. Actually, a balloon payment is something that does not amortize completely throughout the loan’s duration. As a result, the payments do not completely cover the loan, and at the conclusion of the loan, a cash payment repayment is necessary to repay the debt.

If we wish to increase the amount of each monthly premium, the debt may depreciate faster. If we make an extra payment, the computer will calculate how several installments and months we spent over the initial loan period. These are actually the extra payments in excel.

If we want to calculate the terms, with excel we can easily do that. A built-in template will help to do all those calculations quickly. So, let’s make an amortization schedule with balloon payment and extra payments in excel.


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

Step 1: Establish Input Fields

  • To begin with, first, I need to define the input cells to make an amortization schedule with a balloon payment.
  • For this, we have the Annual Interest Rate, which is 5%. An annual rate is started as an annual percentage rate. It mainly computes the annual proportion amount we need to pay.
  • Then, we have our Loan in years which is just 1 year. Actually, when cash goes to the other person in consideration for the return of the loan principal annual interest, this is referred to as a loan. Also, we have the Payments Per Year which is 12, as our loan year is 1 so we need to pay the loan in the next 12 months.
  • Finally, the Loan Amount which is $20,000.

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


Step 2: Make a Schedule for the Amortization

As we have too many term periods, we may restrict the computations to the actual amount of payments for a certain loan. This is accomplished by enclosing each formula in an IF function. The function logical test will determine if the term period is less than or equal to the total payment. If the function returns TRUE, then the matching function will be computed. Now, let’s make the amortization schedule.

Calculate Total Payment Using PMT Function

To begin with, we need to calculate the total payment. And the best way to compute the total payment using the PMT function. The PMT function is a finance function in excel, that computes the mortgage payment based on constant repayments and a continuous rate of interest. The PMT function consists of the following arguments; rate, nper, pv, fv, type. Let’s follow the procedure to calculate the total payment using this function:

  • Firstly, select the cell where you want to calculate the total payment for the amortization schedule. So, we select cell C11.
  • Secondly, put the formula into that cell.
=PMT($C$5/$C$7, $C$6*$C$7, $C$8)
  • But this formula won’t give you an accurate result. For this, we have to compare whether the current row is in the payments per year or not. So, instead of the normal formula use the formula enclosed with the IF statement.
=IF(B11<=$C$6*$C$7, PMT($C$5/$C$7, $C$6*$C$7, $C$8), "")
  • Thirdly, press Enter to see the result.

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

  • Now, drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the Plus (+) symbol.

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

  • Finally, we will be able to see the total 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 IPMT Function to Calculate Interest

The IPMT function is needed to calculate the interest component of a loan payment over a specified time period. The IPMT function’s parameters are similar to the PMT function. And both function performs in the same way. The IPMT function also consists of the following arguments; rate, nper, pv, fv, type. We can compute the interest amount with this function. Let’s see the steps for this:

  • In the first place, select the cell where you want to put the formula to calculate the interest amount for the amortization schedule. Se, we select cell D11.
  • Then, enter the formula into the selected cell.
=IF(B11<=$C$6*$C$7, IPMT($C$5/$C$7, B11, $C$6*$C$7, $C$8), "")
  • Further, press the Enter key to finish the procedure.

  • Furthermore, to copy the formula over the range, drag the Fill Handle down or double-click on the Plus (+) icon.

  • And, that’s it! Lastly, you can see the result in the range of cells D11:D22.

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

  • The formula will work as STEP 2.

Discover the Principle Amount Using PPMT Function

The PPMT function in Excel is being used to compute the principal portion of a loan payment. And this function returns the principal payment for a certain period for a transaction with periodic, constant installments and a fixed interest rate. The function involves similar parameters as PMT and IPMT functions but it has an extra parameter called per which defines the period, which must be between 1 and nper. Let’s look at the steps for calculating the principal amount using this function:

  • Similarly, as in the previous step, select cell E11 and substitute the formula.
=IF(B11<=$C$6*$C$7,PPMT($C$5/$C$7, B11, $C$6*$C$7, $C$8), "")
  • Then, press Enter. And the formula will show in the formula bar.

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

  • Further, to replicate the formula throughout the range, drag the Fill Handle downward. To AutoFill the range, double-click on the Plus (+) symbol.

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

  • Finally, we can see the principal amount in cells E11:E22.

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


Compute Remaining Balance

Now, we need to calculate the Balance. We can easily do this by using a simple formula. All we need to do is sum the Loan Amount and the Principle Amount for each cell. Let’s see the substeps for this:

  • In the first place, select cell F5, where we want to compute the first periodic balance for the amortization schedule.
  • Secondly, put the simple formula into that selected cell.
=C8+E11
  • Press Enter key to see the result in that cell.

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

  • Now, to compute the 2nd periodic balance until the last balance, we need to sum the 1st periodic balance with the principal amount. So, select cell F12 and put the formula there.
=IF(B12<=$C$6*$C$7, F11+E12, "")
  • After that, press Enter on your keyboard.

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

  • Then, drag the Fill Handle downward to repeat the formula across the range. double-click on the Plus (+) sign to AutoFill the range.

  • And, finally, this will calculate the balance for each period.

That’s it. The amortization schedule is done. Now we need to make a summary of the balloon payment.


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

For balloon payments, first, we need to calculate the total payments. For this, we will use the SUM function. We need to use the whole cell range starting from C11. Also, we will be using the negative sum as the payments are for loans. Let’s see the subprocedures to make the summary of the balloon payment:

  • To begin with, select the cell to compute the Total Payments for the loan. So, we select cell F5.
  • Next, write the formula into that selected cell.
=-SUM(C11:C358)
  • Press Enter to see the result.

  • Now, we need to find the Total Interest. For this, again we will be using the SUM function.
  • Select cell F6 and put the formula for computing the total interest.
=-SUM(D11:D358)
  • Further, hit Enter to complete the procedure.

  • That concludes the method for making the amortization schedule with a balloon payment.

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 Procedures to Make an Amortization Schedule with Extra Payments in Excel

Step 1: Specified Input Fields

  • To proceed, we have to establish the input cells first. To create an amortization schedule that includes an extra payment.
  • We have the Annual Interest Rate of 5%, and an annual rate that begins with an annual percentage rate. It primarily computes the annual percentage amount that we must pay.
  • Then we have our Loan in Years, which is just for one year.
  • We also have the Payments Per Year, which is 12, because our loan year is 1, and we must repay the loan within the following 12 months.
  • Also, the loan amount of $20,000 is mentioned.
  • Finally, the Extra Payment is $50.


Step 2: Construct an Amortization Schedule

Since we have too many term periods. Each formula is encased in an IFERROR function to do this. The logical function test will assess whether the term period is less than or equal to the total payment. If the function returns TRUE, the matching function is calculated. IFERROR is a simple approach to catching and handling mistakes while resorting to much more complex nested IF statements. Let us now create an amortization schedule.

Utilize the Loan Amount as a Balance

To proceed, we have to make the loan amount as a balance in 0 periodic time. We will use the IF function to utilize the balance. Let’s see the substeps for this:

  • In the first place, select the cell where you want to put the result after utilizing the loan amount as balance. In our case, we select cell H12.
  • Then, enter the formula into that cell.
=IF(LoanAmount<>"", LoanAmount,"")
  • Finally, press Enter to see the result.

Step-By-Step Procedures to Make an Amortization Schedule with Extra Payment in Excel


Compute Schedule Payment

Now, we have to compute the scheduled payment for the starting time of the loan period. And we also need that balance that we just utilize in the earlier step. We are using the IFERROR function again to do this. Let’s see the quick sub-steps for calculating the scheduled payment using the formula:

  • Firstly, choose the cell where you want the outcome after using the formula for calculating the scheduled payment. In this scenario, we choose cell C13.
  • Secondly, put the formula into that resulting cell.
=IFERROR(IF(ScheduledPayment<=H12, ScheduledPayment, H12+H12*InterestRate/PaymentsPerYear), "")
  • And, press Enter key. This formula will show the result for the scheduled payment.

Step-By-Step Procedures to Make an Amortization Schedule with Extra Payment in Excel


Evaluate Interest

At this point, we need to compute the interest. For this, we again need the balance in the 0 periodic number of payments. Let’s see the subprocedure down to evaluate the interest:

  • To start with the procedure, select the field into which you wish to insert the result after using the formula. In this case, we choose cell G13.
=IFERROR(IF(C13>0, InterestRate/PaymentsPerYear*H12, 0), "")
  • At last, hit Enter to complete the operation.

Step-By-Step Procedures to Make an Amortization Schedule with Extra Payment in Excel


Find the Principal Amount

Here, we need to find the principal amount for each period of time until completing amortizing the loan payment. To compute this, we are combining the IFERROR and the MIN function. In the MIN function, we mainly subtract the interest amount from the scheduled payment. Let’s use the formula to find the principal amount.

  • Choose the cell where you want the output after using the formula. So, we choose cell F13.
  • Then, put the formula into that selected cell.
=IFERROR(IF(C13>0, MIN(C13-G13, H12), 0), "")
  • Press Enter to finish calculating the principal amount for the first period.


Calculate Extra Payment

To calculate the extra payment we have to subtract the balance from the principal amount. For this, we are using a formula. Let’s see the steps to find the extra payment using the formula in excel:

  • Firstly, select cell D13.
  • Secondly, put the formula into that chosen cell.
=IFERROR(IF(ExtraPayment<H12-F13, ExtraPayment, H12-F13), "")
  • To conclude the steps press the Enter key on your keyboard.

Step-By-Step Procedures to Make an Amortization Schedule with Extra Payment in Excel


Compute Total Payment

To compute the total payment, we have to sum up the scheduled payment and the extra payment. Again, we are using the IFERROR function for this:

  • By the same token as before, select the specific cell where you want to put the formula for calculating the total payment. So, we select cell E13.
  • Similarly, as in the earlier steps, enter the formula into that cell.
=IFERROR(C13+D13, "")
  • Press Enter key on the keyboard to finish the step for calculating the total payment.

Step-By-Step Procedures to Make an Amortization Schedule with Extra Payment in Excel


Calculate Remaining Balance for Each Periodical Month

Now, for each periodical month to amortize the loan amount, we have to calculate the remaining balance. For this, we have to subtract the principal amount and the extra payment from the loan amount which we consider as the 0 periodical balance. Let’s follow the subsequent steps:

  • Select the cell immediately down in the 0 periodic balance. So, we select cell H13.
  • After that, put the formula into that selected cell.
=IFERROR(IF(H12 >0, H12-F13-D13, 0), "")
  • Finally, press Enter to complete the procedure. And see the result in the resulting cell.


Amortization Schedule

This is the final amortization schedule for extra payments. All you have to do is follow the above steps and do the same for each column of the schedule. This may take a long time to make this schedule, that’s the reason we provide the template for free. So that, anyone can use the template.


Step 3: Make a Summary of Extra Payments

For an extra payment, first, we need to calculate sequentially the Total Schedule Payment, Schedule Number of Payment, Actual Number of Payment, Total Extra Payment, and Total Interest. Let’s follow the subprocedures for this:

  • Firstly, select the cell for computing the Schedule Payment. So we select cell H5.
  • Secondly, put the formula into that cell.
=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")
  • Then, press Enter to view the result in that cell.

Here, we use the IFERROR and PMT functions together.

  • After that, to compute the Schedule Number of Payment, select cell H6, and insert the formula there.
=LoanTerm*PaymentsPerYear
  • Again, press Enter key on your keyboard.

  • Further, we need to find the Actual Number of Payments, to compute that we are using the COUNTIF function. Now, choose cell H7 and put the formula there.
=COUNTIF(E13:E373,">"&0)
  • Similarly as before, hit Enter to see the result in that cell.

  • Furthermore, to calculate the Total Extra Payments, we need the SUM function. So, select cell H8 and insert the formula there to watch the result.
=SUM(D13:D363)
  • Likewise, in the previous step hit the Enter key.

  • Finally, to complete the extra payment for the amortization schedule, we have to calculate the Total Interest. For this, again we need the SUM function. So, select cell H9 and write the formula into that cell.
=SUM(G13:G373)
  • Lastly, pressing Enter will show the total interest and complete the procedure for making the amortization schedule with extra payment.


Final Template

This is the final template for the amortization schedule with extra payments. You can utilize the template and modify the input cells to meet your needs.


Conclusion

By following the above steps, we will be able to create an amortization schedule with the balloon payment and extra payments in excel easily. Or, else, you can just download our template and use the amortization schedule with the balloon payment and extra payments for your work in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo