Excel Interest Only Amortization Schedule with Balloon Payment Calculator

In this tutorial, we will explain how to calculate an interest-only amortization schedule with a balloon payment calculator in excel. We will develop a calculator which will take the input of price, loan amount, and amortization period. Then, the calculator will return the value of the balloon payment amount automatically.

Download Free Template

We can download the practice workbook from here.

What Is Interest Only Amortization?

An interest-only amortization is one in which the mortgagor (borrower) is only compelled to pay the interest on the loan for a specified length of time. In this process, the principal is returned in cash payment at a predetermined date or in installments.

What Is Balloon Payment?

A balloon payment mortgage is one that does not amortize completely during the duration of the loan, having left a debt due at completion. It’s called the balloon payment because of the large size of this final payment.

4 Steps to Calculate Interest Only Amortization Schedule with Balloon Payment Calculator in Excel

In this article, we will follow 4 steps to create a calculator to calculate the interest-only amortization schedule with a balloon payment calculator in excel. Perform all the steps in a single worksheet in your excel workbook. We will suggest you download the excel file that we have attached with this article. It will help you to understand the steps better.

STEP-1: Create Lender’s Information Segment to Calculate Interest Only Amortization in Excel

First and foremost, we will just create a segment like the following image. This segment will consist of the necessary information about the lender and the type of loan. The following image is just an example. You can add or remove any field of information if it is necessary.

STEP-2: Build Input Data Section to Calculate Interest Only Amortization Schedule with Balloon Payment

In this step, we will create an input data section to calculate the interest-only amortization schedule with a balloon payment. Like the following image, you will create input fields in your excel worksheet. You can add an extra input field if it is necessary for your calculation.

Now, we will calculate the amount of ‘TOTAL DOWN PAYMENT’ and ‘LOAN AMOUNT’ in cells I8 and I9. Let’s see how we can do this.

• First, select cell I8. Insert the following formula in that cell:

• Hit Enter.
• The above command returns the amount of ‘TOTAL DOWN PAYMENT’ in cell I8.

• Next, select cell I9. Insert the following formula in that cell:

• Then, press Enter.
• So, we get the ‘LOAN AMOUNT’ in cell I9.

STEP-3: Make Calculated Output Section to Get Interest Only Amortization in Excel

In this formula, we will create an output section. The output section will return the given parameters of the following image including the ‘BALLOON PAYMENT’.

Just go through the following steps in this section.

• Firstly, we select cell I19 and write down the following formula:

• Press Enter. The above formula returns the value of ‘Monthly Payment’ for our loan.

Here, the ROUND function is used to round the output when the return value of the output is decimal.

• Secondly, insert the following formula in cell I20:

• The formula will return nothing as the range specified in this formula is empty.

Here, the OFFSET function will return a range from a reference cell or range for a specific number of rows and columns.

• Then, enter the following formula in cell I21 which also returns nothing.

• The return value is 0 because the input range of the OFFSET function is still empty.

• Finally, we will calculate the amount of ‘INTEREST TOTAL’ by subtracting ‘LOAN AMOUNT’ from ‘TOTAL AMOUNT PAID’ with the following formula in cell I22.

STEP-4: Calculate Interest Only Amortization Schedule with Balloon Payment in Excel Over a Payment Period

The value of ‘NUMBER of PAYMENTS’ in STEP-2 was 10. This means we will repay the loan in 10 payment periods. Create a dataset like the following image with headings. Input the values like the following image from the inputs data section in your worksheet.

To perform this action we go over several steps.

• To begin with, select cell E28. Input the following formula in that cell:

• Next, press Enter. So, the above action returns the amount of ‘SCHEDULED PAYMENT’ for the first payment period.

• Similarly, insert the following formula in cell F28 to get the INTEREST amount in cell F28.

Here the ROUND function rounds the output of the formula. The IF function returns the rounded value if cell B28 is not blank.

• In addition, select cell B29 and insert the following formula:

• Press Enter. This returns the value of ‘PAYMENT PERIOD’ 2.

• Now, drag the Fill Handle tool from cell B29 to B37.

• Next, input the following formula in cell C29.

• Then, hit Enter and drag the Fill Handle tool to cell C37 from cell C29.

Here, the DATE function increases the previous date by one year in every step. The IF function returns the increased date if cell B29 is not blank.

• Furthermore, select cell I28. Insert the following formula:

• Press Enter. Then, drag the Fill Handle tool from the cell I 28 to I37.

• Then, select cell D29 and write down the following formula:

• Press Enter and drag the Fill Handle to cell D37.

Here, in this formula, if the rounded formula gives an error the ISERROR function will return a blank value. Otherwise, it will return the value of cell I28.

• Moreover, to calculate ‘SCHEDULED PAYMENT’ Select cell E29. Input the following formula:

• Hit Enter and drag the Fill Handle tool to cell E37.

• After that, input the following formula in cell F29:

• Press Enter. Drag the Fill Handle tool from cell F29 to F37.

Here in this formula, the IF function will return a blank value if cell B29 is blank. Otherwise, it will return the rounded value of the formula.

• Moreover, select cell G29 and write down the following formula:

• Hit Enter. Drag the Fill Handle tool from cell G29 to G37.

• Now, if we look at cell I37 we can see that the value of Balance is 0. So, all the loans have been paid.

• In the end, move to the output section which we discussed in STEP-3. We can see the amount of ‘BALLOON PAYMENT’ and ‘TOTAL AMOUNT PAID’ in cells I20 & I21.

Demo Calculation

It’s time to summarize the whole tutorial. So, we will use our input section and output section as a calculator to calculate the interest-only amortization schedule with balloon payment calculation. We will input any random values in the input fields. Then we will automatically get the values of the parameter in the output section. Let’s see how we can do this.

• First, We will change the value following fields from the previous example:

PURCHASE PRICE: $300000.00

PERCENT OF DOWN PAYMENT: 25%

ANNUAL INTEREST RATE: 5.00%

• Next, after changing the values, notice the output section. So, we can see that the values in the output section change automatically as per our change in input values.
• So, we can use this as a calculator for any value to calculate an interest-only amortization schedule with balloon payment calculation.

• Also, we can see the calculations also from this section. This part helps you to understand where the changes are actually happening.

Read More: Loan Amortization Schedule with Variable Interest Rate in Excel

Conclusion

Finally, this tutorial summarizes the concepts of creating a calculator to calculate an interest-only amortization schedule with balloon payment excel. So, to put your skills to the test, use the practice worksheet that comes with this article. Please leave a remark below if you have any questions. Our team will try to respond to you as soon as possible. In the future, keep an eye out for more innovative Microsoft Excel solutions.

Further Readings

• Interest Only Mortgage Calculator with Excel Formula (A Detailed Analysis)
• How to Use Formula for Mortgage Principal and Interest in Excel
• Formula for 30 Year Fixed Mortgage in Excel (3 Methods)
• How to Use Formula for Car Loan Amortization in Excel (with Quick Steps)
• Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel
• Creation of a Mortgage Calculator with Taxes and Insurance in Excel
• Mortgage Calculations with Excel Formula (5 Examples)