How Do You Calculate CAC Payback in a Spreadsheet?

How do you calculate CAC payback in a spreadsheet?

Divide the cost to acquire a customer by the gross margin that customer generates each month. The result is the number of months it takes to earn back what you spent to win them. In a spreadsheet, you need three inputs per period and channel: sales and marketing spend, new customers won, and average monthly gross margin per new customer.

CAC payback is one of the most useful GTM metrics for a small B2B company, because it answers a practical question: how long is cash tied up before a new customer pays for themselves? A short payback means you can reinvest quickly. A long one means growth eats cash and needs funding or patience.

This tutorial is for a founder or first finance or revenue operations hire who wants a working CAC payback sheet without buying a tool. Google Sheets or Excel is enough, and the whole thing fits on two tabs.

What is CAC payback, exactly?

CAC payback is the number of months of gross margin from a new customer needed to recover the cost of acquiring them. Customer acquisition cost, or CAC, is total sales and marketing spend in a period divided by new customers won in that period. Payback divides that CAC by monthly gross margin per new customer.

Using gross margin rather than revenue matters. Revenue overstates how fast you earn back acquisition cost, because some of every dollar goes to hosting, support, payment fees, and other costs of serving the customer. Gross margin is what is actually left to repay CAC.

The metric is also more forgiving of early-stage noise than a ratio like lifetime value to CAC. Lifetime value depends on a churn rate you may not know yet. Payback only needs what happened this period, which makes it easier to trust when the company is young.

Which data do you need before you start?

You need sales and marketing spend by month, new customers by month, the starting monthly revenue of each new customer, and your gross margin percentage. Spend usually comes from accounting software. Customers and revenue come from your CRM or billing system. Gross margin comes from your income statement or a reasonable estimate.

Include everything it costs to acquire customers: ad spend, tools, events, content, and the salaries of people who spend their time on sales and marketing. Leaving out salaries is the most common mistake and makes payback look far better than it is. If a founder spends half their time selling, include half a reasonable salary.

For new customer revenue, use the monthly recurring revenue at the start of the contract. If customers pay annually, divide the annual contract value by twelve. Do not include expansion revenue that comes later. That belongs in a separate view.

How do you set up the spreadsheet?

Create an inputs tab with one row per month and columns for spend, new customers, new monthly recurring revenue, and gross margin percentage. Then create a calculation tab that pulls from the inputs and computes CAC, monthly gross margin per new customer, and payback in months for each period.

On the inputs tab, keep raw numbers only. Do not put formulas there. That makes it easy to update each month without breaking anything, and easy for someone else to check your sources.

On the calculation tab, the first formula is CAC: spend divided by new customers. The second is average new monthly recurring revenue per customer: new monthly recurring revenue divided by new customers. The third is monthly gross margin per customer: that average multiplied by gross margin percentage. The last is payback: CAC divided by monthly gross margin per customer.

Wrap each division in an error check so months with zero new customers show a blank instead of an error. IFERROR in Google Sheets or Excel handles this cleanly.

Should you calculate payback by channel?

Yes, once the blended number works. Blended payback tells you whether the business is healthy overall. Channel payback tells you where to put the next dollar. Add a channel column to the inputs tab, split spend and new customers by channel, and compute payback for each one separately.

Splitting spend by channel is straightforward for ads and tools. Splitting salaries is harder. A simple approach is to allocate people's time by channel based on how they actually spend their week. It will not be perfect, but it is far better than ignoring salary cost entirely.

Attributing new customers to channels depends on your CRM data. If you track original source on every deal, use that. If you do not, start tracking it now and accept that the first few months of channel data will be rough. I explained why payback period should decide your channel mix, and channel-level payback is the number that makes that decision possible.

How do you smooth out noisy months?

Use a trailing three-month total instead of single months. Small companies win a handful of customers a month, so one big deal or one quiet month swings payback wildly. Summing spend, customers, and new revenue over the last three months and then calculating payback gives a much steadier trend.

Add trailing columns next to the monthly ones on the calculation tab. Each trailing cell sums the current month and the two before it. Then run the same CAC and payback formulas on the trailing totals.

Also consider the lag between spend and results. Money spent on content or events this month may produce customers months later. A trailing window partly accounts for that. For channels with long lags, you can shift spend back by a month or two before matching it to customers, as long as you write down the assumption.

How do you read the result?

Compare payback to how long customers typically stay and to how much cash you have. If payback is shorter than your typical customer lifetime by a wide margin, acquisition is creating value. If it is close to or longer than customer lifetime, you are spending more to win customers than they return. Watch the trend, not one number.

There is no universal good number. Companies with long contracts and low churn can tolerate longer payback than companies with monthly plans and high churn. What matters is whether payback is improving or getting worse, and whether your cash position can support it.

If you sell services rather than software, the picture changes, because margins and contract patterns differ. I covered that in how to read CAC payback when you sell services, which is worth reading if your revenue includes project work.

What mistakes make CAC payback misleading?

Leaving out salaries, using revenue instead of gross margin, mixing expansion revenue into new revenue, and reading single months. Each of these makes payback look better or noisier than it really is. The other common mistake is changing definitions month to month, which makes the trend meaningless.

Write your definitions at the top of the calculation tab. What counts as sales and marketing spend? What counts as a new customer? Which gross margin figure do you use? Keeping these stable is what makes the sheet trustworthy over time.

Finally, connect payback to your pipeline view. If you already forecast pipeline, my guide to building a weighted pipeline forecast in a spreadsheet pairs well with this one. Pipeline tells you what is coming. Payback tells you what it costs.

What should you do next?

Pull the last twelve months of sales and marketing spend, including salaries, plus new customers and their starting monthly revenue. Build the two-tab sheet, calculate blended payback on a trailing three-month basis, then split by your two or three biggest channels. Write your definitions down and update the inputs monthly.

Review the trend with whoever owns your budget. Payback is most useful when it changes where money goes next quarter.

If you want help building a CAC payback model from your CRM and accounting data, or automating the monthly update so the sheet stays current, reach out. Let's chat about knowing exactly what your growth costs.