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.

**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 many 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 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. 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.

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

- 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**.

- The formula will work as
**STEP 2**.

**Discover the Principal 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**. The formula will be shown in the formula bar.

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

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

**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 **Principal 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 the
**Enter**key to see the result in that cell.

- 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.

- 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.

**Read More: **Excel Interest Only Amortization Schedule with Balloon Payment Calculator

**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.

**Read More: **Multiple Loan Amortization Schedule Excel Template

**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.

**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 utilized 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.

**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 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.

**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.

**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 the
**Enter**key on the keyboard to finish the step for calculating the total payment.

**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.

**Read More: **Amortization Schedule with Irregular Payments in Excel

**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.

**Read More: **Amortization Schedule Excel Template with Extra Payments

**Download Template**

You can download the template and make changes.

**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.