New plan10 templates a month$14.95/month, cancel anytime

Start your plan

Balloon payment calculator in Excel: formulas, amortization schedule and how to plan for the balloon

12 min read2,624 words
Balloon payment calculator in Excel: formulas, amortization schedule and how to plan for the balloon illustration

A loan offer arrives with a monthly payment that looks surprisingly low, and a line in the small print: "balance due at maturity". That line is the balloon payment. The payment is low because it does not pay off the loan.

A loan offer arrives with a monthly payment that looks surprisingly low, and a line in the small print: "balance due at maturity". That line is the balloon payment. The payment is low because it does not pay off the loan. It pays the interest and a small slice of principal, and the rest comes due in one large lump sum on a fixed date.

Balloon loans are common in more places than people expect: short-term mortgages, owner financing when a seller carries the loan, commercial property loans, equipment finance and car finance with a final payment. They can be a sensible tool, but only if you know exactly how large the balloon is, when it falls due and how you will pay it. This guide builds a balloon payment calculator in Excel from scratch, step by step, with a worked example for a mortgage and a car loan, then shows how to plan for the day the balloon arrives.

What a balloon payment is

A balloon payment is a large final payment that repays the balance left after the regular payments. Three numbers define any balloon loan:

  • The term, how long until the loan is due, for example 7 years.
  • The amortization period, the length of loan the payments are calculated as if they were paying off, for example 30 years.
  • The balloon, the balance still owed when the term ends.

When the amortization period is longer than the term, the payments are small and the balloon is large. When they are equal, there is no balloon and you have an ordinary loan. That is the key to understanding every balloon loan: the payment is calculated on one timeline and the loan is due on a shorter one.

There are four common ways a balloon is set:

  1. 01By amortization period. Payments based on 30 years, loan due in 7. Typical for balloon mortgages and commercial loans.
  2. 02By a fixed amount. The agreement names the balloon, for example 50,000.
  3. 03As a percentage of the price. Common in car finance, where the final payment is a set share of the vehicle's price.
  4. 04Interest only. Payments cover only the interest, and the entire loan is the balloon.

Step 1: the regular payment

For a loan paid over the amortization period, Excel's PMT function gives the payment. Put the inputs in named cells: Loan, Rate (annual), AmortYears and TermYears.

=-PMT(Rate/12, AmortYears*12, Loan)

PMT returns a negative number because it is money going out, so the minus sign makes the payment positive. Dividing the rate and multiplying the years by 12 converts everything to months. For weekly, bi-weekly, quarterly or annual payments, replace 12 with 52, 26, 4 or 1.

For a balloon set as a fixed amount or a percentage of the price, give PMT a future value. The payment must leave exactly the balloon owed at the end:

=-PMT(Rate/12, TermYears*12, Loan, -Balloon)

The last argument is what remains after the final payment, and it is negative because it is still owed. A larger balloon means a lower payment.

For interest only, no function is needed: =Loan*Rate/12.

Step 2: the balloon

The balloon is the balance after the last scheduled payment. FV, the future value function, calculates it directly:

=FV(Rate/12, TermYears*12, Payment, -Loan)

Read it as: starting from a loan of this size, after this many payments of this amount, how much is still owed? You can also use it to find the balance at any point, replacing the number of payments with any payment number, which is how you find what you owe if you refinance in year 5.

Then the rest of the summary follows:

  • Interest paid before the balloon: =Payment*TermYears*12-(Loan-Balloon)
  • Principal repaid before the balloon: =Loan-Balloon
  • Total paid including the balloon: =Payment*TermYears*12+Balloon

Step 3: a worked example, a balloon mortgage

A fictional home buyer borrows 300,000 at a 6.5 percent annual rate. The monthly payments are calculated on a 30-year amortization, but the whole balance is due after 7 years.

ItemResult
Monthly payment1,896.20
Balloon due after 84 payments271,248.73
Principal repaid in 7 years28,751.27
Interest paid in 7 years130,529.88
Total paid including the balloon430,529.88

Look at what the payment actually does. In the very first payment, 1,625.00 is interest and only 271.20 reduces the loan. After seven years of payments, the borrower has paid 159,281 in instalments and still owes 271,249. That is why a balloon loan feels cheap monthly and expensive overall.

Now compare it with two alternatives for the same loan and rate:

Loan structureMonthly paymentOwed at the end of 7 years
Balloon, 30-year amortization1,896.20271,248.73
Interest only1,625.00300,000.00
Fully amortizing over 7 years4,454.830

