AR Aging Report in Excel: DSO & Collections Guide (2026)

Ask three people at the same company what the DSO is and you will get three answers, all defensible, all different — often 12 days apart. That gap is not a rounding difference. It is the difference between "we collect in ten weeks" and "we collect in twelve," and it decides whether the revolver gets drawn. An AR aging report in Excel is the artifact that settles the argument, but only if it does more than bucket invoices into five columns.

This guide builds the full thing from a raw open-invoice export: an aging matrix that ties to the GL control account, three DSO methods with the formulas that separate them, a roll-rate collections forecast, and a CECL allowance derived from the same roll rates rather than a percentage somebody picked in 2019. Every formula is real and copy-ready.

What Is an AR Aging Report?

An AR aging report groups every open receivable by how long it has been outstanding, usually into Current, 1–30, 31–60, 61–90, and 90+ day buckets, summarized by customer. It answers three questions at once: how much cash is likely to arrive and when, which customers are deteriorating, and how much of the balance is probably never coming.

The buckets are conventional, not regulatory. What matters far more is the anchor date — the date the buckets are measured against.

Aging basis	Days past due formula	What it measures	Use it for
Due date	AsOf − Due Date	Delinquency against agreed terms	Collections, CECL, credit decisions
Invoice date	AsOf − Invoice Date	Total time capital is tied up	DSO reconciliation, cash conversion
Statement date	AsOf − Statement Date	Customer-facing dunning cycle	Dunning letters, customer statements
Ship/service date	AsOf − Service Date	Billing lag plus collection lag	Order-to-cash process diagnostics
Promise date	AsOf − Promised Pay Date	Broken commitments	Escalation and dispute triage

⚠️ Warning: Aging by invoice date and then benchmarking against "net 30" is the single most common error in AR reporting. A customer on net 60 terms shows up as 45 days past due on an invoice-date aging while being perfectly current. Age by due date for anything that drives a collections call.

How Do You Build an AR Aging Report in Excel?

Build it in three layers: a flat open-invoice table with one row per invoice, two calculated columns for days past due and bucket label, and a customer-by-bucket matrix driven by SUMIFS. Keeping the raw table flat means the matrix, the DSO calculation, and the allowance all read from one source that reconciles to the general ledger.

Step 1: Lay out the open-invoice table

Load the open items from your ERP into a table named AR with these columns:

Column	Field	Notes
A	Customer ID	The key you will group on — never the display name
B	Customer Name	For reporting only
C	Invoice No	Unique
D	Invoice Date
E	Due Date	From terms, not assumed
F	Invoice Amount	Gross
G	Cash Applied	Including partials
H	Credit Memos	Negative or positive, be consistent
I	Open Balance	=[@[Invoice Amount]]-[@[Cash Applied]]-[@[Credit Memos]]

Put the as-of date in a single cell and name it AsOf. Every date calculation in the workbook points at that one cell, so re-aging the whole report to a prior close date is a one-cell change.

💡 Pro Tip: Never use TODAY() inside an aging report. The moment you email the file, it re-ages itself on the recipient's machine and stops matching the balance sheet you sent it with. Hard-code AsOf to the close date.

Step 2: Calculate days past due and the bucket label

Days past due, floored at zero so not-yet-due invoices land in Current:

=MAX(0, AsOf - [@[Due Date]])

For the bucket, skip nested IF statements — they are unauditable and they hard-code the bucket boundaries into every row. Build a small lookup table named Buckets with a Min Days column (0, 1, 31, 61, 91) and a Label column, then:

=XLOOKUP([@[Days Past Due]], Buckets[Min Days], Buckets[Label], "Unaged", -1)

The -1 match mode means "next smallest," so 47 days past due finds the 31 row and returns 31-60. Change a bucket boundary in the lookup table and the entire report re-buckets.

If you are on a version without XLOOKUP, LOOKUP handles the same ascending-vector logic with array constants:

=LOOKUP([@[Days Past Due]], {0;1;31;61;91}, {"Current";"1-30";"31-60";"61-90";"90+"})

Step 3: Build the customer × bucket matrix

Put customer IDs down column A starting at row 15 and the five bucket labels across B14:F14, in aging order. Then one formula, filled right and down:

=SUMIFS(AR[Open Balance], AR[Customer ID], $A15, AR[Bucket], B$14)

Add a row total in column G and a grand total row. That grand total is the number that has to agree with the ledger.

Step 4: Tie out to the GL control account

This is the step that separates a report from a spreadsheet. Put the AR control account balance from the trial balance in a cell named GL_AR, then:

=IF(ROUND(SUM(AR[Open Balance])-GL_AR, 2)=0, "TIES", "BREAK: "&TEXT(SUM(AR[Open Balance])-GL_AR, "$#,##0.00"))

