Skip to main content
SheetTutor.
SheetTutorLearn Excel by playBack to task

Reconcile remaining loan balance after scheduled payments

Your goal: Connect the remaining balance, principal repaid and interest paid under an unrounded end-payment schedule.

Full task requirements

Fill E9:E11 separately for an end-of-period schedule (E6 must be 0). E7 counts completed payments, from 0 through E4. E9 is the positive outstanding balance immediately after that payment; at zero it is the original balance. E10 is principal repaid and E11 is cumulative interest paid, both positive amounts.

After twelve monthly paymentsMatch the rate to each payment0 cells selected
Column E holds the model values beside their D labels. E4 is total payments; E5 is payments per year. Calculated results appear after your formulas pass.
Click or drag source cells; selected cells change color Drag from selected cells to deselect.
#
1
2
3
4
5
6
7
Step 1 of 4

Match the rate to each payment

Select the source cells in the worksheet, then complete the choices below.

Rules for this step

Select the annual nominal rate and payments-per-year input. PMT, FV, IPMT and PPMT use the rate for one payment period.

0 cells selected