Invoice tracking: partial payments and overdue days
Build an invoice tracker that separates invoice value, payments, outstanding balance, and overdue days, with a fixed review date and a worked example.
Track an invoice’s outstanding amount as invoice value less cumulative payments. Calculate overdue days separately, using the due date and a stated review date. A partial payment reduces the balance but does not change the due date of the remaining amount unless your agreement changes it.
What columns does an invoice payment tracker need?
Start with an invoice ID, customer, due date, invoice amount, and payments received. Calculate outstanding balance and days overdue in separate columns. Keep the invoice ID stable so new payments update the correct record.
The invoice payment tracker uses this structure with fictional invoices. Its review date is October 12, 2026. You can replace that date when preparing a later report and still reproduce the earlier result from a saved copy.
How do partial payments change the outstanding balance?
An invoice for $1,250 with $500 paid has $750 outstanding: 1250-500. A second payment of $300 brings cumulative payments to $800 and reduces the remaining balance to $450.
If invoice amount is D11 and cumulative payments are E11, the formula is =D11-E11. Enter either the cumulative amount on one invoice row or maintain a separate payment ledger and sum it by invoice ID. Do not add a new full invoice row for each payment: that would duplicate the original invoice amount in totals.
Keep overpayments visible. A $1,250 invoice with $1,300 paid produces a negative $50 balance that needs review. Flooring every balance at zero would hide that information.
How do you calculate overdue days at a fixed date?
With due date in C11, outstanding balance in F11, and review date in B7, use =IF(F11<=0,0,MAX(0,$B$7-C11)) when the due date is populated.
For an unpaid balance due October 1 and reviewed October 12, the result is 11 calendar days overdue. An invoice due October 20 shows zero overdue days on that review date. A fully paid invoice also shows zero days overdue in this open-balance report.
This measure describes the remaining obligation at review. If you need the historic lateness of a payment that has already cleared, record its actual payment date and compare that date with the due date in a separate measure.
How should supplier bills and reimbursements be tracked?
Supplier bills reverse the direction of the obligation: you owe the remaining amount. The accounts payable planner keeps bill amount, payments, due dates, and days until due together. Keep receivables and payables separate so their signs and ownership remain clear.
Expense reimbursements need an approval basis as well as a payment basis. A claimed expense can include disallowed items, so approved or eligible amount may differ from the original claim.
What should you check before sharing an invoice report?
Compare the invoice population and payment cutoff with the source records. Inspect missing due dates, duplicate invoice IDs, negative balances, and payments allocated to the wrong invoice. A collection tracker is useful for follow-up; it does not replace a reconciled accounting ledger or determine tax treatment.