LTV math you can do in a spreadsheet: naive vs cohort#

Naive LTV is ARPU divided by monthly churn: a $40 subscription at 8% churn implies $500. Cohort LTV sums what each signup month actually paid: the same store realizes about $268 per subscriber by month 12 on a typical commerce retention curve. Both fit in one spreadsheet. For stores under a year old, the naive formula usually flatters you, because early churn reads artificially low and the formula extrapolates it forever.

You do not need a data warehouse to compute lifetime value. You need your subscription export, one afternoon, and a clear view of what each formula assumes. This post gives you both formulas, a worked example, and the specific reasons the quick version lies to young stores.

The naive formula#

Naive LTV divides average revenue per user by churn rate: LTV = ARPU / monthly churn. It is the formula in every SaaS metrics primer, including Baremetrics' LTV guide, which presents it as ARPU times customer lifetime, where lifetime is 1 divided by churn. Same math, two spellings.

The appeal is obvious. Two inputs, one division, and you get a dollar figure you can hold against your acquisition cost. At $40 ARPU and 8% monthly churn, implied lifetime is 12.5 months and LTV is $500. Spend less than that acquiring a subscriber and you are, in theory, printing money.

Even the people who run this formula at scale hedge it. Paddle's ProfitWell Metrics documentation calls ARPU over churn "the simplest - and most naive - way" to calculate LTV, and their stated reason is the denominator: churn swings widely month to month, and every swing whipsaws the output. Move churn from 4% to 6% and implied LTV drops by a third, even though nothing real changed except one noisy month. Baremetrics goes further and suggests multiplying the result by 0.75 as a rough haircut for that variance.

The cohort formula#

Cohort LTV asks a smaller, honest question: of the people who subscribed in a given month, how much have they actually paid us, per starting subscriber, as of month N?

The recipe:

1. Group subscribers by signup month. That group is a cohort.
2. For each month after signup, count how many are still active. That is the survival column.
3. Multiply survivors by price to get that month's revenue from the cohort.
4. Divide cumulative revenue by the cohort's starting size.

The output is a curve, not a single number: LTV at month 3, month 6, month 12. That shape is the point. It tells you when the money actually arrives, which a single division never can. The naive formula is a forecast of terminal value. The cohort number is cash that has cleared.

A worked example#

The following store is fictional and the retention curve is illustrative. We have calibrated it to the only broad public anchors we know of for subscription commerce: Recharge's 2023 State of Subscription Commerce report, built on 2022 data from its merchant base, put average subscriber retention at 45% after 6 months and 33% after 12 months across verticals. Treat every row below as a shape lesson, not a benchmark.

Alder & Ivy sells a $40/month coffee subscription on WooCommerce. Take the January cohort: 100 subscribers.

Cohort LTV at month 12: $268. Now the naive readings for the same store:

Same store, same $40 price, and the formula returns anything from $286 to $1,333 depending on which month you happened to measure churn. The cohort table has no such degrees of freedom. Month 12 realized value is $268 whichever week you compute it.

Why the naive formula overstates LTV for young stores#

The short answer: young stores feed the formula a churn rate that is biased low, and the formula multiplies that error across an infinite horizon. Three mechanisms do the damage.

Cancellations lag signups. In WooCommerce Subscriptions, a customer who cancels mid-term does not become Cancelled immediately. Per the official statuses documentation, the subscription moves to Pending Cancellation and only transitions to Cancelled when the prepaid term ends. A store that launched eight weeks ago has taken plenty of orders and registered almost none of its eventual cancellations. Its dashboard churn is not 8%. It is 2 or 3%, and it is wrong.

Growth dilutes the denominator. Churn is usually computed as cancellations divided by total subscribers. On a fast-growing store, a large share of that base signed up days ago and has not yet faced a renewal decision. They cannot churn this month, but they pad the denominator, so measured churn drops while nothing about retention improved. The faster you grow, the better your churn looks, which is exactly backwards as a decision input.

The horizon is infinite and the sample is tiny. ARPU over churn assumes the current rate persists forever and sums the whole geometric series. A young store is extrapolating 30-plus months of future revenue from perhaps 90 days of biased data. Baremetrics' guide makes the related point that LTV computed on too few customers is not statistically meaningful in the first place; small stores need a large fraction of their base in the calculation before the number stabilizes.

