AR/AP Ageing Analysis in Excel: The 4-Bucket Method

Step 1: a DaysOverdue helper column

Resist the urge to embed date logic directly inside a bucket formula. Calculate days overdue once, as its own column, then bucket off that clean column — it’s more reliable and far easier to audit than a complex nested date formula buried inside SUMIFS criteria.

=TODAY()-DueDate

A negative result means the invoice isn’t yet due; positive means it’s overdue by that many days.

Step 2: four SUMIFS buckets

The four standard ageing buckets are 0–30, 31–60, 61–90, and 90+ days. Each of the first three uses two conditions against the DaysOverdue column — a lower bound and an upper bound. The top bucket is open-ended, so it only needs a single >90 condition:

=SUMIFS(OutstandingAmount,DaysOverdueRange,">=0",DaysOverdueRange,"<=30")
=SUMIFS(OutstandingAmount,DaysOverdueRange,">=31",DaysOverdueRange,"<=60")
=SUMIFS(OutstandingAmount,DaysOverdueRange,">=61",DaysOverdueRange,"<=90")
=SUMIFS(OutstandingAmount,DaysOverdueRange,">90")

Step 3: DSO and DPO

Two summary metrics condense the whole ageing picture into one trackable number each. DSO — Days Sales Outstanding — is roughly total receivables divided by average daily sales: “on average, how many days does it take us to collect a sale?” DPO — Days Payable Outstanding — is the payables mirror: how long you’re taking to pay suppliers.

=SUM(OutstandingAmount)/(SUM(SalesTbl[Amount])/365)

Neither DSO nor DPO is an exotic formula — it’s SUMIFS and division — but both are approximations, not precise accounting figures. Real calculations vary by methodology and period definition; what matters for a monthly report is tracking the trend, not treating the number as exact.

The reconciliation check that catches most mistakes

The most common error building this report is a gap or overlap at a bucket boundary — for example both the 0–30 and 31–60 buckets including day 30, or neither including it. Make this an explicit, required step, not an afterthought:

Sum of all 4 buckets should equal total outstanding amount — always check.

A worked example

Applied to a receivables list: ₹1.2 lakh sitting in the 90+ day bucket, concentrated in 3 customers. That single number immediately tells a credit-control team exactly who to follow up with, rather than reviewing every invoice individually. Repeat the same 4-bucket pattern on the payables list, and DPO tells you whether you’re paying suppliers faster or slower than last month.