Financial Functions
Advanced

The balance still owed after a year

Loan minus the principal repaid so far.

Task:

You are thinking of paying off a 30,000 loan (4.5% a year over 6 years, monthly) after its first 18 payments. In B6 give the balance still owed at that point.

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/2

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

  1. CUMPRINC Function

    Calculate cumulative principal paid over a range of periods.

    Core
    Advanced
  2. The balance still owed after a year

    Loan minus the principal repaid so far.

    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.

6 rows × 2 columns1 cell you fill in
AB
1Annual rate0.045
2Years6
3Loan30000
4Payments made18
5
6Balance owed
What this exercise teachesMay contain the answer

Eighteen payments of about 476 is roughly 8,580 paid, but much of the early payments went on interest, so the balance has fallen by less. CUMPRINC counts only the principal, which is exactly what a settlement figure is built from.