A balloon loan or balloon mortgage payment is a payment in which you plan to pay off your auto or mortgage loan in a big chunk after a number of small regular monthly payments. To determine what that balloon payment will be, you can download the free Excel template below which calculates the regular monthly payment and balloon payment for a loan period between 1 and 360 months (30 years). The spreadsheet can be used for other types of balloon loan calculations as well.

Balloon Loan Calculator for Excel

Balloon Loan Calculator for Excel

Download

⤓ Excel (.xlsx)
For: Excel 2007 or later & Excel for iPad/iPhone

Description

Calculate the monthly payments, total interest, and the amount of the balloon payment for a simple loan using this Excel spreadsheet template.

The spreadsheet includes an amortization and payment schedule suitable for car loans, business loans, and mortgage loans.

Update 11/12/2015: The main download and the Google version now have you enter the total number of payments rather than the number of regular payments. The balloon payment is simply the final payment required to fully pay off the loan. This is a minor change but makes the calculator a bit easier to use. (You can still download the older .xls version)

Using the Balloon Mortgage Calculator

Why a Balloon Payment?

I originally created this spreadsheet to figure out a payment schedule for a car loan or auto loan. Why? Mainly because I didn't have the cash in hand to pay for the car in one lump sum, but I knew that I would after 6 months (because after 10 years of being a student, I was finally going to have a job). So, to keep the monthly payments low at first, we set up a 3-year loan with the plan to pay the loan off completely after about 6 months.

Rounding

The latest versions of the balloon loan calculator (v1.3+) take into account the fact that the regular payment and the interest are rounded to the nearest cent. The "Balloon Payment with Rounding" value is taken directly from the amortization schedule, which ensures that the final balance is zero.

Using the Balloon Payment Calculator for Mortgages

This spreadsheet can be useful as a mortgage calculator, particularly for calculating the balloon payment that is made when you sell your house after a number of years. However, there are many other costs associated with home buying/selling that the calculator does not take into account, such as property taxes, escrow payments, mortgage insurance, homeowner's insurance, closing costs, etc.

Interest-Only Mortgage Loans

An option has been added to the spreadsheet to choose between monthly payments that are amortized vs. payments that are interest-only. Warning! While interest-only loans may look appealing due to the low monthly payment, you still have to pay off the loan eventually. Beware of the consumer debt spiral!

More Loan Calculators

References

  • "Balloon Loan Definition," From investorwords.com, Mar 24, 2005.
Disclaimer: The spreadsheet and the info on this page is meant for educational purposes only. We believe the calculations to be correct, but do not guarantee the results. Please consult your financial advisor or lending institution before making any final financial decisions.