Rate and payment periods disagree
E3 is a nominal annual rate as a decimal. Divide it by E5, the number of equal payment periods per year. E4 is already the total count of payments; do not multiply it by E5 again.
Reconcile remaining loan balance after scheduled payments
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
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.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: E9, E10, E11. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Connect the remaining balance, principal repaid and interest paid under an unrounded end-payment schedule.
THE QUICK ANSWER01
FV uses the original positive receipt and the negative scheduled payment. Negate its result to report positive debt outstanding. Repaid principal and interest reconcile the cumulative payments.
E9
=-FV(E3/E5,E7,PMT(E3/E5,E4,E2,0,E6),E2,E6)E10
=E2-E9E11
=-PMT(E3/E5,E4,E2,0,E6)*E7-E10For the example data
E9 4943.59833299165E10 4656.40166700835E11 449.33263410923746
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
A balance needs an observation time
For end-of-period payments, k=0 means origination before interest has accrued. k=1 means immediately after the first scheduled payment. This task does not estimate accrued interest between payment dates or an early payoff quote.
The balance and repaid principal reconcile
The two positive amounts sum to the original principal. At zero rate, a principal of 2,400 with twelve payments leaves 1,400 after five payments and repays 1,000. Interest paid is zero. At a positive rate, some early payments pay interest instead of reducing principal.
Use the complete unrounded schedule
FV is evaluated with the same unrounded PMT used for all scheduled payments. Rounding each payment to cents produces a slightly different balance and often a final adjustment. Merely displaying two decimals does not change the stored payment.
Check all endpoints and intermediate cases
E7 must be an integer from zero to E4 and E6 must be zero. Other numeric bounds match the payment task. Endpoints alone cannot validate a balance formula: the datasets also include intermediate monthly, quarterly and annual payments. Native Excel and Sheets checks remain separate.
Read the full step-by-step walkthrough
- Count completed payments
E7 is zero before the first payment and E4 after the last. These end-payment cases measure balance immediately after a payment, not an arbitrary calendar date.
- Keep the nested payment on the same contract
The PMT inside FV must use the same original principal and total payment count. Its payment is negative while the receipt E2 is positive.
- Report positive outstanding debt
FV returns the signed future cash flow. Negate it to express positive debt remaining after completed end payments. At maturity the balance is zero apart from floating-point noise.
- Reconcile principal repaid
Subtract the remaining balance from original principal. Before payment zero is repaid; after the final payment all principal is repaid.
- Separate cumulative interest
Total positive outlay to date is negative PMT times completed payments. Subtract repaid principal to find interest paid. Keep the payment unrounded.
Things to watch
The cash-flow sign changed
The positive principal is money received by the borrower. Payments, interest payments and principal payments leave the borrower and are negative. Outstanding balance, total outlay and repaid principal are reported as positive amounts.
Rounding payments changes the model
Keep full precision inside the calculation. These tasks model equal unrounded payments and exclude fees, taxes, extra payments and a final cents adjustment. Formatting to two decimals is different from rounding each payment.
About this example
These original constant-rate teaching cases are checked with the local browser engine and independent arithmetic. Native Excel and Google Sheets recalculation, editing and saving remain unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.