How to Calculate Customer Acquisition Cost From a Spreadsheet (2026)
Customer acquisition cost is total sales and marketing spend for a period divided by the number of new customers acquired in that period. The formula takes ten seconds. Getting a number you can defend takes most of a day, because the spend lives in one system and the customers live in another.
The gap between those two systems is where every argument about CAC actually happens. Which costs count, which customers count, and how you handle the fact that money spent in March produces customers in May.
This guide covers why the calculation is harder than it looks, three ways teams work around it, and where each one stops being defensible.
Why customer acquisition cost is harder than the formula suggests
The spend and the customers live in different systems
Ad spend sits in each platform's reporting. Content and tooling costs sit in accounting. Salaries sit in payroll. New customers sit in the CRM or the billing system.
Nothing joins them automatically. Someone exports four or five files and stacks them by month, and immediately hits the mapping problem. The ad platform reports by campaign, accounting reports by cost centre, and the CRM reports by lead source. A campaign name and a lead source label are rarely the same string, so channel-level attribution needs a translation table that someone maintains by hand.
The lag problem nobody documents
Money spent in March does not produce customers in March. Depending on your sales cycle, it produces them in April, May or the following quarter.
Divide March spend by March customers and you are dividing one month's investment by a previous month's returns. In a steady business the error mostly cancels out. In a business that just doubled its budget, the same arithmetic makes CAC look wonderful for a month and terrible three months later. Neither number reflects reality.
Nobody agrees on what counts as spend
Ad spend obviously counts. Do the salaries of the marketing team? The sales team's commission? The share of a content writer's time that goes to acquisition rather than retention? The design tool subscription?
Every one of these is defensible either way. What is not defensible is changing the answer between quarters, which happens constantly because the decision was never written down.
What this costs you
A benchmark you cannot compare against. Published CAC benchmarks assume fully loaded costs including salaries. If yours is ad spend only, you look dramatically more efficient than you are, and any comparison is meaningless.
Channel decisions made on the wrong number. Blended CAC hides the fact that one channel costs three times another. Budget then gets moved on intuition, because the data as assembled cannot answer the question being asked of it.
A quarterly argument that repeats. Because the scope decision is not recorded, each recalculation reopens it. That is a recurring meeting rather than a one-time cost, and it tends to be attended by expensive people.
The workarounds people try
Option 1: A single blended CAC
Add all sales and marketing spend for the period, divide by all new customers. One number, quick, and impossible to dispute on methodology because there is barely any.
This is the right choice for a board slide and for tracking direction over time. It cannot tell you where to move budget, because it deliberately averages away the differences between channels.
Option 2: Channel-level CAC with a mapping table
Build a sheet mapping each campaign name and cost line to a channel. Then use SUMIFS to total spend per channel, with a matching count of new customers by lead source.
This answers the budget question, and it is the version most teams actually need. The maintenance cost is real. New campaigns appear every month, and an unmapped one silently drops out of the total, making that channel look cheaper than it is.
Option 3: Cohort-based CAC
Group customers by the month they were acquired, then attribute spend to the period that generated them rather than the period it was booked in. This is the only approach that handles the lag honestly.
It is also the most work. You need a defensible lag assumption per channel, and you have to hold several months open before any figure settles.
Where all three hit the same ceiling
Each of these produces a number once you have made the decisions. The decisions are the hard part, and they are not arithmetic.
Which lag to assume. Whether a channel's cost is rising or the customer mix simply shifted. Whether an unmapped campaign matters or is noise. Those judgments require looking at the actual distribution of the data, not applying a formula to it. That is also where the calculation quietly goes wrong, because a formula returns a number regardless of whether the inputs made sense.
How to calculate customer acquisition cost with Powerdrill Bloom
Step 1: Upload your spreadsheet
Upload the spend exports and the customer list together. Powerdrill Bloom profiles each file, so mismatched campaign names, differing date formats and missing months are visible before you attempt to join them.
Step 2: Describe the calculation in natural language
State the scope explicitly: total these spend categories by month, count new customers by acquisition month, and divide. Ask which campaigns failed to map to a channel rather than discovering it from a total that looks too good. Then test the lag — compare the same period with a one-month and two-month offset and see how much the answer moves.
Step 3: Export the chart, report, or deck
Take out the CAC trend by channel, a table of the underlying spend and customer counts, or a written summary for the quarterly review.
Why this beats rebuilding the calculation each quarter
| Spreadsheet route | Powerdrill Bloom | |
|---|---|---|
| New campaigns appear | Update the mapping table by hand | Flagged as unmapped at upload |
| Testing a different lag | Rebuild the whole sheet | Ask for the offset |
| Scope decision | Rarely documented | Stated in the request, visible later |
| Channel breakdown | Separate formula per channel | One question |
The row that changes behaviour is the second one. When testing a lag assumption costs a sentence rather than an afternoon, you test three and pick the one that holds up. That is the difference between a defensible number and a plausible one.
Best practices
Write the scope into the file. List exactly which cost categories are included. This single line prevents the most common argument and makes quarter-on-quarter comparison valid.
Report blended and channel-level together. Blended tracks direction; channel-level drives decisions. Publishing only one guarantees somebody uses it for the wrong purpose.
State your lag assumption. Even "we assume no lag" is better than silence, because it tells the reader how to interpret a sudden improvement.
Show unmapped spend as its own line. Never let it disappear. An unmapped row with a visible total makes the gap impossible to overlook.
Pair it with lifetime value. CAC alone says nothing about whether acquisition is working. The ratio against customer lifetime value is the number that carries a decision.
Conclusion
Calculating customer acquisition cost from spreadsheets is a scope-and-mapping problem, not a maths problem. Blended CAC for direction, channel-level with a maintained mapping table for budget decisions, cohort-based when your spend is changing fast enough that lag distorts everything.
If the quarterly rebuild has become the reason nobody re-checks the number, try Powerdrill Bloom on this quarter's exports. Related reading: our AI report generator page and the guide to turning a CRM export into a pipeline report. See also building a marketing funnel report without SQL and building an interactive sales dashboard from a CSV.
Frequently asked questions
What is the formula for customer acquisition cost?
Divide total sales and marketing spend for a period by the number of new customers acquired in that period. The formula is simple; the difficulty is agreeing which costs belong in the numerator and which customers belong in the denominator.
Should salaries be included in CAC?
Fully loaded CAC includes the salaries of staff working on acquisition, and that is what published benchmarks assume. Ad-spend-only CAC is a legitimate internal metric, but label it clearly so nobody compares it against an external figure.
How do I calculate CAC by channel in Excel?
Build a mapping table linking campaign names and cost lines to channels, total spend per channel with SUMIFS, then count new customers by lead source. Keep an unmapped row visible so campaigns missing from the table do not vanish.
What is a good customer acquisition cost?
There is no universal figure, because it depends on the price and lifetime value of what you sell. The useful test is the ratio of lifetime value to CAC. It tells you whether each acquired customer eventually pays back the cost of acquiring them.
Why does my CAC jump around month to month?
Usually the lag between spend and acquisition. Money spent this month produces customers over the following weeks. A budget change therefore makes CAC look artificially good or bad until the effect works through. Cohort-based attribution smooths this out.