Calculate a Balloon Payment in Excel

1Gather the details of your proposed balloon mortgage.
You will need the loan amount (principle), interest rate, term of amortization, loan term and any other details about the payment.

2Launch Microsoft Excel.

3Open a new workbook.

4Create labels for your variables in the cells from A1 down to A6 as follows: Principle, Interest Rate, Amortization periods, Payment periods, Payment and Balloon Payment.

5Enter the variables for your mortgage in the cells from B1 down to B4.
Assume a $100,000 mortgage at 6 percent interest that will be amortized over 30 years with a balloon payment after 7 years.
Using this example, enter $100,000 as the principle, =.06/12 as the monthly interest rate, 360 as the amortization periods (30 years x 12 months) and 84 as the number of payment periods (7 years x 12 months).

6Use the "PMT" function to calculate the monthly payment in cell B5.
Either type "=PMT(B2,B3,B1,0)" in the cell and press "Enter" or use the "fx" function key to create the formula.
Using this example, the monthly payment ​would be $599.55. It appears as a negative amount because this is money paid out.

7Use the "FV" payment function in Excel to calculate the balloon payment in cell B6.
Either type "=FV(B2,B4,B5,B1)" in the cell and press "Enter," or you can click the function button with the "fx" label and follow the steps to create your formula.

8Review your formula result for the balloon payment.
Using the variables in this example, a balloon payment of $89,639.39 will be due at the end of the loan's term.

9Adjust the variables, if desired, to reflect a different interest rate or payment amount.
This will allow you to calculate the effect of a better rate or making a higher monthly payment.