PMT with a balloon payment
Leave part of the loan to be paid at the end, and the monthly payment falls.
Task:
A van costs 32,000, financed at 5.9% a year over 4 years with a final balloon payment of 8,000 left at the end. In B6 give the monthly payment, as a positive figure.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of PMT
0/4The same formula in the other shapes it takes at work.
- PMT Function
Calculate the payment for a loan based on constant payments and a constant interest rate using the PMT function.
CoreAdvanced - PMT with annual terms converted to monthly
The loan is quoted per year; the payment is per month.
Advanced - PMT with a balloon payment
Leave part of the loan to be paid at the end, and the monthly payment falls.
Advanced - PMT for a savings goal
Not a loan this time: how much to put away each month to reach a target.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | |
|---|---|---|
| 1 | Annual rate | 0.059 |
| 2 | Years | 4 |
| 3 | Price | 32000 |
| 4 | Balloon | 8000 |
| 5 | ||
| 6 | Monthly payment |
What this exercise teachesMay contain the answer
A balloon is a future value: the monthly payments only have to bring the balance down to 8,000, not to zero, so they are lower. The catch is that you still owe the 8,000 — and interest has been charged on it the whole time.