About this lesson
IPMT and PPMT in Google Sheets: splitting a loan repayment into interest and principal
In Google Sheets, IPMT gives the interest part of one repayment and PPMT the principal part: =IPMT(rate,period,periods,amount) and =PPMT(rate,period,periods,amount), with the same arguments as PMT plus the period you want. The two always add up to the PMT repayment. For a run of periods, =-CUMIPMT(rate,periods,amount,first,last,0) totals the interest.
Using Excel? Do this lesson in Excel.
=PMT(B2/B4,B3*B4,-B1)What you'll practise
Budget the finance costs on a five-year term loan: the quarterly repayment, how much of one quarter's repayment is interest and how much is principal, and the interest for the whole first year.
4 steps · 40 points · the cells outlined in gold below
- IPMT
- PPMT
- CUMIPMT
- term loans
- finance costs
The data
| A | B | |
|---|---|---|
| 1 | Term loan ($) | $2,000,000.00 |
| 2 | Annual rate | 7.25% |
| 3 | Years | 5 |
| 4 | Repayments a year | 4 |
| 5 | ||
| 6 | Quarterly repayment | |
| 7 | Quarter to look at | 7 |
| 8 | Interest that quarter | |
| 9 | Principal that quarter | |
| 10 | Interest in year 1 |