Report how much loan interest was deductible in each of two tax years
CUMIPMT sums just the interest portion of a loan's payments between two period numbers you name, instead of amortizing the whole schedule to get there — and the figure it returns shrinks every year, because a level payment sends more toward principal as the balance owed goes down.
Task:
You handle equipment accounting for a construction company that financed a $60,000 excavator over 5 years, in 60 equal monthly payments, at a 6% annual interest rate. The accountant preparing the tax filing needs the interest expense for each of the first two years reported separately, since only the interest portion of a payment is deductible. In B5, use CUMIPMT to find the total interest paid across Year 1 (payments 1 through 12). In B6, find the total interest paid across Year 2 (payments 13 through 24).
Interactive Spreadsheet
Solve without hints for +5 XP
The data in this exercise
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 | Loan amount | 60000 |
| 3 | Annual interest rate | 0.06 |
| 4 | Term (months) | 60 |
| 5 | Year 1 interest paid | |
| 6 | Year 2 interest paid |
What this exercise teaches (contains the answer)
CUMIPMT(B3/12,B4,B2,1,12,0) walks the loan's amortization schedule between period 1 and period 12 and sums just the interest portion of each of those payments, which comes to $3,311.43 for Year 1 — the same total twelve separate interest calculations would produce, without laying any of them out. The annual rate and the term describe a whole year while the payments fall monthly, so both need converting to that same monthly unit before CUMIPMT can use them, exactly the reconciliation PMT and FV require. CUMIPMT returns the figure as a negative number, the same convention PMT uses for a payment leaving an account, so ABS turns it into the positive figure an expense report actually wants. Repeating the same formula for periods 13 through 24 gives $2,657.14 for Year 2 — noticeably less than Year 1, because a level monthly payment sends more toward principal every month as the balance still owed shrinks, leaving less of each payment to be interest.