LTV / CAC / Payback Model

What this is

A Google Sheets model that calculates Customer Lifetime Value (LTV), Customer Acquisition Cost (CAC), the LTV:CAC ratio and payback period. It comes pre-filled with sample data, and every formula has a plain-English explanation next to it.

I built it for founders and marketing leads who have to make budget decisions without a finance background. Every finance term is defined, and every formula is visible.

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

It shows both blended CAC (total spend / total customers) and channel CAC (channel spend / channel customers), because budget allocation depends on the gap between the two.

Tab 4: Unit Economics Summary

This tab pulls the other numbers into one summary:

• 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%)

I show founders this table when they ask “what happens if we improve retention by 10%?” The answer is always bigger 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

The three sit side by side, so you can see the range of outcomes at a glance.

How to use it

1. Make a copy of the spreadsheet.
2. Start with Tab 1 and fill in your actual numbers (or your best estimates).
3. Review Tab 4. Your unit economics summary updates automatically.
4. Play with the inputs on Tab 5 to see which levers move the ratio most.
5. Use Tab 6 to model scenarios before you make budget decisions.

Common traps to avoid

• Use gross margin, not revenue. LTV should reflect profit. The spreadsheet handles this as long as you enter your margin correctly.
• Include all acquisition costs. Ad spend is only part of CAC. Salaries, tools, agency fees and content production count too, and leaving them out is the most common error I see.
• 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