Total interest on a loan
PremiumIn B5, work out how much interest the loan costs in total: all the monthly payments added up, minus the amount borrowed. That is PMT(B3/12,B4*12,-B2)*B4*12-B2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Loan | Value | ||||
| 2 | Amount borrowed | 50000 | ||||
| 3 | Interest rate per year | 0.07 | ||||
| 4 | Years | 3 | ||||
| 5 | Total interest | |||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock Loans: PMT and NPER and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Why this works
The monthly payment is 1,543.85 and there are 36 of them, so you pay back 55,578.77 in total. Take away the 50,000 you borrowed and the interest is 5,578.77. Working out 7% of 50,000 for three years (10,500) overstates it, since the balance shrinks with every payment.
Common mistake: Working out the interest as amount × rate × years. That charges interest on the full amount the whole time, but the balance goes down every month.
The answer
- B5
=PMT(B3/12,B4*12,-B2)*B4*12-B2