Summary: This professional template provides a repeatable, end-to-end LTV calculator specifying clear period/ARPU/margin/churn inputs, the underlying math and DCF-based valuation, a worked example, implementation notes for spreadsheet/SQL/Python, sensitivity analysis, and founder-focused best practices. It recommends computing LTV on a cohort basis with margin adjustment and discounting, explicitly modeling churn and expansion, and using the heuristic LTV:CAC ≥ 3 with CAC payback < 12 months (monthly periods typical for SaaS) to guide acquisition and retention investments.

LTV Calculator Template (Markdown)

Professional, repeatable template for building a Customer Lifetime Value (LTV) calculator. Includes clear inputs, the underlying math, a DCF-based approach, worked example, implementation notes (spreadsheet / SQL / Python), sensitivity guidance, and founder-focused best practices.

1 Executive summary

LTV (Customer Lifetime Value) estimates the present value of future gross profit from a customer (or cohort). Use LTV to evaluate unit economics, set customer acquisition budgets (CAC), and prioritize retention/expansion investments. Always compute LTV on a cohort basis with margin adjustment and discounting for long horizons.

Key rule-of-thumb: target LTV : CAC ≥ 3 and CAC payback < 12 months for typical SaaS/recurring businesses (context-dependent).

2 Definitions and core inputs

• Period: time unit for modeling (monthly, quarterly, yearly). Most SaaS use monthly.
• ARPU (Average Revenue Per User) per period: average revenue per active customer in the period.
• Gross Margin (GM): % gross margin applied to revenue to convert revenue → gross profit. Use contribution margin (exclude CAC).
• Churn rate (c): probability a customer churns in a period (discrete period churn). For cohorts, measure cohort-specific churn.
• Retention rate per period: r = 1 − c.
• Discount rate per period (d): appropriate WACC / opportunity cost per period. For monthly use (annual discount rate)^(1/12) − 1.
• Time horizon T: finite horizon in periods (3–5 years common), or model to infinity using steady-state retention.
• Expansion/Contraction revenue: optional modeled as multiplicative growth in ARPU over time (expansion MRR, upsell).
• Revenue type: recurring vs one-time; one-time should be included separately (often in initial period).

Required input fields (spreadsheet UI):

• period_length = "month" / "quarter"
• ARPU_0 (starting ARPU)
• gross_margin (decimal)
• churn_rate (periodic, decimal)
• discount_rate_annual (decimal)
• time_horizon_periods (integer)
• expansion_rate_periodic (decimal, optional)
• initial_cohort_size (optional)
• CAC (Customer Acquisition Cost)

3 Core formulas (discrete-period, margin-adjusted, non-discounted)

1. Retention per period: r = 1 − c

2. Expected lifetime (periods) under constant discrete churn:

• E[L] = Σ_{t=1..∞} t * c * (1 − c)^{t-1} = 1 / c
  (This is the geometric distribution mean; valid when churn is constant.)

3. Simple (non-discounted) LTV (steady ARPU, margin-adjusted):

• LTV_simple = (ARPU * GM) / c
  (Pay attention: ARPU is per period, c periodic churn. Assumes ARPU constant and no discounting.)

4 DCF / cohort formula (recommended)

Model per-period cash flows and discount them to present value. Let ARPU_t be revenue per customer in period t (can include expansion). Let m = gross_margin. Let r = retention rate per period = 1 − c. Let v = 1 / (1 + d) be the per-period discount factor.

Per-customer LTV (cohort-based DCF):

• LTV = Σ_{t=1..T} (ARPU_t * m * S(t) * v^t)
  where S(t) = probability customer is active in period t = r^{t-1} (for discrete constant churn), and v^t discounts the period-t cash flow to present.

Expanded:

• LTV = Σ_{t=1..T} [ARPU_t * m * r^{t-1} / (1 + d)^t]

If ARPU is constant (ARPU_0) and T → ∞, closed form:

• LTV_DCF_inf = ARPU_0 * m * Σ_{t=1..∞} [r^{t-1} / (1 + d)^t]
  = ARPU_0 * m * (1 / (1 + d)) * Σ_{t=0..∞} [(r / (1 + d))^t]
  = ARPU_0 * m * (1 / (1 + d)) * (1 / (1 − r/(1 + d))) if r/(1 + d) < 1
  = ARPU_0 * m / (1 + d − r)

Note equivalently:

• LTV_DCF_inf = ARPU_0 * m / (d + c)
  because (1 + d − r) = d + (1 − r) = d + c (valid for consistent period units)

This illustrates that discounting increases the effective denominator from c → (d + c).

Important checks:

• If d = 0, LTV_DCF_inf reduces to ARPU_0 * m / c (matches simple formula).
• If r/(1 + d) ≥ 1, infinite sum diverges indicates retention > discount growth (model breakdown) and you must use finite T or model ARPU growth explicitly.

