Customer LTV, CAC and payback months in a spreadsheet
Work out monthly gross profit per customer, lifetime value, the LTV to CAC ratio and CAC payback months with formulas, a worked example and clear limits.
Customer lifetime value (LTV) is the gross profit a customer produces before leaving. Customer acquisition cost (CAC) is what it cost to win that customer. Comparing them takes four inputs per segment: monthly revenue per customer, gross margin, monthly churn and CAC.
How do you calculate LTV in a spreadsheet?
Monthly gross profit is revenue times gross margin. Lifetime value divides that by monthly churn, because a churn rate of 2.5 percent implies an average life of 1/0.025 = 40 months.
For a fictional mid-market segment with $200 monthly revenue per customer, an 80 percent margin and 2.5 percent churn:
| Step | Formula | Result |
|---|---|---|
| Monthly gross profit | =B11*C11 |
$160 |
| Customer LTV | =IF(D11<=0,"",F11/D11) |
$6,400 |
How do you calculate the LTV to CAC ratio?
With CAC of $1,600, the ratio is 6400/1600, or 4.0. Payback months divide CAC by monthly gross profit: 1600/160 = 10 months. Payback ignores churn after month ten, so read it alongside the ratio.
How do segments compare?
| Segment | ARPU | Margin | Churn | CAC | LTV | LTV to CAC | Payback |
|---|---|---|---|---|---|---|---|
| Mid-market | $200 | 80% | 2.5% | $1,600 | $6,400 | 4.0 | 10.0 months |
| Small business | $80 | 75% | 4.0% | $600 | $1,500 | 2.5 | 10.0 months |
| Enterprise | $850 | 82% | 1.5% | $7,500 | $46,467 | 6.2 | 10.8 months |
Small business and mid-market pay back in the same time, yet small business has the weaker ratio because its customers leave sooner. The unit economics calculator holds these rows with the formulas intact.
What do these formulas assume?
- Churn and margin stay constant, with no discounting of future profit.
- Expansion revenue, onboarding cost and sales commissions are excluded unless you include them in the inputs.
- A very low churn rate produces a very large LTV. Cap the customer life at a period you can defend, such as five years.
If cash is the constraint, pair this with the startup runway planner to see how long a payback period can be financed.