Vendor ageing reports built by hand in a spreadsheet often quietly get the math wrong — usually because payments aren't matched to specific bills in the right order. Here's the logic that holds up.
When a vendor has multiple open bills and you make a partial payment, which bill does that payment reduce? Most accounting convention applies payments against the oldest outstanding bill first — FIFO. Getting this wrong doesn't just shift which bucket an amount lands in; it can misstate which bills are actually overdue.
Walk through a vendor's transactions in date order. Each credit entry adds a new "open bill" with its own date and amount. Each debit (payment) is consumed against the oldest open bill first, then the next, until the payment is fully applied.
Once payments are matched off, only the remaining open balance on each bill should be aged into 0-30 / 31-60 / 61-90 / 90+ day buckets — based on the bill's own date, not the date of the last transaction.
CaSync's free Ledger Analyzer runs this exact FIFO logic in your browser — upload a vendor ledger export and see the ageing built automatically.
Send a sample file and we'll show you the cleaned output.