5 Worked example (monthly)

Inputs:

• period = month
• ARPU_0 = $50 / month
• gross_margin m = 0.75
• monthly churn c = 4% → r = 0.96
• annual discount rate = 10% → monthly d = (1.10)^(1/12) − 1 ≈ 0.007974
• time horizon T = 60 months (5 years)

Compute per-period discount v = 1 / (1 + d) ≈ 0.99206

DCF LTV:
LTV = Σ_{t=1..60} [50 * 0.75 * 0.96^{t-1} / (1 + d)^t]

Closed form infinite approximation (if used):
LTV ≈ 50 * 0.75 / (d + c) = 37.5 / (0.007974 + 0.04) ≈ 37.5 / 0.047974 ≈ $781.6

Interpretation: each acquired customer yields ~ $782 present-value gross profit. If CAC =$400 → LTV:CAC ≈ 1.95 (ok, but below ideal 3x). CAC payback months = CAC / (ARPU * m) = 400 / (50 * 0.75) = 400 / 37.5 ≈ 10.67 months.

Spreadsheet-friendly per-row formula (Excel/Sheets) for period t in row:

• Revenue_t_per_user = ARPU_0 * (1 + expansion_rate)^(t-1) [if modeling expansion]
• Survival = r^(t-1)
• DiscountFactor = 1 / (1 + d)^t
• CashFlow_t = Revenue_t_per_user * m * Survival * DiscountFactor
• LTV = SUM(CashFlow_t for t=1..T)

6 Implementation recipes

Spreadsheet (Excel/Sheets)

• Inputs block with: ARPU, GM, churn, discount_annual, period = months, T.
• Compute per-period discount rate: d = (1 + discount_annual)^(1/12) − 1
• Build time series columns: t, ARPU_t, Survival, DiscountFactor, CashFlow, Cumulative LTV
• Use SUM to compute LTV.

Example Excel formulas:

• cell d_monthly: = (1 + discount_annual)^(1/12) - 1
• ARPU_t (row t): = ARPU_0 * (1 + expansion_rate)^(t-1)
• Survival (row t): = (1 - churn)^(t-1)
• DiscountFactor (row t): = 1 / (1 + d_monthly)^t
• CashFlow (row t): = ARPU_t * gross_margin * Survival * DiscountFactor
• LTV: = SUM(CashFlow_range)

Python (numpy)

import numpy as np

def ltv_dcf(arpu0, gross_margin, churn, discount_annual, periods, expansion_rate=0.0):
    d = (1 + discount_annual) ** (1/12) - 1
    r = 1 - churn
    t = np.arange(1, periods + 1)
    arpu_t = arpu0 * (1 + expansion_rate) ** (t - 1)
    survival = r ** (t - 1)
    discount = 1 / (1 + d) ** t
    cashflows = arpu_t * gross_margin * survival * discount
    return cashflows.sum()

SQL (cohort-level ARPU & retention)
Example to compute per-cohort monthly retention and ARPU (Postgres-like pseudocode):

WITH cohort AS (
  SELECT user_id, MIN(date_trunc('month', signup_date)) AS cohort_month
  FROM users
  GROUP BY user_id
),
activity AS (
  SELECT c.cohort_month, date_trunc('month', e.event_date) AS month, count(DISTINCT e.user_id) AS active_users, SUM(e.revenue) AS revenue
  FROM cohort c
  JOIN events e ON e.user_id = c.user_id
  GROUP BY c.cohort_month, date_trunc('month', e.event_date)
),
cohort_metrics AS (
  SELECT cohort_month,
         month,
         active_users,
         revenue,
         revenue::numeric / NULLIF(active_users,0) AS arpu
  FROM activity
)
SELECT * FROM cohort_metrics;

Then convert the cohort_metrics into per-period survival and cashflow and sum with your margin & discount externally or via SQL window functions.

7 Sensitivity & scenario analysis

• Always run sensitivity on churn and ARPU. Small changes in churn can have large LTV impact because churn is in denominator.
• Typical sensitivity matrix:
  • Vary churn ±50% (e.g., from 2% to 6% monthly)
  • Vary ARPU ±20%
  • Vary gross margin ±10 percentage points
  • Vary discount rate (5–20% annual)
• Visualize with tornado chart or heatmap. Show LTV:CAC break-even lines.

8 Pitfalls and limitations

• Using revenue instead of gross profit: must apply gross margin before computing LTV that will compare to CAC.
• Using overall averages rather than cohorts: averages mix vintages with different behaviors; cohort analysis reveals trends and improvements.
• Ignoring expansion (upgrades/upsell) and contraction: Net Revenue Retention (NRR) matters. If NRR > 100% model must include expansion; simple churn-based models under-estimate LTV.
• Short attribution and observation windows bias churn measurement (survivorship bias).
• Assuming constant churn/ARPU forever: use finite horizon or model time-varying churn & ARPU.
• Incorrect discount rate unit: ensure discount rate and churn are in same period units.
• Not modeling non-recurring revenue and installation fees separately.
• Using LTV without comparing to CAC + payback period: LTV alone is insufficient.