ℹ️ Note: The two most common causes of a break are unapplied cash sitting in a suspense account and credit memos posted to the GL but not yet applied to an invoice. Both are legitimate reconciling items — but they belong in a named reconciliation line, not absorbed silently into a bucket.

The modern one-formula alternative

If you are on Microsoft 365, PIVOTBY collapses steps 3 and 4 into a single spilled formula:

=PIVOTBY(AR[Customer Name], AR[Bucket], AR[Open Balance], SUM, 0, 1, , 1)

The catch: PIVOTBY sorts columns alphabetically, so your buckets come back as 1-30, 31-60, 61-90, 90+, Current — Current at the far right, which reads wrong. Either rename the buckets to sort correctly (0 Current, 1 1-30, …) or stay with the SUMIFS matrix where you control the column order. The full trade-off is covered in our GROUPBY and PIVOTBY guide.

Why Is My DSO Different From Everyone Else's?

Because there are at least four legitimate DSO formulas and they disagree by design. Simple DSO annualizes and is distorted by growth or seasonality; countback DSO consumes the AR balance against actual recent sales and is far more accurate; best possible DSO strips out delinquency entirely. Publishing one number without naming the method is how the argument starts.

Work through the same balance sheet with each method. Take an AR balance of $4,180,000 at 30 June, trailing-twelve-month credit sales of $18,000,000, and quarterly credit sales of $5,050,000 (April $1.40M, May $1.75M, June $1.90M — a business that is ramping).

Method	Formula	Result	Best for	Weakness
Simple (annual)	AR / TTM Sales × 365	84.8 days	Year-over-year trending	Badly distorted by growth or seasonality
Simple (quarterly)	AR / Q Sales × 91	75.3 days	Quick monthly reporting	Still assumes sales are flat within the quarter
Average AR	Avg AR / Q Sales × 91	78.6 days (avg AR $4.36M)	Smoothing month-end spikes	Hides a deteriorating trend inside the average
Countback	Consume AR against most recent months	72.4 days	Seasonal or fast-growing businesses	More data, harder to audit
Best possible (BPDSO)	Current AR / Sales × Days	52.3 days	Isolating terms from delinquency	Ignores the delinquent balance entirely
Average days delinquent	DSO − BPDSO	23.1 days	Measuring the collections team	Only meaningful next to BPDSO

The 12-day spread between the annual and countback figures is not noise — it is the ramp. Annualizing spreads a $4.18M balance over an average month of $1.5M in sales when the most recent month was $1.9M. For a growing business, simple DSO systematically overstates. For a declining one, it understates, which is precisely when you least want a flattering number.

💡 Pro Tip: Report DSO and ADD together. DSO of 72 with an ADD of 4 is a terms problem — go renegotiate. DSO of 72 with an ADD of 23 is a collections problem — go make phone calls. The two require entirely different interventions and DSO alone cannot distinguish them.

How Do You Calculate Countback DSO in Excel?

Countback DSO consumes the AR balance against the most recent month's credit sales, then the month before, counting full days while the balance still exceeds that month's sales and a pro-rated partial month when it does not. It is the method most credit organizations consider authoritative because it makes no assumption that sales are level.

The helper-column build

Lay out months most recent first, starting in row 5:

Cell	Column	Formula
A5	Month end	30-Jun (descending)
B5	Days in month	=DAY(A5)
C5	Credit sales	Hard-coded or linked
D5	AR entering month	=AR_Balance
D6	AR entering month	=MAX(0, D5-C5) (fill down)
E5	Days counted	see below (fill down)

=IF(D5>=C5, B5, IF(C5=0, 0, D5/C5*B5))

Then:

=SUM(E5:E16)

Traced through the numbers above: June absorbs $1.90M of the $4.18M and contributes a full 30 days. May absorbs $1.75M of the remaining $2.28M and contributes a full 31 days. April is left with $530,000 against $1.40M of sales, so it contributes 530/1400 × 30 = 11.4 days. Total: 72.4 days.

The one-cell dynamic array version

If your months and sales are already spilled ranges, SCAN does the running balance without helper columns:

=LET(
  ar,      AR_Balance,
  sales,   Sales_Desc,
  days,    Days_Desc,
  after,   SCAN(ar, sales, LAMBDA(acc, s, MAX(0, acc - s))),
  before,  VSTACK(ar, DROP(after, -1)),
  counted, IF(before >= sales, days, IF(sales = 0, 0, before / sales * days)),
  SUM(counted)
)

The only subtlety is the VSTACK/DROP pair. SCAN returns the balance after each month is consumed, but the days calculation needs the balance entering each month — so you prepend the opening balance and drop the final element. Wrap the whole thing in a LAMBDA and name it COUNTBACKDSO if you use it monthly; the pattern is covered in our LAMBDA custom functions guide.