The balloon loan's payment is 2,558.63 a month lower than a loan that is fully repaid in 7 years. That difference is the real product on offer: cash flow now, in exchange for a large obligation later. Interest only is lower still, but it repays nothing. Which of the three is right depends entirely on what you expect to happen before month 84.

Total cost: the APR with fees

Upfront fees change the true cost. RATE calculates the periodic rate that makes the money received equal the payments plus the balloon:

=RATE(TermYears*12, -Payment, Loan-Fees, -Balloon)*12

With 3,000 of fees deducted from the 300,000, the annual percentage rate of the example rises from 6.50 to about 6.69 percent. The fees seem small against the loan, but they are spread over only 84 payments rather than 360, so they weigh more on a balloon loan than on a standard one.

Step 4: an amortization schedule with balloon payment

A schedule makes the balloon visible payment by payment. Build one row per payment with these columns: Payment number, Date, Opening balance, Interest, Payment, Principal, Closing balance.

  • Date: =EDATE(FirstPaymentDate, n-1) for monthly payments.
  • Opening balance: the first row is =Loan; each later row is the previous row's closing balance.
  • Interest: =Opening*Rate/12
  • Payment: =Payment, except on the last row.
  • Principal: =Payment-Interest
  • Closing balance: =Opening-Principal

On the final row of the term, add a balloon column: =IF(n=TermYears*12, Closing, 0). The schedule ends there, with the balloon shown as one extra payment due.

Using the example, the balance falls very slowly, which is what a long amortization period does:

End of yearBalance still owed
1296,646.82
2293,069.08
3289,251.73
4285,178.72
5280,832.93
6276,196.10
7271,248.73

In seven years the balance drops by under 29,000 on a 300,000 loan, about 9.6 percent. This is the number to show anyone who believes they are "building equity" through the payments alone.

Extra payments shrink the balloon

Any extra payment goes straight to principal, so it lowers every later interest charge as well. Add an Extra payment column and subtract it in the principal formula: =Payment+Extra-Interest.

In the example, an extra 200 a month for the whole 7 years reduces the balloon from 271,248.73 to 250,046.05, a reduction of 21,202.68. The interest paid also falls, from 130,529.88 to 126,127.20. The extra payments total 16,800, so about 4,400 of the reduction is interest you did not have to pay.

Step 5: balloon loans on cars and seller financing

The mechanics are the same, but the inputs differ.

A car loan with a balloon. A buyer finances a 36,000 vehicle with 3,600 down, which leaves a 32,400 loan. The agreement sets a final payment of 30 percent of the price, which is 10,800, after 60 months at 7 percent.

Balloon car loanStandard car loan
Monthly payment490.71641.56
Final payment10,800.00none
Interest over 60 months7,842.356,093.53
Total paid40,242.3538,493.53

The monthly payment is 150.85 lower, and the buyer pays 1,748.82 more in total. For a driver who plans to trade the car in at month 60, that can be a fair price. For a driver who keeps the car, the final payment of 10,800 is still owed.

Seller financing. When a seller finances the sale, a balloon is common because the seller does not want to wait 30 years to be paid. To judge the offer from the seller's side, add up what they receive: the down payment, the regular payments, the balloon and the total interest. The same schedule gives all four numbers.

Step 6: planning for the balloon

The balloon date is a deadline, and there are only three ways to meet it. Test each one in the spreadsheet before you sign.

Save for it

The monthly saving needed to have the balloon ready is the future value problem in reverse:

=-PMT(SavingsRate/12, TermYears*12, 0, Balloon)

In the example, saving 271,249 over 84 months at a 4 percent annual return takes 2,803.48 a month. With no interest at all it takes 3,229.15 a month. Both numbers are far higher than the loan payment itself, which tells you that for most borrowers, saving the whole balloon is not realistic. It is a useful reality check.

Refinance it

Most borrowers plan to replace the balloon with a new loan. Test it with PMT using the balloon as the new loan: if rates have changed to 6 percent and the new loan runs 25 years, the payment on 271,248.73 is 1,747.66 a month, and 253,049.08 of interest will be paid over the new term.

Refinancing carries a risk that the calculator cannot remove: you will need to qualify for the new loan at that time, at that rate. If rates rise or the asset falls in value, the option may be worse than it looks today. Run it at a higher rate to see how much room you have. At 7.5 percent on a 30-year amortization, for example, the balloon rises to about 275,500 and the payment to 2,097.64.

Sell the asset

If the plan is to sell, equity at the balloon date is the sale price minus the balloon. Assume the 375,000 home grows at 3 percent a year. After 7 years it is worth about 461,203, so selling would leave roughly 189,954 after paying the balloon, before selling costs. Run the same sum with growth of zero or a fall in value to see what happens if the market does not help.

