How to Calculate Customer Lifetime Value in Google Sheets (Free Template Inside)

I've built LTV models for 20+ subscription businesses, and most founders get it wrong. They use a single formula that ignores churn timing.

Here's the right way to calculate LTV in Google Sheets — step by step.

Why Simple LTV Formulas Fail

Most templates use: LTV = ARPU × 1/Churn Rate

That assumes constant churn for life. Reality? Churn compounds. A customer who lasts 12 months is statistically more loyal in month 13.

The 6 Models That Actually Work

After 3 years of iteration, I settled on 6 LTV models that cover every business model:

1. Simple — ARPU × Gross Margin / Churn (for quick estimates)
2. Cohort-based — Track actual retention by signup month
3. ARPU-driven — Factoring in upsells and price changes
4. Churn-adjusted — Accounts for churn decay over time
5. DCF — Discounted cash flow for long subscription lives
6. Multi-segment — Compare premium vs. basic customers

Each uses different Google Sheets formulas. For the cohort model: =AVERAGEIFS(retention_range, cohort_month, current_month-1)

The Sensitivity Analysis That Changes Decisions

The most valuable piece? A sensitivity table showing what 1% change in churn does to your LTV.

For a $49/mo SaaS with 5% churn:

• 4% churn LTV: $1,225 (37% higher)
• 6% churn LTV: $816 (8% lower)

That $1 churn reduction = 37% more value per customer. Worth optimizing retention before acquisition.

Want the Complete Template?

I packaged all 6 models with input forms, 8 charts, and a cohort tracker into a Google Sheets calculator. Grab it here: Customer LTV Calculator — Google Sheets

No signup needed, instant copy to your Drive. Built for founders who need answers in 5 minutes.