Excel guide · 4 min read

How to calculate the cash conversion cycle in Excel

Quick answerCash conversion cycle (CCC) = days inventory outstanding + days sales outstanding - days payable outstanding. In Excel, with inventory in D2, receivables in E2, payables in F2, sales in B2, cost of goods sold in C2 and the days in the month in G2: =D2/C2*G2+E2/B2*G2-F2/C2*G2. Here October's cycle is 41.1 + 20.4 - 16.1 = 45.4 days.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	E	F	G	H	I	J	K
1	Month	Sales	COGS	Inventory	Receivables	Payables	Days	DIO	DSO	DPO	CCC
2	May 1, 2026	84000	50400	61000	52000	31000	31	37.51984127	19.19047619	19.067460317	37.642857143
3	Jun 1, 2026	88000	52800	63500	55500	32500	30	36.079545455	18.920454545	18.465909091	36.534090909
4	Jul 1, 2026	79000	47400	66000	54000	29000	31	43.164556962	21.189873418	18.966244726	45.388185654
5	Aug 1, 2026	91000	54600	64000	60500	33500	31	36.336996337	20.60989011	19.02014652	37.926739927
6	Sep 1, 2026	95000	57000	70500	66000	34000	30	37.105263158	20.842105263	17.894736842	40.052631579
7	Oct 1, 2026	98000	58800	78000	64500	30500	31	41.12244898	20.403061224	16.079931973	45.445578231

Download this sheet as an Excel practice file (.xlsx)

Step 1: One row per month, and the days in each month

The sheet is a small kitchenware wholesaler. Each row is one month: sales, cost of goods sold (COGS), and the month-end balances of inventory, receivables (what customers owe) and payables (what you owe suppliers). Your bookkeeping software's balance sheet and profit and loss reports give you all five.

The three day counts each need the number of days in the period. Typing 30 for every month is close but not right: October has 31. =DAY(EOMONTH(A2,0)) returns the last day of the month in A2, which is the number of days, so the table stays right for February too.

The cash conversion cycle is how many days your cash is tied up: from paying for stock, through holding and selling it, to collecting from the customer, minus the days your suppliers let you wait before you pay them.

=DAY(EOMONTH(A2,0))

Result: 31

31: the number of days in May 2026.

Step 2: DIO, DSO and DPO

Days inventory outstanding (DIO) = inventory / COGS x days. October: $78,000 / $58,800 x 31 = 41.1 days of stock on hand. Days sales outstanding (DSO) = receivables / sales x days: 20.4 days. Days payable outstanding (DPO) = payables / COGS x days: 16.1 days.

Use COGS, not sales, for DIO and DPO. Inventory and payables are at cost, so dividing them by sales (which include your markup) makes both look shorter than they are. For DSO, use credit sales only if some customers pay on the spot; cash sales never become receivables.

If you already track purchases, DPO on purchases instead of COGS is fine. The DPO guide does it that way. Pick one and keep it for every month.

=D7/C7*G7

Result: 41.12244898

About 41.12: October's days inventory outstanding.

Step 3: The cycle, and a monthly trend

Add the first two and take away the third: =H2+I2-J2 in K2, copied down. In Excel 365 one formula does all six months: =D2:D7/C2:C7*G2:G7+E2:E7/B2:B7*G2:G7-F2:F7/C2:C7*G2:G7 spills a column of cycles.

The cycle runs 37.6, 36.5, 45.4, 37.9, 40.1 and 45.4 days from May to October. July and October stand out. A single month can jump because one big delivery arrived on the 30th, so read the trend, not one row.

Month-end balances are a snapshot. Many people use the average of the opening and closing balance instead. For October that's =(D6+D7)/2/C7*G7, 39.1 days of inventory, and a cycle of 42.8 days. Either way works if you use it every month.

=H7+I7-J7

Result: 45.445578231

About 45.45: October's cash conversion cycle in days.

Which of the three moved?

A longer cycle means more of your cash is waiting somewhere. To find where, subtract last month's value from this month's for each part. From September to October the cycle grew by 5.4 days: inventory +4.0 days, receivables -0.4 days, payables -1.8 days.

Watch the sign on DPO. It's subtracted, so when DPO falls by 1.8 days, the cycle grows by 1.8 days. Here the business paid suppliers faster while stock built up ahead of the holiday season. Receivables actually improved slightly.

Over the six months, the cycle grew 7.8 days: 3.6 from inventory, 1.2 from receivables and 3.0 from paying faster. That tells you which conversation to have: with the buyer, the person chasing invoices, or the person paying bills.

=H7-H6

Result: 4.0171858217

About 4.02: inventory days rose by four between September and October.

A service business with no inventory

If you sell time instead of stock, DIO is 0 and the cycle is DSO - DPO. The cells in M2:N6 hold one month for a design studio: $42,000 of revenue, $51,000 owed by clients, $30,000 of monthly costs and $18,000 of bills not yet paid.

DSO is 37.6 days, DPO is 18.6 days, so the cycle is about 19 days: the studio pays its own bills almost three weeks before clients pay it. Shortening payment terms or invoicing as soon as work is delivered moves this number more than anything else.

A negative cycle is possible too. It means customers pay before you pay suppliers, which is common for shops paid by card on the day.

=N3/N2*N6-N5/N4*N6

Result: 19.042857143

About 19.04: the service business's cash conversion cycle in days.

Common mistakes

Adding DPO instead of subtracting it

DPO is time you get to keep cash. The cycle is DIO + DSO - DPO; adding it makes a business that pays slowly look worse.

Dividing inventory by sales

Inventory and payables are at cost. Divide them by COGS; only receivables go over sales.

Using 30 days for every month

Use =DAY(EOMONTH(month,0)) or the real number of days in the period, and 365 for a year.

Mixing a month of sales with yearly balances

The flow (sales, COGS) and the days must cover the same period. One month of COGS goes with that month's days.

FAQ

What is the cash conversion cycle formula?

CCC = DIO + DSO - DPO. DIO = inventory / COGS x days, DSO = receivables / sales x days, DPO = payables / COGS x days.

How do I calculate the cash conversion cycle for a year?

Use the year's sales and COGS, the average balances across the year and 365 days. For the six months here, =AVERAGE(D2:D7)/SUM(C2:C7)*SUM(G2:G7) gives the inventory part, and the full cycle comes out at about 40.5 days.

Is a shorter cash conversion cycle better?

Usually it means less cash tied up, but how it got shorter matters. Paying suppliers late or running out of stock both shorten it and both have costs. Compare it with your own past months first.

Can the cash conversion cycle be negative?

Yes. If customers pay before you pay suppliers, DPO is bigger than DIO + DSO and the result is below zero.

Functions used

Related guides

Updated November 6, 2026