Together, these three tests give you a decision. If saving is unrealistic, refinancing depends on the market and selling depends on the price, then the loan has a real risk. If at least one route holds up under pessimistic assumptions, the balloon is manageable.

Sensitivity: what moves the balloon

A single calculation can make a balloon loan look more certain than it is. Change one input at a time on the example loan:

Input changedPaymentBalloon
Term 5 years, 30-year amortization1,896.20280,833
Term 7 years1,896.20271,249
Term 10 years1,896.20254,328
Rate 5.5 percent1,703.37266,448
Rate 6.5 percent1,896.20271,249
Rate 7.5 percent2,097.64275,502

Two things stand out. The balloon barely changes with the rate, because most of the payment is interest in the early years, so the borrower is not paying down much at any rate. And a shorter term leaves a bigger balloon on the same payment, which is the pattern to watch for when comparing offers: a lower payment is always paid for somewhere else.

Mistakes that make balloon loans go wrong

  • Looking only at the payment. The balloon is the real size of the loan. Always read the balloon and the due date first.
  • Assuming refinancing is guaranteed. Lenders decide at the time, based on your income, the asset's value and market rates.
  • Ignoring fees. Upfront costs are spread over a shorter period, so the APR can be noticeably higher than the quoted rate.
  • Confusing amortization period with term. They are different numbers, and the gap between them is the size of the balloon.
  • Not testing a pessimistic case. Run the plan with a higher rate, a lower sale price and a later start before you commit.
  • Treating extra payments as optional maths. Because the balance falls so slowly, even small extra payments reduce the balloon visibly.

A balloon calculator that does all of this

Building the payment, balloon, schedule and plan once is a good way to understand a balloon loan, and rebuilding it for every offer takes time. The Balloon Payment Calculator in Excel does it from a single set of inputs.

On the Calculator tab you enter the purchase price, down payment, fees, rate, payment frequency and first payment date, then choose how the balloon is set: by amortization period, a fixed amount, a percentage of the price or interest only. It returns the regular payment, the balloon and its due date, interest paid, total paid, the cost of credit and an APR that includes fees, and compares the loan with a fully amortizing one over the same term. A seller financing view shows what the seller receives.

The Schedule tab lists every payment with its date, opening balance, interest, payment, extra payment, principal, the balloon and the closing balance, for monthly, bi-weekly, weekly, quarterly, semi-annual or annual payments and up to 1,560 payments, with a yearly summary and a chart. The Balloon Plan tab compares saving, refinancing and selling, the Compare tab sets four loan structures side by side and the Sensitivity tab varies rate, balloon size, term and amortization period. Calculated cells are locked without a password and there are no macros. Results are estimates for planning, so check them against your loan agreement.

If you only need a simpler calculation, these templates may be enough:

Common questions

What is a balloon payment?

A large lump sum due at the end of a loan. The regular payments are kept low because they do not repay the whole loan during the term, so the remaining balance is paid in one go when the loan matures.

How do I calculate a balloon payment in Excel?

Calculate the regular payment with =-PMT(Rate/12, AmortYears*12, Loan), then find the balance left after the last scheduled payment with =FV(Rate/12, TermYears*12, Payment, -Loan). That balance is the balloon.

How do I make an amortization schedule with a balloon payment?

Build one row per payment with the opening balance, interest (=Opening*Rate/12), payment, principal and closing balance. On the last row of the term, show the closing balance as the balloon due.

Can extra payments reduce the balloon?

Yes. Extra payments go to principal, so the balance and the balloon are lower, and less interest is charged on the way. In the example, 200 extra a month cuts the balloon by about 21,200.

Is a balloon loan cheaper than a standard loan?

The regular payment is lower, but you still owe the balloon, and the borrower usually pays more interest in total on the same loan. Compare the payment, the balloon and the total cost side by side before choosing.

What can I do when the balloon is due?

Pay it from savings, refinance it into a new loan or sell the asset and use the proceeds. Test all three with pessimistic assumptions, because each one depends on something outside the spreadsheet.

Does it work in Google Sheets?

The calculator is built for Microsoft Excel 2016 or later (desktop or Excel for the web). You can import it into Google Sheets, but sheet protection does not carry over and charts may look different, so Excel is the better fit.

A balloon loan is manageable when you know the size of the balloon, the date and the plan for it before you sign. You can preview the Balloon Payment Calculator in Excel free; downloading it is included in the OpenWorksheet monthly plan, and you can also buy this template on its own. All figures in this guide are examples for fictional loans, and this is general information rather than financial advice.

Keep reading