Calculate and forecast long-term customer value and revenue potential
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Lifetime value is quoted far more often than it is calculated correctly. This template computes it from actual cohort retention and gross margin, not from an assumed churn rate applied to revenue.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Cohort | Size | M1 | M6 | M12 | LTV |
| 2 | 2025-Q1 | 184 | 100% | 71% | 58% | 412 |
| 3 | 2025-Q2 | 212 | 100% | 68% | 54% | 388 |
| 4 | 2025-Q3 | 241 | 100% | 74% | 61% | 441 |
| 5 | 2025-Q4 | 266 | 100% | 77% | — | — |
Why each one is written the way it is
=COUNTIFS(Cust[Cohort],[@Cohort],Cust[LastActive],">="&EDATE([@Start],6))/[@Size]
Six-month retention measured from the customer table, not assumed. Cohort retention is almost never the smooth curve a single churn rate implies.
=[@ARPU]*[@[Gross margin]]/[@[Monthly churn]]
The classic LTV formula — useful as a sanity check, misleading as a primary measure, because it assumes churn is constant over the customer's life. It is not; it is highest early.
=SUMPRODUCT(Retention[Rate],Cohort[ARPU])*GrossMargin
LTV from the observed retention curve, which is the number to plan against. It is usually lower than the formula version for young businesses and higher for mature ones.
=[@LTV]/[@CAC]
LTV to CAC ratio. Below 3 the model is fragile; above 5 you are probably underspending on acquisition.
A worked example with real numbers
A subscription business used a 3% monthly churn rate and calculated a €680 LTV, justifying €220 of acquisition cost. Cohort data showed churn at 9% in the first three months and 2% thereafter — the blended rate was right on average and wrong for the shape. True LTV was €412, and CAC needed to fall below €140. Cutting the two worst-performing channels achieved that without reducing volume much.
Tab by tab
| Tab | Contents |
|---|---|
| Cohorts | Retention by month since acquisition. |
| Customers | Acquisition date, revenue and last active date. |
| LTV | Cohort-based LTV alongside the formula version. |
| CAC | Acquisition spend by channel and period. |
| README | Why cohort LTV and formula LTV differ. |
What is included, and what to look at next
Specific to this workbook, not generic
Because it assumes a constant churn rate. Almost every business loses customers fastest in the first few months, and a blended rate overstates the value of the ones who stay.
At least twelve months, ideally twenty-four. With less, you are extrapolating a retention curve from its steepest section and will overestimate.
Gross margin, always. Revenue LTV compared against CAC tells you nothing about whether you can afford the customer.
"This calculator has revolutionized how we value and segment our customers. The insights have helped us optimize our acquisition and retention strategies."