Building a SaaS business without tracking churn and LTV is like flying blind. But you don't need expensive BI tools – Google Sheets can handle it beautifully.
In this article, I'll show you a simple framework to calculate:
- Monthly churn rate (cohort-based)
- Customer lifetime (months)
- Lifetime Value with discount rate
- Sensitivity analysis for pricing decisions
The Core Formulas
Churn Rate = Customers lost in month / Customers at start of month
Customer Lifetime = 1 / Churn Rate (in months)
LTV = ARPU / (Churn Rate + Discount Rate/12)
Step 1: Set Up Inputs
Create a sheet with these inputs:
- ARPU: $50
- Monthly Churn: 5%
- Discount Rate: 10% annual
- Starting Customers: 1,000
- Growth Rate: 10% new subs/month
Step 2: Build a Cohort Tracker
Month 1: 1,000 start → 50 churn (5%) → 100 new → 1,050 end → MRR = $52,500
Month 2: 1,050 start → 53 churn → 105 new → 1,103 end → MRR = $55,125
Step 3: Calculate Key Metrics
With 5% monthly churn, customer lifetime is 20 months. LTV = $50 / 0.05 = $1,000. If you discount at 10% annual, LTV drops to $850. Now you know the real value of your customers.
Sensitivity: What If Churn Improves?
If you reduce churn from 5% to 3%: lifetime jumps from 20 to 33 months, LTV rises from $1,000 to $1,667. That's the power of retention.
Get the Ready-Made Template
Instead of building this from scratch, grab the complete SaaS Churn & LTV Calculator – Google Sheets template. It includes cohort tracking, sensitivity tables, and a dashboard – ready to use with your own data.
Top comments (0)