About this lesson
PMT in Google Sheets: repayments on equipment finance, in arrears, in advance and with a balloon
In Google Sheets, PMT gives the equal repayment on a loan: =PMT(rate,periods,amount). For monthly repayments on an annual rate, =PMT(B2/12,B3*12,-B1). Add 1 as the last argument, =PMT(B2/12,B3*12,-B1,0,1), for payments at the start of each month (in advance), and put a balloon in the fourth argument.
Using Excel? Do this lesson in Excel.
=PMT(B2/12,B3*12,-B1)What you'll practise
Price the finance on two haul trucks: the monthly repayment with PMT, paid in advance with the type argument, with a balloon, and the total interest.
4 steps · 40 points · the cells outlined in gold below
- PMT
- annuities
- payments in advance
- balloon payments
The data
| A | B | |
|---|---|---|
| 1 | Cost of trucks ($) | $1,260,000.00 |
| 2 | Annual rate | 8.4% |
| 3 | Term (years) | 5 |
| 4 | Balloon (% of cost) | 20% |
| 5 | ||
| 6 | Repayment, in arrears | |
| 7 | Repayment, in advance | |
| 8 | Repayment with balloon | |
| 9 | Total interest ($) |