Build an Excel Tracker for Late Payments and Oldest Overdue Amount
Question details
Create a spreadsheet tracker for consumer invoices and partial payments that allocates payments to the oldest unpaid invoices first.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to track invoices and payments, calculate the remaining balance for each invoice, and identify the oldest outstanding overdue amount.
- Observed behavior
- Partial payments must reduce earlier unpaid invoices before later invoices, and overdue days must be calculated only for invoices with a remaining balance greater than zero.
Create separate Invoice and Payment tables, and ensure every invoice has a customer name, invoice date, due date, amount, and a unique sequence number. Sort the Invoice table by customer and then by invoice date from oldest to newest before assigning sequence numbers.
Use invoice and payment tables with oldest-first allocation formulas
Use this method when you need partial payments to automatically reduce each customer's oldest invoice balances before newer invoices.
Keep invoices and payments in separate Excel tables. The invoice table calculates a running billed total per customer, compares it with the customer's total payments, and returns the amount still unpaid on each invoice.
On an Invoice sheet, select your invoice data and choose Insert > Table. Name the table Invoices and add columns named Customer, InvoiceDate, DueDate, Amount, Sequence, CumulativeBilled, TotalPaid, and RemainingBalance. Enter a unique Sequence number for each customer's invoices in oldest-to-newest order.
On a Payment sheet, choose Insert > Table and name the table Payments. Add Customer, PaymentDate, and Amount columns, with one row for every payment received.
In the Invoices[CumulativeBilled] column, enter =SUMIFS(Invoices[Amount],Invoices[Customer],[@Customer],Invoices[Sequence],"<="&[@Sequence]). This totals the current customer's invoices through the current oldest-first sequence.
In the Invoices[TotalPaid] column, enter =SUMIFS(Payments[Amount],Payments[Customer],[@Customer]). This returns all recorded payments for the customer.
In the Invoices[RemainingBalance] column, enter =MAX(0,[@CumulativeBilled]-[@TotalPaid])-MAX(0,[@CumulativeBilled]-[@Amount]-[@TotalPaid]). A payment is therefore applied to earlier invoice amounts before any later invoice receives credit.
Find the oldest unpaid due date and calculate overdue days
Use these formulas after the RemainingBalance column is available to report the oldest overdue balance for a selected customer.
Build an oldest-first overdue payment tracker in WPS Spreadsheet
WPS Spreadsheet lets you organize invoice and payment tables, calculate remaining balances with SUMIFS and MAX, and identify the oldest unpaid due date with MINIFS. You can open existing Excel tracker files or build the tracker in a familiar spreadsheet interface.
- 1. Create invoice and payment tables: Open WPS Spreadsheet, create separate Invoice and Payment sheets, then select each data range and use Insert > Table.
- 2. Add allocation columns to invoices: On the Invoice sheet, add Sequence, CumulativeBilled, TotalPaid, and RemainingBalance columns after the invoice amount column.
- 3. Enter oldest-first allocation formulas: Use SUMIFS for cumulative invoices and payments, then use MAX formulas in RemainingBalance to allocate payments to the oldest invoices first.
- 4. Add an overdue summary: On a Summary sheet, use MINIFS to return the earliest due date where RemainingBalance is greater than zero, then use MAX(0,TODAY()-DueDate) for overdue days.
Frequently Asked Questions
Why should invoices and payments be stored in separate tables?
Separate tables allow one customer payment to be compared against all of that customer's invoices. Use SUMIFS on the Payments table to total payments, then calculate unpaid balances in the Invoices table.
How does the remaining balance formula apply a payment to the oldest invoice first?
The formula compares total payments with the running invoice total through each sequence number. Earlier invoice rows consume the available payment first, so later rows retain a balance until all earlier amounts are covered.
What should the Sequence column contain?
For each customer, use consecutive unique numbers based on invoice age: 1 for the oldest invoice, 2 for the next invoice, and so on. Sort by Customer and InvoiceDate before creating or checking the sequence.
How can I calculate overdue days without showing negative numbers?
Use =MAX(0,TODAY()-DueDate). The MAX function returns zero for invoices that are not yet due and returns a positive number only after the due date has passed.
Can I identify the oldest unpaid invoice for one customer?
Yes. Use =MINIFS(Invoices[DueDate],Invoices[Customer],CustomerCell,Invoices[RemainingBalance],">0") to return the earliest due date for that customer's unpaid invoices.