9 Best practices for founders (authoritative checklist)

Data & measurement

• Measure cohort LTVs (cohort = acquisition month/week). Track cohort evolution over time.
• Use gross margin (contribution margin) not revenue in LTV.
• Compute both "LTV to horizon" (3/5 year) and "infinite-horizon DCF" with transparent assumptions.
• Measure churn correctly: cohort-based, per-period churn, and distinguish logo churn vs revenue churn.
• Track NRR (Net Revenue Retention) and GRR (Gross Revenue Retention).

Modeling

• Model LTV on a DCF basis when horizon > 12 months. Use per-period discounting.
• Explicitly model expansion/contraction/downsells (NRR) as multiplicative ARPU growth.
• Use finite horizon (3–5 years) for conservative planning; use infinite formula only with clear assumptions.
• Sensitivity test the model; present ranges, not single-point estimates.

Economic decision-making

• Evaluate LTV alongside CAC: target LTV:CAC ≥ 3 for growth funding; > 1 is necessary; < 1 is a failing unit-economic model.
• Monitor CAC payback months. For venture-funded SaaS, target < 12 months; for bootstrapped businesses, longer payback may be acceptable.
• Use LTV marginally: consider incremental LTV when running marketing/retention experiments (incremental gross profit per incremental customer).

Product & growth actions

• Prioritize retention engineering (reduce churn) small % improvements in churn yield outsized LTV gains.
• Increase gross margin by optimizing delivery costs (lower COGS) to boost LTV directly.
• Drive expansion revenue (upsell / cross-sell) to increase ARPU and therefore LTV and NRR.
• Use segmentation: LTV often varies widely by cohort, channel, plan, geography. Invest where LTV:CAC is highest unless scale or strategic reasons justify otherwise.

Governance & reporting

• Publish cohort LTV / CAC / Payback dashboards to leadership and board. Use consistent definitions and timeframes.
• Keep model assumptions explicit and version-controlled (e.g., spreadsheet with named inputs and scenario tabs).
• Recompute LTV after major product, pricing, or cost-of-delivery changes.

Benchmarks (rules-of-thumb)

• LTV:CAC:

  • 3: attractive for aggressive scale

  • 1.2–3: workable, depends on margins and cash runway
  • < 1: unsustainable unless significant cross-sell/long-term value expected
• CAC payback:
  • < 12 months: healthy for VC-backed SaaS
  • 12–24 months: acceptable for capital-efficient or enterprise models
• NRR:

  • 100%: ideal (expansion offsets churn)

  • 90–100%: acceptable depending on margins; focus on expansion if below 100%

10 Sample reporting template (Markdown-ready)

Inputs:

• Period: monthly
• ARPU_0: $__
• Gross margin: __%
• Monthly churn: __%
• Annual discount rate: __%
• Expansion rate (monly): __%
• Time horizon: __ months
• CAC: $__

Outputs:

• LTV (3-year): $__
• LTV (5-year): $__
• LTV (infinite DCF): $__
• LTV : CAC = __ : 1
• CAC payback (months) = CAC / (ARPU_0 * gross_margin) = __ months
• NRR (trailing 12 months) = __%

Notes:

• Assumptions: list cohort, time of measurement, inclusion/exclusion (one-time fees), margin calc, exchange rates.
• Sensitivity scenarios: best / expected / worst for churn & ARPU.

11 Final recommendations (action plan for founders)

1. Build cohort-level LTV + CAC dashboards and refresh monthly.
2. Enforce gross-margin adjusted LTV and report NRR and churn separately.
3. Prioritize retention to improve LTV most efficiently; A/B test lifecycle messaging, onboarding, and product-led growth loops.
4. Use LTV and payback to set CAC budgets per channel; reallocate spend to channels with highest incremental LTV:CAC.
5. Revisit discount rate and horizon periodically (market conditions, capital costs).
6. Present LTV estimates with scenario ranges and explicit assumptions to investors; avoid single-number claims.

12 References & further reading

• SaaS metrics frameworks (e.g., David Skok, Tomasz Tunguz)
• “Measure What Matters” style guides for cohort analysis
• Academic: Customer lifetime value literature and survival analysis for retention modeling

If you want, I can:

• produce a ready-to-use Google Sheets/Excel template with the inputs and scenario tabs;
• generate a reproducible Python notebook that ingests cohort data and computes cohort LTVs with visualizations;
• add example SQL queries tailored to your schema to compute cohort ARPU and survival curves.

Which would you like next?