⚠️ Warning: Countback DSO breaks if your sales history is shorter than the balance can consume. If the after vector never reaches zero, you have run out of months and the result is understated. Add =IF(MIN(after)>0, "EXTEND HISTORY", SUM(counted)) as a guard.

How Do You Forecast Collections From an Aging Report?

Use roll rates: the percentage of each bucket's balance that migrates to the next bucket a month later, measured across at least 12 months of history. What does not roll forward is collected, so the same rates that predict delinquency also predict cash — from the report you already built.

Calculating the roll rates

Stack your month-end aging totals in a grid, one row per month, one column per bucket. With last month's buckets in C5:G5 and this month's in C6:G6:

=IFERROR(D6/C5, 0)

That is the Current → 1–30 roll rate: the balance that is now 1–30 days past due, as a fraction of the balance that was Current a month ago. Fill right for the remaining buckets, fill down for every month pair, then average each column over 12 months.

graph LR
    A[Current<br/>$2,900K] -->|18% roll| B[1-30<br/>$780K]
    B -->|22% roll| C[31-60<br/>$310K]
    C -->|45% roll| D[61-90<br/>$118K]
    D -->|65% roll| E[90+<br/>$72K]
    E -->|40% roll| F[Write-off]
    A -->|82% collected| G[Cash]
    B -->|78% collected| G
    C -->|55% collected| G
    D -->|35% collected| G
    E -->|60% collected| G

⚠️ Warning: The Current → 1–30 roll rate is only clean when your payment terms are 30 days or shorter. On net 60 or net 90 terms, a large share of the Current bucket is simply not yet due and cannot roll, so the denominator is inflated and the rate is understated. For long-terms portfolios, compute roll rates on invoice vintages instead of on bucket balances.

Turning roll rates into a cash forecast

Expected collections next month from each bucket are the balance times one minus its roll rate. With balances in C20:G20 and roll rates in C10:G10:

=SUMPRODUCT(C20:G20, 1-C10:G10)

On the numbers above that is $2,378K + $608K + $171K + $41K + $43K = $3,241K of expected receipts, before any new invoicing. Push each bucket forward one month at its roll rate and repeat to get months two and three — which is exactly the receipts line a 13-week cash flow forecast needs, sourced from actual behaviour rather than a blanket "customers pay in 45 days" assumption.

Building the collections worklist

The report is only useful if it produces a call list. One formula, sorted by exposure:

=SORT(
  FILTER(
    CHOOSECOLS(AR, 2, 3, 9, 10),
    (AR[Days Past Due]>=31) * (AR[Open Balance]>1000)
  ),
  3, -1
)

The multiplication acts as a logical AND across the two criteria. Add a third condition — * (AR[Dispute Flag]="") — to keep invoices under dispute off the collections queue, since chasing them wastes the collector's time and irritates the customer.

How Do You Calculate the Allowance for Doubtful Accounts From the Aging?

Under ASC 326 (CECL), derive a cumulative loss rate for each bucket by chaining the roll rates forward to write-off, apply those rates to the current bucket balances, then layer a forward-looking qualitative adjustment. The aging report becomes the allowance model rather than a separate exercise.

Chaining roll rates into loss rates

Work right to left. The oldest bucket's loss rate is its own write-off rate; every bucket to the left is its roll rate times the loss rate of the bucket it rolls into.

G14: =G10                 ' 90+  → write-off:      40.0%
F14: =F10*G14             ' 61-90 → 65% × 40.0% =  26.0%
E14: =E10*F14             ' 31-60 → 45% × 26.0% =  11.7%
D14: =D10*E14             ' 1-30  → 22% × 11.7% =   2.6%
C14: =C10*D14             ' Current → 18% × 2.6% =  0.5%

Apply them to the balances and add the forward-looking overlay:

=SUMPRODUCT($C$20:$G$20, $C$14:$G$14) * (1 + Forward_Adj)

Bucket	Balance	Cumulative loss rate	Expected credit loss
Current	$2,900,000	0.46%	$13,436
1–30	$780,000	2.57%	$20,077
31–60	$310,000	11.70%	$36,270
61–90	$118,000	26.00%	$30,680
90+	$72,000	40.00%	$28,800
Total	$4,180,000	3.09%	$129,263

With a 15% forward-looking adjustment for a deteriorating outlook in one customer segment, the allowance lands at $148,652.

