How do you calculate 'true' LTV when you have variable churn by cohort age, and some customers never expand?

Question

Pulse RevOps · The Machine · Accepted Answer

## Brief
Cohort-based LTV = ARPU × cohort retention curve ÷ discount rate. Don't use blended churn. Plot retention by age, calculate LTV per cohort, weight by composition. Variable expansion = manual scenario tables.

## Detail

Standard LTV formula (ARPU ÷ monthly churn) assumes flat churn forever. Reality: churn is highest in months 1-3, then stabilizes. Some cohorts expand; others don't. Here's how to model actual LTV:

**Step 1: Build Cohort Retention Curves**
Track each cohort monthly. Plot retention degradation:
- Cohort A (2024): Month 1 100%, Month 2 95%, Month 3 92%, Month 6 85%, Month 12 78%
- Cohort B (2025): Month 1 100%, Month 2 96%, Month 3 93%, Month 6 87%, Month 12 80%

Don't blend; retention improves over time as your product matures.

**Step 2: Estimate Expansion by Cohort**
Some cohorts never expand (SMB self-serve). Enterprise cohorts expand 15-25% annually. Track separately:
- Cohort A (SMB): 0% expansion, $1K ARPU
- Cohort B (enterprise): 20% expansion, $10K ARPU, +$2K annual uplift

**Step 3: Calculate Cohort LTV**
Formula: `LTV = Sum of (ARPU × retention_month) + expansion_upsells`

Example (24-month view, no discount rate):
```
Cohort A (SMB, $1K ARPU, 0% expansion):
Month 1-12: $1K × (100% + 95% + 92% + ... + 78%) = $1K × 10.8 = $10.8K
Month 13-24: $1K × (77% + 75% + ... + 68%) = $1K × 8.9 = $8.9K
Total LTV (24m): $19.7K

Cohort B (Enterprise, $10K ARPU, 20% expansion/year):
Month 1-12: $10K × 12 months baseline + $2K expansion (month 9+) = $122K
Month 13-24: $11.2K × 12 months (expanded ARPU) = $134K
Total LTV (24m): $256K (2.5x higher due to expansion)
```

Bridge Group research: Companies with **variable expansion by segment** show **50-100% LTV variance** across cohorts. Blended average hides this.

**Step 4: Weight by Current Cohort Mix**
If your revenue is 60% SMB (LTV $19.7K) and 40% enterprise (LTV $256K):
```
Blended LTV = (0.60 × $19.7K) + (0.40 × $256K) = $11.8K + $102.4K = $114.2K
```

**Discount Rate Adjustment** (for present value):
If you apply 10% annual discount rate (investors expect capital return):
```
Present Value LTV = LTV ÷ (1 + discount_rate)^n
Cohort A PV: $19.7K ÷ 1.10^2 = $16.3K
Cohort B PV: $256K ÷ 1.10^2 = $211.6K
Blended PV: $127.9K
```

This is the LTV you should use for board reporting; it's more conservative than un-discounted.

```mermaid
flowchart TD
    A["Build Cohort Retention"] --> B["Track 12-Month Curve"]
    B --> C["Measure Expansion By Segment"]
    C --> D["Calculate Per-Cohort LTV"]
    D --> E{"SMB Cohort"}
    D --> F{"Enterprise Cohort"}
    E -->|"$1K ARPU, 0% expansion"| G["LTV = $19.7K"]
    F -->|"$10K ARPU, 20% expansion"| H["LTV = $256K"]
    G --> I["Weight by Mix"]
    H --> I
    I --> J["Blended LTV = $114.2K"]
    J --> K["Apply Discount Rate"]
    K --> L["PV LTV = $94.2K (10% discount)"]
    L --> M["Use for Board Reporting"]
```

**Operator moves**:
- Build cohort retention in Excel or BI tool (Tableau, Looker); don't hand-calculate
- Segment LTV by product line (if multi-product), geography, and buyer persona
- Run sensitivity: If churn increases 5pp, how does LTV change?
- Board narrative: "Our LTV:CAC is 4.2:1 using present-value LTV with segment-specific expansion. This accounts for variable churn by cohort and actual expansion patterns."
- Flag if any cohort has LTV:CAC <2.5:1; investigate unit econ risk

TAGS: LTV,cohort-analysis,churn-modeling,expansion-revenue,board-metrics

How do you calculate 'true' LTV when you have variable churn by cohort age, and some customers never expand?

Brief

Detail

What does the score mean?