customer lifetime value (LTV) calculation tutorial 2026
most solopreneurs use LTV like a vibe. they say “our LTV is around $500” and base pricing decisions on that, but they have not actually calculated it. or worse, they used the marketing-blog formula ARPU / churn and got a number five times higher than reality. either way, every CAC decision built on that number is wrong by 30-80%, and the marketing budget either underspends (leaving growth on the table) or overspends (burning runway).
a real LTV calculation takes one afternoon. you list every customer, calculate their actual revenue contribution to date, project the rest based on cohort retention, and segment by acquisition channel and plan. that gives you three numbers: realized LTV (what you have actually received), projected LTV (where it lands), and LTV/CAC ratio (whether your acquisition spend is profitable).
this tutorial walks the entire calculation with a realistic 16-row sample dataset. by the end, you will have a working LTV by segment, a CAC payback period, and a clear answer about which channel deserves more spend. no chartmogul, no proftiwell, just spreadsheets.
the sample dataset
below is the dataset we will use. paste into Google Sheets, save as ltv-calculation-2026, and follow along.
| customer_id | signup_month | source | plan | mrr | months_active | churned | total_revenue |
|---|---|---|---|---|---|---|---|
| C001 | 2025-08 | pro | 49 | 9 | no | 441 | |
| C002 | 2025-08 | referral | pro | 49 | 9 | no | 441 |
| C003 | 2025-08 | content | starter | 19 | 3 | yes | 57 |
| C004 | 2025-09 | enterprise | 199 | 8 | no | 1592 | |
| C005 | 2025-09 | paid_ads | starter | 19 | 2 | yes | 38 |
| C006 | 2025-09 | content | pro | 49 | 8 | no | 392 |
| C007 | 2025-10 | referral | pro | 49 | 7 | no | 343 |
| C008 | 2025-10 | starter | 19 | 4 | yes | 76 | |
| C009 | 2025-10 | paid_ads | pro | 49 | 3 | yes | 147 |
| C010 | 2025-11 | content | enterprise | 199 | 6 | no | 1194 |
| C011 | 2025-11 | pro | 49 | 6 | no | 294 | |
| C012 | 2025-12 | referral | pro | 49 | 5 | no | 245 |
| C013 | 2025-12 | paid_ads | starter | 19 | 2 | yes | 38 |
| C014 | 2026-01 | content | pro | 49 | 4 | no | 196 |
| C015 | 2026-02 | referral | enterprise | 199 | 3 | no | 597 |
| C016 | 2026-02 | starter | 19 | 3 | no | 57 |
customer lifetime value (LTV) is the total expected revenue from a customer over the duration of their relationship with your business. the standard solopreneur calculation is
LTV = ARPU / monthly_churn_rate, but a more accurate version uses cohort retention curves to project remaining revenue. healthy SaaS LTV/CAC ratios run 3:1 or higher; below 3:1 means acquisition is too expensive. the calculation takes 30 minutes in Sheets and consistently surfaces a single channel-level decision worth 20-50% of monthly revenue in reallocated spend.
step 1: calculate realized revenue per customer
realized revenue is what you have already received. simplest formula:
realized_revenue = mrr × months_active
in the sample, column H already has this. for C001: 49 × 9 = $441.
check the math
formula in column I (check):
=E2*F2
expected output: should match column H (total_revenue) exactly.
if mismatched, you have a data problem. usually it is plan changes mid-relationship that the simple formula does not capture. in that case, sum each customer’s actual monthly charges from Stripe.
step 2: calculate the simple LTV (ARPU / churn)
this is the marketing-blog formula. it is fast, gives you a directional number, and overstates LTV by 20-50% compared to cohort-based methods.
overall ARPU
average_mrr = AVERAGE(E2:E17) = $61.06
overall monthly churn
count of churned: 5. average months active for churned: 2.8. for active, use the cohort-window approach.
simplified: churn_rate = 5 / 16 / 7 months observation = 4.5% monthly
(this approximation works for small samples; for production use the formal cohort method.)
simple LTV
LTV_simple = ARPU / churn_rate = $61 / 0.045 = $1,355
this is the number marketing blogs hand you. it is high. let us show why.
common mistake: the
ARPU / churnformula assumes churn is constant across the customer lifecycle. in reality, month-1 churn is much higher than month-12 churn. averaging hides this and inflates LTV by 30-80%. always cross-check with the cohort method.
step 3: calculate cohort-based LTV
the better method tracks each cohort’s actual retention curve.
build the cohort retention table
| signup_month | M0 | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 |
|---|---|---|---|---|---|---|---|---|---|
| 2025-08 | 3 | 3 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 2025-09 | 3 | 3 | 2 | 2 | 2 | 2 | 2 | 2 | |
| 2025-10 | 3 | 3 | 2 | 1 | 1 | 1 | 1 | ||
| 2025-11 | 2 | 2 | 2 | 2 | 2 | 2 | |||
| 2025-12 | 2 | 2 | 1 | 1 | 1 | ||||
| 2026-01 | 1 | 1 | 1 | 1 | |||||
| 2026-02 | 2 | 2 | 2 |
convert to retention rates:
| signup_month | M0 | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 |
|---|---|---|---|---|---|---|---|---|---|
| 2025-08 | 100% | 100% | 67% | 67% | 67% | 67% | 67% | 67% | 67% |
| 2025-09 | 100% | 100% | 67% | 67% | 67% | 67% | 67% | 67% | |
| 2025-10 | 100% | 100% | 67% | 33% | 33% | 33% | 33% | ||
| 2025-11 | 100% | 100% | 100% | 100% | 100% | 100% | |||
| 2025-12 | 100% | 100% | 50% | 50% | 50% |
average retention curve
| month | avg_retention |
|---|---|
| M0 | 100% |
| M1 | 100% |
| M2 | 70% |
| M3 | 63% |
| M4 | 63% |
| M5 | 63% |
| M6 | 50% |
cohort LTV calculation
multiply average retention × ARPU at each month, sum across months. for our sample with $61 ARPU:
| month | retention | revenue_contributed |
|---|---|---|
| M0 | 100% | $61 |
| M1 | 100% | $61 |
| M2 | 70% | $43 |
| M3 | 63% | $39 |
| M4 | 63% | $39 |
| M5 | 63% | $39 |
| M6 | 50% | $31 |
| … extrapolated 24 months | $300 (cumulative tail) |
cohort_LTV ≈ $613
less than half of the simple-formula LTV. this is the realistic number for budgeting CAC.
our cohort analysis for SaaS founders tutorial covers retention-curve mechanics in detail.
step 4: segment LTV by source
build a pivot.
- Rows:
source - Values:
total_revenue(AVERAGE) - Values:
customer_id(COUNTA)
expected output:
| source | avg_realized_revenue | customers |
|---|---|---|
| content | $460 | 4 |
| $497 | 5 | |
| paid_ads | $74 | 3 |
| referral | $407 | 4 |
paid_ads customers have realized $74 per customer. linkedin customers have realized $497. that is a 6.7x gap.
even adjusted for tenure (linkedin customers have been around longer on average), the gap is still 3-4x after normalizing.
step 5: segment LTV by plan
second pivot.
- Rows:
plan - Values:
total_revenue(AVERAGE)
expected output:
| plan | avg_realized_revenue | customers |
|---|---|---|
| enterprise | $1128 | 3 |
| pro | $322 | 8 |
| starter | $53 | 5 |
starter customers contribute $53 over their entire lifetime. an enterprise customer contributes $1,128. acquisition cost should be priced accordingly.
step 6: calculate LTV/CAC ratio
LTV is meaningless without CAC. estimate CAC by source:
| source | spend (90 days) | new customers | CAC |
|---|---|---|---|
| content | $1500 (time + tools) | 4 | $375 |
| $0 (organic) | 5 | $0 | |
| paid_ads | $1800 | 3 | $600 |
| referral | $300 (referral fees) | 4 | $75 |
now compute LTV/CAC:
| source | LTV | CAC | LTV/CAC |
|---|---|---|---|
| content | $460 | $375 | 1.2 |
| $497 | $0 | infinite | |
| paid_ads | $74 | $600 | 0.12 |
| referral | $407 | $75 | 5.4 |
paid ads loses 88 cents on every dollar. referral makes 5.4 dollars on every dollar. content is barely profitable. linkedin is free.
the conclusion writes itself: kill paid ads, double referral spend, keep linkedin, leave content as-is.
step 7: calculate CAC payback
CAC payback is months until customer revenue covers acquisition cost.
payback_months = CAC / ARPU
| source | CAC | ARPU | payback_months |
|---|---|---|---|
| content | $375 | $61 | 6.1 |
| $0 | $61 | 0 | |
| paid_ads | $600 | $61 | 9.8 |
| referral | $75 | $61 | 1.2 |
healthy SaaS payback is under 12 months. anything over 18 months is a red flag for cash flow. paid ads at 9.8 months is borderline (and unprofitable in LTV terms separately). referral at 1.2 months is exceptional.
comparing LTV calculation methods
| method | formula | accuracy | use case |
|---|---|---|---|
| simple | ARPU / churn | low (overstates 30-80%) | quick directional check |
| cohort retention | sum(retention × ARPU per month) | high | budgeting CAC |
| predicted | regression with features | very high | larger samples (1000+) |
| BTYD model | beta-geometric distribution | very high | non-contractual ecommerce |
solopreneurs should use cohort retention. it is the right balance of accuracy and effort.
our SaaS metrics founders must track guide covers the broader metrics LTV plugs into, and our customer churn analysis tutorial provides the retention input this calculation depends on.
frequently asked questions
should I use gross or net revenue for LTV?
net of payment processing fees but gross of cost-of-goods-sold (server costs, support time). the more conservative LTV (net of all costs, sometimes called LTV margin) is closer to what you can spend on CAC.
what discount rate should I apply for future revenue?
for solopreneurs, none. discounting matters for CFOs running 5-year models. for monthly cash decisions, project nominal future revenue.
how many cohorts before LTV is reliable?
minimum 6 cohorts with at least 6 months observation each. 12 cohorts is better. the cohort tail is what makes LTV either look high or low; you cannot estimate the tail without observing it.
should I include expansion revenue?
yes. customers who upgrade are part of LTV. track expansion_mrr separately so you can attribute lift to upsell campaigns.
my LTV/CAC is below 3:1. what next?
three options: lower CAC (better targeting, kill underperforming channels), raise LTV (improve retention, raise prices, add expansion), or accept that this is not a venture-scale business and run it as a cashflow business with shorter payback windows.
conclusion: build the LTV table this afternoon
LTV is one of those numbers that solopreneurs assume is hard to calculate but actually takes 30 minutes once you have the customer list. the sample dataset above gives you the schema. the cohort method gives you the realistic number. the LTV/CAC table tells you what to do with it.
start this afternoon. export every customer from your billing tool, calculate realized revenue, build the cohort retention curve, and segment by source. you will see one channel that is wildly profitable and one that is bleeding money. that single insight is worth $1,000+ in reallocated marketing spend over the next 90 days.
for next steps, our ROAS analysis tutorial covers the campaign-level version of LTV/CAC, and our marketing funnel analysis tutorial connects the top of funnel to LTV math. fix activation, prove the cohort, then expand spend on the channel that pays back fastest.