ℹ️ Note: CECL requires the estimate to reflect a reasonable and supportable forecast, not just historical experience. The Forward_Adj cell is where that judgement lives — document what drives it (a customer's credit downgrade, a sector-wide slowdown, a change in your own credit policy) in a note next to the cell. Auditors will ask, and "it's been 15% for three years" is not an answer. The same loss-rate mechanics scale up to loan portfolios, as covered in our bank financial model guide.

The allowance rollforward

Prove the ending balance the way the auditors will:

Beginning allowance
  + Bad debt expense       (the plug that gets you to the required ending balance)
  − Write-offs             (invoices removed from AR)
  + Recoveries             (previously written-off cash received)
  = Ending allowance

Bad debt expense is the derived figure, not an input. Compute the required ending allowance from the aging, then back into the expense.

Which Aging Report Design Should You Use?

graph TD
    A[What is the report for?] --> B{Driving collections<br/>calls?}
    B -->|Yes| C[Age by due date<br/>Exclude disputes<br/>Sort by exposure]
    B -->|No| D{Estimating the<br/>CECL allowance?}
    D -->|Yes| E{Terms longer<br/>than 30 days?}
    E -->|No| F[Bucket roll rates<br/>on balances]
    E -->|Yes| G[Vintage roll rates<br/>on invoice cohorts]
    D -->|No| H{Reporting DSO<br/>externally?}
    H -->|Yes| I[Countback DSO<br/>+ BPDSO + ADD]
    H -->|No| J[Simple DSO<br/>consistent period]

What Are the Most Common AR Aging Mistakes?

1. Netting unapplied cash into the oldest bucket. A customer with a $40,000 unapplied payment and a $38,000 90+ invoice looks like a collections emergency. It is a cash-application backlog. Show unapplied cash as its own line.
2. Letting credit memos create negative buckets. A −$12,000 balance in 61–90 makes the roll rates nonsense. Apply memos to the invoices they relate to before ageing, or isolate them.
3. Aging on customer name instead of customer ID. "Acme Corp", "ACME Corp.", and "Acme Corporation" become three customers, three credit limits, and three separate collection calls.
4. Using TODAY() anywhere in the file. The report stops reconciling the day after you send it.
5. Applying a fixed loss percentage per bucket. The percentages that came with the template were somebody else's portfolio. Derive them from your own roll rates.
6. Ignoring concentration. A 3.1% reserve is meaningless if 60% of the 90+ balance is one customer. Add a top-five exposure block: =TAKE(SORT(HSTACK(Cust, Over90), 2, -1), 5).
7. Reporting DSO without the method. Twelve days of spread is enough to change a covenant conversation.

Conditional formatting turns the matrix from a data dump into something a credit committee can read in ten seconds — a data bar across the 90+ column, a red fill where a customer's total exceeds their credit limit, and a grey fill on rows where the entire balance is Current so the eye skips them. The rule patterns are in our conditional formatting guide for financial models.

Frequently Asked Questions

What is a good DSO for a B2B company?

There is no universal benchmark — DSO is dominated by your payment terms. The useful test is DSO against best possible DSO: if your average terms are net 45, a BPDSO near 45 and a DSO of 52 means collections is working well. A DSO more than 15 days above BPDSO signals a real delinquency problem regardless of the absolute number.

How do you calculate days sales outstanding in Excel?

For simple DSO, use =AR_Balance / Credit_Sales * Days_In_Period, making sure the sales figure covers exactly the same period as the day count and excludes cash sales. For seasonal or growing businesses, use the countback method, which consumes the AR balance against the most recent months of actual sales and avoids the flat-sales assumption baked into the simple formula.

What is the difference between an AR aging report and an AR aging schedule?

They describe the same data at different grains. The report is typically invoice-level detail used for collections work — one row per open invoice with days past due. The schedule is the summarized customer-by-bucket matrix used for the allowance calculation and the financial statement note. Both come from the same open-invoice table; only the aggregation differs.

How often should you run an AR aging report?

Weekly for collections and monthly at close for the allowance. Collectors need a fresh worklist as invoices roll into new buckets, but the allowance and DSO must be anchored to the close date so they reconcile to the balance sheet. Running both from one workbook with a single AsOf cell gives you the weekly cadence without breaking the monthly tie-out.

Can Power Query automate an AR aging report?

Yes, and it should. Point Power Query at the ERP open-items export, add the days-past-due and bucket columns in the query rather than the worksheet, and refresh on open. The one thing to keep in the worksheet is the AsOf date, passed into the query as a parameter — hard-coding it inside the query is what breaks re-ageing to a prior period.

Wrapping Up

The aging report most finance teams inherit stops at the matrix. Everything valuable sits downstream of it: the roll rates that turn buckets into a cash forecast, the loss rates that turn the same roll rates into a defensible CECL allowance, and the DSO decomposition that tells you whether the problem is your terms or your collectors. All of it comes from one flat table and a handful of formulas — and all of it reconciles, because it never leaves that table.

If you are rebuilding this every month across entities and currencies, Dezzmond can generate the bucket logic, the countback formula, and the roll-rate grid from a description of your ERP export layout, and flag the GL break before it reaches the close file. Once the aging ties, run the same roll rates on your payables to get the other half of the equation — DPO and DIO alongside DSO give you the cash conversion cycle, which is where the working capital schedule picks the story up.