Financial
Balloon loan formula: monthly payment (PMT with FV) & 5/25 balloon payoff balance
Model commercial balloon mortgages both ways: solve monthly PMT for a fixed maturity residual OR compute the 5-year balloon payoff on a 25-year amortization schedule.
Two commercial balloon structures—and getting PMT / FV cash-flow signs right!
Commercial real-estate and equipment notes use two distinct balloon structures on a $500,000 loan at 6.00% annual interest: (1) Target-Residual Balloon: pay down principal to a fixed $400,000 lump sum at Month 60 -> pass -400000 as PMT's 4th [fv] argument (negative because both monthly payments and the final balloon are cash outflows repaying the positive +500000 loan inflow!): -PMT(6%/12, 60, 500000, -400000) = $3,933.28/mo.
(2) "5/25" Amortizing Balloon: monthly payments follow a 25-year (300-month) schedule (-PMT(6%/12, 300, 500000) = $3,221.51/mo), and the entire unamortized principal falls due at Month 60 -> -FV(6%/12, 60, PMT(6%/12, 300, 500000), 500000) = $449,660.43 lump-sum balloon payoff!
Assumes fixed nominal annual interest divided by 12 and end-of-month payments. The balloon balance is due after the regular payment in the maturity month; the final cash outflow includes both that installment and the remaining balloon. Fees and day-count variations are excluded. Use positive whole terms with balloon months no greater than amortization months, and nonnegative principal, rate and target balance.
| Balloon Structure ($500k @ 6.00%) | Formula | Result |
|---|---|---|
| Target $400k Balloon @ Mo 60 | =ROUND(-PMT(B2/12, C2, A2, -D2), 2) | $3,933.28 / month |
| 5/25 Schedule Monthly Payment (300m) | =ROUND(-PMT(B2/12, E2, A2), 2) | $3,221.51 / month |
| 5/25 Balloon Balance Due @ Mo 60 | =ROUND(-FV(B2/12, C2, PMT(B2/12, E2, A2), A2), 2) | $449,660.43 lump sum |
| Interest-Only Balloon ($500k FV) | =ROUND(-PMT(B2/12, C2, A2, -A2), 2) | $2,500.00 / month |
Monthly payment to reach a specified balloon residual D2 at Month C2
Passing -D2 as the 4th argument of PMT ensures principal amortizes from +A2 down to D2 instead of 0.
=ROUND(-PMT(B2 / 12, C2, A2, -D2), 2)Remaining balloon payoff balance at Month C2 on an E2-month amortization schedule
Uses the unrounded modeled payment in FV. A lender that collects payments rounded to cents may quote a slightly different final balance.
=ROUND(-FV(B2 / 12, C2, PMT(B2 / 12, E2, A2), A2), 2)Try the example
Use the original values or change them to test your understanding.