How to calculate days sales outstanding (DSO) in Excel

Step 1: Credit sales for the period

Keep one row per invoice with the date, the amount, whether it was a credit sale (you sent an invoice to pay later) or a cash sale (paid on the spot), and the date it was paid. Leave Paid on blank while it's open.

Only credit sales belong in DSO. A counter sale paid in cash never becomes a receivable, so counting it makes collection look faster than it is. With the period dates in J2 and J3, SUMIFS adds the credit invoices dated August 1 to October 31: $29,600. The three cash sales ($1,350) stay out.

Step 2: Receivables at period end, then DSO

Accounts receivable (AR) is what customers still owe. Here it's the credit invoices with no paid date: $19,770. Days in the period is J3-J2+1 = 92, because subtracting dates leaves out the first day. Some teams use a fixed 30, 90 or 365 instead. Either works if you never switch.

DSO is $19,770 / $29,600 x 92, about 61.4 days. Put cash sales in by mistake and it drops to 58.8, a nicer number that's wrong.

Run the export after the period ends and some invoices may be paid by then. They were still owed on October 31, so count them too: add SUMIFS(D2:D15,E2:E15,"Credit",C2:C15,"<="&J3,F2:F15,">"&J3) to the open total. If customers pay in parts, use a Balance column instead of Amount, so AR is the amount still owed and not the original invoice.

Monthly DSO, and why it doesn't match the quarter

For October alone, divide the same AR by October's credit sales ($17,520) and multiply by the days in October. DAY(EOMONTH(J4,0)) returns 31 from the month start in J4. The result is about 35 days.

That's not a mistake, and it's the most confusing thing about DSO. The formula compares what's owed with average daily sales. Over the quarter that's $321.74 a day; in October alone it's $565.16 a day, because October was a big month. Same receivables, bigger daily sales, lower DSO.

So pick one period length for your trend and keep it. A common choice is to recalculate every month on the last 90 days of sales. Comparing a monthly DSO with a quarterly one tells you nothing.

One big invoice can move DSO more than late payers

Orion Group's $9,500 invoice is dated October 30, one day before the period ends. Nobody is late on it, yet it's almost half of AR. Take it out of both the receivables and the sales and quarterly DSO falls from 61.4 to about 47 days.

A big invoice near period end pushes DSO up, and one paid early pushes it down, without any change in how fast customers pay. When DSO jumps, check the biggest open invoices before chasing anyone. The aging table below shows where the money actually sits.

The countback method

Countback asks how many recent days of sales add up to the money you're owed. Work backward from the latest month until AR is used up. It reacts less to a single big month than the average method.

Here: AR is $19,770. October's credit sales were $17,520, so all 31 October days count and $2,250 is left. September's credit sales were $4,930, so the rest covers 2,250 / 4,930 x 30 = 13.7 days. Countback DSO is 31 + 13.7, about 44.7 days.

1. Start with AR at period end.
2. Take the latest month's credit sales. If AR is larger, count every day of that month and subtract that month's sales from AR.
3. Repeat with the month before until the remaining AR is smaller than a month's sales.
4. For that last month, count (remaining AR / that month's sales) x days in that month, and add it.

AR aging buckets with SUMIFS

Add an Age column: =IF(F2="",$J$3-C2,"") gives days since the invoice date for open invoices and stays blank for paid ones. SUMIFS with two conditions on the same column fills each bucket: "<=30", then ">30" and "<=60", then ">60" and "<=90", then ">90".

Here $14,920 is 30 days old or less, $1,750 is 31 to 60 days and $3,100 (Northfield's August invoice, 65 days) is 61 to 90. That's 24.5% of AR older than 30 days. DSO says "about two months"; aging tells you which customer to call. Swap SUMIFS for COUNTIFS to count invoices per bucket.