How to calculate days payable outstanding (DPO) in Excel

Step 1: Purchases in the period

Keep one row per supplier bill: the bill date, the amount, the due date and the date you paid it. Leave Paid on blank while the bill is open. Most accounting tools can export this as an "unpaid bills" or "AP" report; ask for all bills, paid and unpaid, for the period.

With the period start in J2 and the end in J3, SUMIFS adds every bill dated August 1 to October 31: $23,140. Textbook DPO divides by cost of goods sold, which suits businesses that buy stock to resell. For a service business, the bills you buy on credit are the better measure. Pick one and keep it every month.

Step 2: Unpaid bills at period end, then DPO

Accounts payable (AP) is what you still owe suppliers: the bills with no paid date, $12,700 here. Days in the period is J3-J2+1 = 92, since subtracting two dates leaves out the first day.

DPO is $12,700 / $23,140 x 92, about 50.5 days. On average you take about 50 days of purchases to pay your suppliers. Compare that with the terms they give you: if most bills are net 30, a DPO of 50 means a lot of bills are paid late or sit near period end.

If you export after the period ends, some bills may be paid by then. They were still owed on October 31, so count them too: add SUMIFS(D2:D13,C2:C13,"<="&J3,F2:F13,">"&J3) to the open total. For October alone, use October's purchases ($9,950) and 31 days: 39.6 days, lower because the quarter's daily purchases are smaller than October's.

DPO next to DSO: the cash gap

DPO on its own says little. Put it next to days sales outstanding (DSO), how long customers take to pay you. The DSO in J4 (61 days) comes from your receivables sheet; the DSO guide shows how to calculate it from an invoice list.

DSO minus DPO is how many days you pay suppliers before customers pay you: 61 - 50.5 is about 10.5 days that your own cash has to cover. A business that holds stock adds days inventory outstanding as well, and the full cash conversion cycle is days inventory + DSO - DPO.

A higher DPO shrinks that gap, which is why it can look good on paper. Getting there by paying late isn't free: late fees, lost discounts and suppliers who stop giving you terms. Read DPO with the overdue count from the next section.

Bills due this week and next

For planning, the due dates matter more than the average. COUNTIFS and SUMIFS with two conditions on the Due date column find the open bills in a window. From October 31 (J3), the next 7 days hold 1 bill for $1,980 (Millbrook, due November 4). Days 8 to 14 hold 2 bills for $2,520.

For the second week, change the window to ">"&J3+7 and "<="&J3+14. Overdue bills are open ones with a due date on or before the date in J3: one, Apex's $2,750 bill that was due October 22.

In a live sheet, replace J3 with TODAY() so the windows move each day: =SUMIFS(D2:D13,F2:F13,"",E2:E13,">"&TODAY(),E2:E13,"<="&TODAY()+7).

What a skipped early-payment discount costs

Terms like 2/10 net 30 mean 2% off if you pay within 10 days, otherwise the full amount in 30. Put the discount, the discount days and the net days in input cells (J5:J7) so you can test other terms.

Skipping the discount means you pay 2% more to keep your cash for 20 more days. As a yearly rate that's 2% / 98% x 365 / 20, about 37.2%. Compare it with what your cash or credit line costs you; the sheet shows the number, the choice is yours.

To see a bill's discount deadline, add the discount days to the bill date: =C7+$J$6. On Apex's $2,750 bill the discount would have been $55.