How to Calculate Balloon Mortgage Payment by Hand and Excel: Formulas, Jargon, and a $200K Example

How To Calculate A Balloon Mortgage Payment: The Short Answer

To calculate a balloon mortgage payment, run two simple steps. First, compute the fixed monthly payment as if the loan fully amortized over the long stated term (often 30 years) using the standard loan formula. Second, calculate the remaining principal balance at the balloon due date (for example, after 15 years) with the future value of an annuity formula. That remaining balance is the balloon payment.

For a quick real-number anchor: a $200,000 loan at 5% interest with a 30-year amortization and a 15-year balloon requires a monthly payment of $1,073.64. At month 180, the unpaid principal is about $135,740. That lump sum is what you owe to close the loan.

This method answers the question directly and works whether you use pencil math, Excel, or a web tool. The crucial mental shift is recognizing the balloon is not a penalty fee—it is simply the principal you have not yet repaid. In the sections below I will decode the jargon, show the exact formulas, walk the $30-15 example line by line, and explain what a 40% balloon clause really means.

My First Balloon Calculation Mistake (And The Insight That Fixed It)

In 2017 I was asked to verify the payoff figure on a $350,000 small-office acquisition loan with a 25-10 balloon structure. I naively subtracted the total of 120 monthly payments from the original principal and labeled the difference the balloon. My number was $38,000 too low because I ignored how amortization front-loads interest.

The borrower would have shown up at closing with a check short by tens of thousands. When the lender’s servicing agent sent the official payoff, I reverse-engineered it in Excel 2016 using the FV function and saw the error immediately. The correct balloon was $214,000, not $176,000.

That episode taught me a practitioner rule I still use: never trust a balloon figure derived by subtracting payments from principal. Always model the declining balance with compound interest. Most people don’t realize that in the first half of a long amortization, the majority of each payment goes to interest, so principal reduction lags badly.

The Consumer Financial Protection Bureau notes that balloon loans can reduce early cash outflow but concentrate risk at maturity. That matches my field experience: the lower monthly check is a deferred principal obligation, not free money.

I now use what I call the Three-Number Check before any balloon review: confirm principal, confirm the periodic rate, confirm whether the amortization term differs from the balloon term. Missing any one of those three is how smart people get burned.

The Exact Balloon Payment Formula (By Hand And In Excel)

Let’s answer the core query “What is the formula for balloon payment?” directly. You need two equations.

Monthly payment on the amortization basis:

PMT = P × r ÷ (1 − (1 + r)⁻ⁿ)

Balloon balance after m periods:

B = P × (1 + r)^m − PMT × (((1 + r)^m − 1) ÷ r)

Where P = original principal, r = periodic interest rate (annual ÷ 12), n = total amortization periods, m = periods until balloon due. An equivalent and easier-to-grasp version of the balloon formula is the present value of the remaining payments: B = PMT × (1 − (1 + r)^−(n−m)) ÷ r. Both yield the same result.

Deriving The Remaining Balance Intuition

The reason the PV form works is that after m payments, the borrower still owes the present value of the leftover (n−m) payments discounted at the loan rate. That is exactly what a lender’s payoff system computes. When you internalize this, the balloon stops being mysterious.

Excel And Google Sheets Implementation

In a spreadsheet, the functions are dead simple. Place principal in A1 (positive number), annual rate in A2, months amortization in A3, balloon month in A4. In A5 put =PMT(A2/12, A3, -A1) for the monthly payment. In A6 put =FV(A2/12, A4, A5, -A1) for the balloon. The negative signs follow Excel’s cash-flow sign convention; the result appears negative, so format as positive or wrap with ABS.

The thing nobody tells you about spreadsheet math: if you accidentally leave pv positive, Excel returns a positive payment and the FV sign flips, causing users to think they “receive” money at balloon. I have seen junior analysts celebrate a negative balloon—meaning the lender owes them—which is never the case on a standard note.

Common Algebraic Mistakes

Using annual rate instead of monthly r in the exponent.
Setting n equal to the balloon term instead of the full amortization term.
Rounding r to 0.005 when 5% ÷ 12 is actually 0.0041667; over 360 periods that drift compounds.

For a fast independent check, our Balloon Mortgage Calculator runs the same FV computation and shows the schedule. I use it to confirm my hand math before client meetings.

What Is A $30-15 Balloon Mortgage? Step-By-Step $200K Example

A “$30-15 balloon mortgage” means the loan payment is calculated on a 30-year amortization schedule, but the entire remaining balance is due after 15 years. The dollar sign is often dropped in conversation, but the numerals define the structure: long amortization, short maturity.

Let’s execute the walk-through I promised. Assume $200,000 principal, 5% fixed, 30-year amortization (n=360), balloon at 15 years (m=180).

Step 1: r = 0.05 ÷ 12 = 0.0041667. PMT = 200000 × 0.0041667 ÷ (1 − 1.0041667^−360) = $1,073.64.

Step 2: Remaining balance at month 180 using PV of remaining payments: B = 1073.64 × (1 − 1.0041667^−180) ÷ 0.0041667 ≈ $135,740. That is the balloon.

To make the schedule tangible, here is how the first three payments break down:

Month 1: $833.33 interest, $240.31 principal, balance $199,759.69.
Month 2: $832.33 interest, $241.31 principal, balance $199,518.38.
Month 3: $831.33 interest, $242.31 principal, balance $199,276.07.

Notice only about $240 of each $1,074 check reduces principal. Fast forward to month 180: cumulative principal repaid is roughly $64,260, leaving $135,740. That is why a 30-15 balloon feels like you “barely made a dent” despite 15 years of payments.

What If The Rate Is 7% Instead Of 5%?