One honesty note, because the naive formula is not always wrong: at genuine steady state, blended churn mathematically equals 1 divided by average lifetime, so ARPU over churn converges on the truth for old, stable, flat-growth businesses. That is precisely why big companies get away with it. The formula is not broken in general. It is broken for you, specifically, while your store is young, growing, and cliff-heavy - which is when you are making your biggest CAC bets.

There is also a cash-timing overstatement that applies at any age. Naive LTV books the entire lifetime today. The cohort table shows the money arriving over years: $120 by month 3, $268 by month 12. If you spend $350 to acquire a subscriber because "LTV is $500", you are financing a 20-month payback with a number that was never cash to begin with.

Building both in a spreadsheet#

Fifteen rows, no plugins. Export your subscriptions with signup date and cancellation date (remember that Pending Cancellation subscribers should count as churned for this purpose, not active).

• Column A: months since signup, 0 through 12.
• Column B: survivors. =COUNTIFS(signups, cohort_month, lifetime_months, ">="&A2) against your export, or just count by hand for a small store.
• Column C: revenue, =B2*price.
• Column D: cumulative LTV, =SUM(C$2:C2)/B$2.
• Naive, for comparison: =ARPU/churn in one cell, and label it "forecast, not cash".

Two rules make the output trustworthy. First, only read cohorts where the data is complete: a March cohort has no month-6 value in July, and averaging incomplete cohorts into your curve drags it down artificially. Second, compute at least three cohorts before believing anything. One cohort of 40 subscribers is an anecdote. We say this as a vendor whose own numbers deserve the same caveat: the early ChurnStop install cohort is still too small for us to publish LTV curves from it, so everything above leans on the public anchors cited.

Revenue LTV vs margin LTV#

Everything above is revenue LTV, and for a physical-goods subscription that overstates value a second time. A $268 revenue LTV on coffee that costs you $14 a bag to source and $6 to ship is not $268 of value. It is roughly $134 of contribution before you have paid for ads, software, or your own time.

The fix is one more cell: multiply by contribution margin. Baremetrics' guide applies the same idea for SaaS by folding profit per user into the formula, and the logic transfers directly to commerce, where margins are far thinner than software. Two practical notes:

• Use contribution margin (price minus product cost, fulfillment, payment fees), not gross revenue and not fully-loaded net profit. Fixed costs do not scale with one extra subscriber, so they do not belong in a per-subscriber decision number.
• Compute margin per cohort month if your product mix shifts. A store that upsells a larger box at month 3 has a different margin at month 6 than at month 0, and a flat multiplier hides that.

Discounts belong here too. If your save flow or your acquisition funnel hands out 20% off for three cycles, the survival column stays the same but the revenue column does not. Model discounted months at their discounted price instead of pretending every renewal bills at list. On a store where a third of subscribers came in through a promo, list-price LTV can run 10 to 15% ahead of billed reality - and that gap is pure spreadsheet fiction, visible in your own order data if you look.

The compare-against number matters as much as the LTV itself. A CAC ceiling set against margin LTV at month 12 is a business rule. A CAC ceiling set against revenue LTV from the naive formula is a wish.

What to do with the number#

• Use cohort LTV at month 12 for CAC ceilings and ad budgets. It is realized cash, so it cannot flatter you.
• Use cohort LTV at month 3 as your early-warning metric. It moves within one quarter of a retention change, while the naive number can drift for months.
• Recompute the naive formula quarterly if you like, but only after your store is past its first year and growth has settled. Before that, treat it as an upper bound, not an estimate.
• When you evaluate a retention investment - a save flow, a winback sequence, a cadence change - model it as a shift in the survival column, not as "churn goes from 8% to 6%". The LTV lift calculator does exactly that arithmetic if you want to skip the spreadsheet.
• To understand the shape of the survival column itself - why it cliffs early and flattens late - see the companion post on reading cohort retention curves, and check your churn inputs against the WooCommerce churn benchmarks before trusting them.

The naive formula answers "what might a subscriber be worth someday, if nothing changes". The cohort table answers "what did subscribers actually pay us, and by when". Run your store on the second number and use the first only as a sanity check - never the other way around.