Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate customer lifetime value in Excel and Google Sheets
Excel & Google Sheets
=A2*B2*C2✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| AvgPurchase | FreqPerYr | LifespanYrs | CLV |
| 85 | 3 | 4 | =A2*B2*C2 |
| 120 | 2 | 5 | =A3*B3*C3 |
| 40 | 6 | 2 | =A4*B4*C4 |
=A2*B2*C2→1020
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Organize your inputs in a row: A2 = average purchase value (e.g. 85), B2 = average purchase frequency per year (e.g. 3), C2 = average customer lifespan in years (e.g. 4).
- 2In D2, calculate CLV: =A2*B2*C2. For this example: 85 × 3 × 4 = 1020.
- 3To compute per-row CLV for a table of customer segments with different values, copy D2 down the table.
- 4For a margin-adjusted CLV, multiply by the gross margin percentage: =A2*B2*C2*E2 where E2 contains the margin (e.g. 0.40 for 40%).
- 5For a discounted CLV using present value of an annuity, use: =A2*B2*((1-(1+D2)^(-C2))/D2) where D2 is the annual discount rate.
Tips
- CLV is only as good as its inputs. Average purchase value from a small sample or average lifespan from a young cohort will underestimate or overestimate CLV significantly — use the largest historical dataset available.
- Track predicted vs actual CLV per cohort over time. If actual churn is faster than assumed lifespan, your CLV model needs recalibration.
- For subscription businesses, CLV simplifies to: Monthly MRR × (1 / Monthly Churn Rate). A 2% monthly churn rate means an average lifespan of 50 months.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What is the difference between historical CLV and predictive CLV?
Historical CLV sums actual revenue from a customer to date. Predictive CLV (the formula above) projects future revenue based on behavioral averages. Use historical CLV to evaluate past segments and predictive CLV to set acquisition budgets.
How do I calculate CLV when customers have very different purchase patterns?
Segment customers into cohorts (e.g. by acquisition channel or product tier) and calculate separate CLV per segment rather than using a single average, which would obscure the variance.
Should CAC be subtracted from CLV?
CLV and CAC are separate metrics. The CLV:CAC ratio (target ≥ 3:1) is the key health indicator — you do not subtract CAC from CLV but compare them as a ratio.