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:

`=I7*I6`

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

`=I6-I8`

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

`=ROUND(I10/12*I9,2)`

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

`=OFFSET(E26,I12+1,0,1,1)`

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

`=SUM(OFFSET(E26,2,0,I12,1))`

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

`=I21-I9`

### 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:

`=IF(B28="","",IF(B28=$I$12,I9+F28,$I$19))`

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

`=IF(B28="","",ROUND($I$10/12*I27,2))`

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:

`=IF(B28>=$I$12,"",B28+1)`

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

`=IF(B29="","",DATE(YEAR(C28),MONTH(C28)+1,DAY(C28)))`

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

`=IF(B28="","",I27-G28)`

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

`=IF(ISERROR(IF((ROUND(I28,1)>0),I28,"")),"",(IF((ROUND(I28,1)>0),I28,"")))`

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

`=IF(B29="","",IF(B29=$I$12,I28+F29,$I$19))`

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

- After that, input the following formula in cell
**F29**:

`=IF(B29="","",ROUND($I$10/12*I28,2))`

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

`=IF(B29="","",E29-F29)`

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