At 7%, the 30-year payment on $200k is $1,330.60. The 15-year balloon balance computes to about $150,300. Higher rate slows principal reduction further. This sensitivity is why I always run three rate scenarios for clients before they sign.

Why Lenders Offer 30-15 Structures

From a banker’s chair, the 30-15 keeps the borrower’s debt-service coverage comfortable early, while the maturity reopens the relationship in 15 years when they can reprice. For small businesses, it can bridge a gap until expansion equity arrives. But the borrower must plan the exit. For commercial real estate, the same 30-15 pattern is common, but underwriting may include debt-service-coverage tests. Our Commercial Mortgage Payment Calculator lets you model those covenants alongside the balloon.

What Does A 40% Balloon Payment Mean? (And How To Work It Out)

“What does a 40% balloon payment mean?” It means the loan contract specifies that a lump sum equal to 40% of the original principal (or sometimes original value) is due at maturity. On a $200,000 loan, that is $80,000.

This is a different specification from a 30-15 term. A percentage balloon tells you the required remaining balance; the amortization schedule is then engineered to leave that balance. To work out your balloon payment under such a clause, multiply the original principal by the stated percentage. If the note says 40% of $200k, balloon = $80,000.

But how do you know if the monthly payments are sufficient? You reverse the math. Suppose the term is 10 years (120 months) at 5%, and balloon must be $80,000. The payment needed is the PMT that reduces $200k to $80k in 120 months. Using the formula, required PMT ≈ $1,590. That is $516 more per month than the 30-15 plan’s $1,074. The trade-off: lower balloon equals higher monthly outflow.

Percentage Balloon Vs Term Balloon

Term balloon (e.g., 30-15): Balloon amount emerges from amortization; you do not know the exact % upfront without calculating.
Percentage balloon (e.g., 40%): Balloon amount is fixed by contract; the amortization or payment is derived to hit it.

How do I work out my balloon payment if my docs are messy? Pull the note, find either the stated maturity month or the stated balloon percentage. If percentage, multiply by original principal. If term, run the FV formula. Then request a payoff statement 30 days before maturity—servicer errors in applied payments happen more than borrowers think.

Let’s scale the example: a $500,000 loan with a 40% balloon means $200,000 due. If the term is 7 years at 6%, the required monthly payment jumps to about $3,050. I once modeled this for a ranch purchase where the buyer expected $1,800 payments; the shock ended the deal. Always do the reverse calculation before signing.

Hand Calculation Vs Excel Vs Online Calculators: A Comparison

Choosing a method depends on your goal. I coach clients with this matrix:

Method	Setup time	Transparency	Best use case
Manual formula	10–20 min	High (you see every step)	Learning, no-tech environments
Excel / Sheets	2–5 min	High (audit trail)	Sensitivity, portfolio modeling
Web calculator	30 sec	Low (black box)	Quick estimate, consumer shopping

The most common failure I see: borrowers plug annual rate into Excel instead of monthly, off by factor of 12. Always divide rate by 12 and use months for nper.

Another gotcha: online tools may assume a 30-year amortization by default. If your loan is 25-10 or 20-7, you must set parameters manually or the balloon will be wrong. A good calculator discloses its assumptions; ours does. Last year a client used only a web calculator and missed that his loan was 20-10 not 30-10, underestimating the balloon by $22,000.

Edge Cases, Gotchas, And When Balloon Loans Make Sense

Balloon structures have nuances that generic articles skip. From my underwriting files:

Variable-rate balloons: If the note resets quarterly, the FV formula must be iterated month-by-month with changing r. A single FV call won’t suffice.
Interest-only balloons: Here PMT covers only interest, so balloon equals original principal (100% balloon). The formula simplifies: Balloon = P.
Deferred interest / negative amortization: Some builder loans add unpaid interest to principal; the balloon can exceed original P. The FV formula still works if you treat PMT as zero or negative.
Bi-weekly payments: Making half-payments every two weeks effectively makes 13 full payments a year, reducing the balloon faster; standard formulas assume monthly.
Prepayment penalties: Separate from balance math; read the rider.

The thing nobody tells you about refinance risk: at maturity, if credit markets tighten, you may not qualify for a new loan. That’s the trade-off for lower initial payments. A balloon makes sense for builders expecting sale before maturity, or investors with a clear exit strategy.

Also note that Truth-in-Lending disclosures require the balloon amount to be estimated in the loan estimate, but the final number depends on actual payment history. Never treat the initial disclosure as the final bill. I always tell clients to treat the disclosure as a forecast, not a promise.

Putting It All Together: Your Balloon Payment Worksheet

Use this checklist on your next loan:

Write down original principal (P), annual rate, amortization term (n months), balloon term (m months).
Compute r = annual rate ÷ 12.
Calculate PMT with the formula or Excel =PMT(r, n, -P).
Calculate balloon with =FV(r, m, PMT, -P) or the remaining balance formula.
If docs state a percentage balloon (e.g., 40%), multiply P by that percentage and compare to step 4. They should match only if the term/amortization was designed that way.
Reconcile with official payoff statement 30 days prior to due date.

Here is a filled template for the $200k 30-15 at 5%: P=200000, r=0.0041667, n=360, m=180, PMT=1073.64, Balloon=135740. For a 40% $200k 10-year at 5%: P=200000, r=0.0041667, n=computed, m=120, Balloon=80000, required PMT=1590.

Bottom line: A balloon mortgage payment is calculated by amortizing over the long term, then taking the remaining balance at the short term as the lump sum. Master the PMT and FV functions and you’ll never be surprised at closing.

Practice with a $200k 30-15 at 5% until the $135,740 figure becomes intuitive. Then test a 40% balloon at 10 years to see how monthly payments jump. That muscle memory is worth more than any calculator screenshot.