Customer Lifetime Value Calculator
Calculates customer lifetime value using three methods: simple (average revenue times lifespan), cohort-based (tracking actual retention curves), and DCF (discounting future revenue to present value). Compares results across methods and segments.
Business - Customer Lifetime Value Calculator.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- No reliable estimate of what a customer is worth over their entire relationship
- Difficulty choosing between simple, cohort, and DCF-based CLV calculations
- Inability to compare CLV across customer segments or acquisition channels
- Unclear how much to spend on acquiring a customer (CAC vs. CLV ratio)
- No way to model how retention improvements affect lifetime value
Who This Is For
- Marketing directors setting acquisition budget limits
- Growth managers optimizing channel spend against CLV
- Product managers evaluating retention feature investments
- CFOs building long-term revenue models
Inputs
- $Average Revenue per Customer per Period
- #Average Customer Lifespan (periods)
- %Gross Margin %
- %Retention Rate
- %Discount Rate
- $Customer Acquisition Cost
Outputs
- Simple CLV
- Cohort-Based CLV
- DCF-Adjusted CLV
- CLV-to-CAC Ratio
- Months to Recover CAC
- Net CLV (after CAC)
How Calculations Work
Simple CLV multiplies average revenue per period by lifespan and gross margin. Cohort CLV uses the retention rate to model how revenue decays period over period and sums the retained revenue. DCF CLV discounts each future period's retained revenue back to present value. The CLV-to-CAC ratio divides CLV by acquisition cost to evaluate channel efficiency.
Example Use Case
Scenario: A subscription business: $100/month average revenue, 24-month average lifespan, 70% gross margin, 92% monthly retention, 10% annual discount rate, $350 CAC.
Result: Simple CLV: $1,680. Cohort CLV: $875 (retention decay reduces it significantly). DCF CLV: $812. CLV-to-CAC ratio: 2.3x (using DCF CLV). CAC payback: 5 months. The business is healthy but below the 3x benchmark.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
Which CLV method should I use?
Use simple CLV for quick estimates when you have limited data. Use cohort CLV when you have retention data. Use DCF CLV for the most accurate financial view, especially when presenting to investors.
What is a good CLV-to-CAC ratio?
3:1 or higher is the commonly cited benchmark. Below 1:1 means you lose money on every customer. Between 1:1 and 3:1 is viable but needs improvement.
Why is cohort CLV lower than simple CLV?
Simple CLV assumes the customer stays for the full average lifespan at full revenue. Cohort CLV accounts for the fact that customers churn gradually, so cumulative revenue is lower.
Can I calculate CLV by acquisition channel?
Yes. Enter separate rows for each channel with channel-specific retention rates and revenue. The model will calculate CLV per channel.
How often should I recalculate CLV?
Quarterly at minimum. Recalculate whenever you change pricing, experience significant churn shifts, or enter a new market.
Download Customer Lifetime Value Calculator
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.