A finance deal with a lump sum at the end has a lower monthly payment than a plain loan, and dealers lead with that number. PMT's optional fourth argument, the amount still owed after the last payment, prices the deal properly, and setting it beside the plain loan shows what the lower payment really costs.
You do the books for a landscaping firm that is buying a $38,500 crew van. The dealer offers finance at 6.9% a year over 48 monthly payments, with a $12,000 balloon payment due at the end, on top of the last monthly payment. In B6, use PMT to work out the monthly payment on that deal. In B7, work out the monthly payment on the same van, rate and term with no balloon at all. In B8, work out how much more the firm pays in total, and so in interest, by taking the balloon deal instead of the plain loan. All three answers should be positive amounts.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 | Item | Value |
| 2 | Van price | 38500 |
| 3 | Annual interest rate | 0.069 |
| 4 | Term (monthly payments) | 48 |
| 5 | Balloon payment at the end | 12000 |
| 6 | Monthly payment with the balloon | |
| 7 | Monthly payment with no balloon | |
| 8 | Extra interest the balloon costs |
A balloon deal is a loan that is deliberately not paid off. The monthly payments only clear part of the debt, and the last $12,000 is settled in one go at the end. PMT's fourth argument, fv, is the balance left once the payments are done, which is normally zero and is why it can be left out. Setting it to the balloon tells PMT to size the payments so that after 48 of them exactly $12,000 is still owed. B3/12 turns 6.9% a year into 0.575% a month, to match the monthly payments. The signs follow the money: -B2 is the $38,500 the lender puts up for the van, and +B5 is the $12,000 that comes back to the lender at the end, opposite to the van, so PMT returns a positive $702.35. Give both the same sign, =PMT(B3/12,B4,-B2,-B5), and PMT reads the balloon as a further $12,000 the firm must pay down within the 48 months, which returns $1,137.94, more than the plain loan rather than less. The plain loan, with fv left at zero, costs $920.15 a month, so the balloon saves $217.80 a month, which is the figure the dealer leads with. It is not free. Under the balloon deal the firm hands over 48 × $702.35 plus $12,000, $45,712.64 in all; under the plain loan it hands over 48 × $920.15, $44,166.97. The van is the same $38,500 in both, so the $1,545.67 gap is pure extra interest. It arises because the $12,000 is never paid down: interest runs on the full amount for all 48 months, while in the plain loan that part of the debt shrinks month by month. Whether $217.80 a month of breathing room is worth $1,545.67 is a decision for the firm, but it can only make it once both numbers are on the sheet.