Financial Functions
Advanced

CUMIPMT for one year in the middle of a loan

Interest paid in year 2 is periods 13 to 24.

Task:

For your tax return you need the interest paid on a loan during its second year only. The loan is 20,000 at 5% a year over 5 years, repaid monthly. In B5 give that interest as a positive figure.

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of CUMIPMT, CUMPRINC

0/3

The same formula in the other shapes it takes at work.

  1. CUMIPMT Function

    Calculate cumulative interest paid over a range of periods.

    Core
    Advanced
  2. CUMIPMT for one year in the middle of a loan

    Interest paid in year 2 is periods 13 to 24.

    Advanced
  3. The total interest over a whole loan

    From the first payment to the last: the full cost of borrowing.

    Advanced

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.

5 rows × 2 columns1 cell you fill in
AB
1Annual rate0.05
2Years5
3Loan20000
4
5Interest in year 2
What this exercise teachesMay contain the answer

Interest falls as the balance shrinks, so each year's interest is different and multiplying one month by 12 is wrong. CUMIPMT sums the exact interest across any window of the loan — a calendar year, a tax year, or the time before you refinance.