How to Calculate SaaS Unit Economics in Google Sheets
As a SaaS founder or investor, you need to know your numbers cold. MRR, CAC, LTV, churn — these metrics decide whether your startup grows or dies. But tracking them manually in spreadsheets is painful.
Here's the exact setup I use to track unit economics for SaaS businesses. I've packaged it as a ready-to-use Google Sheets template.
Step 1: Raw Data Structure
Create columns: Customer Name | Month | Subscription Price | Acquisition Source | Acquisition Cost | Start Date | Churn Date
Example: Acme Corp | 2024-01 | $500 | Google Ads | $200 | 2024-01-01 |
Pro tip: Use data validation to ensure consistency in Acquisiton Source names.
Step 2: MRR Calculation
Use SUMIFS to aggregate monthly recurring revenue: =SUMIFS(RawData!C:C, RawData!B:B, A2) where A2 contains the month label.
Step 3: CAC Per Channel
=SUMIF(RawData!E:E, ">0") / COUNTIF(RawData!F:F, ">0") gives overall CAC. Then use AVERAGEIF per source.
Step 4: LTV Projection
Standard formula: Monthly Revenue * (1 - (1 + Discount Rate)^(-60)) / Discount Rate gives 5-year LTV at 10% discount.
Step 5: Dashboard
Pull key numbers: MRR, MoM Growth, CAC, LTV, LTV:CAC ratio, Churn rate, Payback period. Use conditional formatting — green for LTV:CAC >3, red for <1.
Want the Ready-Made Template?
I built a complete Google Sheets template with all formulas pre-loaded, sample data for 10 SaaS businesses, and a dashboard with conditional formatting. Grab it here: https://microtoolsb2b.gumroad.com/l/... (free version available with limited rows).
This template has saved me hours of manual spreadsheet work. Hope it helps you too!
Top comments (0)