LTV / CAC / Payback Model
A pre-filled spreadsheet with formulas, sample data, and a sensitivity table. For founders without finance backgrounds.
What this is
A Google Sheets model that calculates Customer Lifetime Value (LTV), Customer Acquisition Cost (CAC), LTV:CAC ratio, and payback period — pre-filled with sample data and plain-English explanations next to every formula.
Built for founders and marketing leads who need to make budget decisions but don’t have a finance background. No finance jargon without definitions. No hidden formulas.
What is inside
Tab 1: Input Dashboard
A single sheet where you enter your key numbers:
- Average revenue per customer per month
- Gross margin percentage
- Average customer lifespan (in months)
- Monthly marketing spend by channel
- New customers acquired per month by channel
Everything else is calculated automatically.
Tab 2: LTV Calculation
Three methods of calculating LTV, so you can pick the one that matches your data quality:
| Method | When to use | Formula |
|---|---|---|
| Simple | Early-stage, limited data | Avg revenue × Gross margin × Avg lifespan |
| Cohort-based | 6+ months of data | Revenue per cohort tracked over time |
| Predictive | 12+ months of data | Uses retention curves to project future value |
Each method shows the formula in the cell and a plain-English explanation in the adjacent column.
Tab 3: CAC by Channel
Breaks down customer acquisition cost per channel:
- Google Ads
- Meta Ads
- SEO (organic)
- Referral
- Content marketing
- Outbound/sales
- Partnerships
Includes both blended CAC (total spend / total customers) and channel CAC (channel spend / channel customers) — because the difference matters for budget allocation.
Tab 4: Unit Economics Summary
The dashboard that ties it all together:
- LTV:CAC ratio — with a colour-coded indicator (red < 3:1, yellow 3:1–5:1, green > 5:1)
- Payback period — months until a customer’s revenue covers their acquisition cost
- Contribution margin per customer — what’s left after COGS and acquisition cost
- Break-even customer count — how many customers you need to cover fixed costs
Tab 5: Sensitivity Analysis
A two-variable data table that shows how your LTV:CAC ratio changes when you adjust:
- Customer lifespan (rows: 6, 12, 18, 24, 36 months)
- Monthly churn rate (columns: 2%, 5%, 8%, 10%, 15%)
This is the table I show founders when they ask “what happens if we improve retention by 10%?” — the answer is always more dramatic than they expect.
Tab 6: Scenario Planner
Three pre-built scenarios:
- Conservative: Higher churn, lower conversion, higher CAC
- Base case: Your current numbers
- Optimistic: Lower churn, higher conversion, better CAC
Side-by-side comparison so you can see the range of outcomes.
How to use it
- Make a copy of the spreadsheet
- Start with Tab 1 — fill in your actual numbers (or best estimates)
- Review Tab 4 — your unit economics summary updates automatically
- Play with Tab 5 — adjust the sensitivity table inputs to see what levers matter most
- Use Tab 6 to model scenarios before making budget decisions
Common traps to avoid
- Don’t use revenue, use gross margin. LTV should reflect profit, not revenue. The spreadsheet handles this if you fill in your margin correctly.
- Include all acquisition costs. CAC isn’t just ad spend — it’s salaries, tools, agency fees, and content production. Undercounting CAC is the most common error.
- Cohort data beats averages. If you have 6+ months of data, use the cohort-based LTV tab instead of the simple calculation. Averages hide the shape of your retention curve.
Who this is for
- Founders making ad spend or hiring decisions
- Marketing leads justifying budget to a CFO
- Agency operators calculating their own unit economics
- Students learning SaaS/D2C financial fundamentals
Version history
| Version | Date | Notes |
|---|---|---|
| 2.0 | May 2026 | Added scenario planner, sensitivity analysis, cohort LTV |
| 1.0 | December 2025 | Initial release with simple LTV/CAC model |
