Monthly loan payment with PMT
In B5, work out the monthly payment on the loan with PMT. The rate and the number of payments must both be monthly: PMT(B3/12,B4*12,-B2).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Loan | Value | ||||
| 2 | Amount borrowed | 24000 | ||||
| 3 | Interest rate per year | 0.06 | ||||
| 4 | Years | 4 | ||||
| 5 | Monthly payment | |||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Why this works
PMT needs the rate per period, the number of periods and the amount borrowed. With monthly payments the period is a month, so the rate is 6% / 12 = 0.5% and there are 4 × 12 = 48 payments. The payment is 563.64 a month. The minus in front of B2 is a sign convention: money you receive and money you pay go in opposite directions.
Common mistake: Mixing a yearly rate with monthly payments, PMT(B3,B4*12,-B2). The payment comes out more than five times too high.
The answer
- B5
=PMT(1B3/12,2B4*12,3-B2)
In plain words: The monthly payment on 3the amount borrowed in B2 over 2the years in B4, counted in months at 1the annual rate in B3, split into months.
+15 points