How to Build a Unit Economics Model in Excel

Unit economics strips a business down to a single customer: what it costs to acquire one, how much margin that customer throws off, and how long they stay. The core outputs are CAC, LTV, the LTV to CAC ratio, and payback period. Built cleanly in Excel, the model tells you whether growth is profitable before you scale spend behind it.

What a unit economics model does

Unit economics measures profitability per customer rather than in aggregate. Customer acquisition cost (CAC) is the fully loaded sales and marketing spend divided by customers acquired. Lifetime value (LTV) is the contribution margin a customer generates over their expected life.

It fits a model as the bridge between marketing spend and profit. If the LTV to CAC ratio is healthy and payback is short, the income statement growth has a sound foundation. If not, the model shows that buying revenue destroys value, which no amount of scale fixes.

Build it step by step

Lay out the inputs as a small block: monthly revenue per customer, gross margin, monthly churn, and acquisition spend. Then derive the four headline metrics below them.

Drive contribution margin per customer first, because both LTV and payback depend on it.

1. Contribution margin per customer is =monthly_revenue * gross_margin - variable_cost_to_serve.
2. Average customer lifetime in months is =1 / monthly_churn_rate.
3. LTV is =contribution_margin_per_customer * customer_lifetime.
4. CAC is =sales_and_marketing_spend / new_customers_acquired.
5. Payback period in months is =CAC / contribution_margin_per_customer.

=1000/300 gives an LTV to CAC ratio of 3.3, comfortably above the 3.0 rule of thumb.

The formulas that tie it together

Contribution margin per customer is the engine: =monthly_revenue * gross_margin - variable_cost_to_serve. Use contribution margin, not revenue, because acquiring a customer only pays back the margin they generate, not their top line.

Customer lifetime is =1 / monthly_churn_rate, so a 4 percent monthly churn implies a 25-month life. LTV then equals =contribution_margin * lifetime. The two summary ratios are LTV / CAC and the payback period =CAC / contribution_margin, expressed in months. A 3.0 or higher LTV to CAC and a payback under 12 months are common health benchmarks, though they vary by business.

• Contribution margin: =monthly_revenue * gross_margin - variable_cost.
• Lifetime: =1 / monthly_churn_rate.
• LTV: =contribution_margin * lifetime.
• Payback (months): =CAC / contribution_margin.

Pitfalls and what reviewers check

The classic error is computing LTV on revenue instead of contribution margin, which overstates value by the entire cost base. A second error is loading CAC with only paid media while excluding sales salaries and tooling, which understates the true cost to acquire.

Reviewers check that LTV uses margin not revenue, that CAC is fully loaded, and that the churn-to-lifetime conversion is 1 / churn rather than a hardcoded guess. They also look for stray hardcoded numbers inside the metric formulas, since a model that mixes inputs and calculations cannot be sensitized cleanly when assumptions change.