LTV & Cohort Analysis Model | SaaS Finance Guide

Take two subscription businesses. Both acquire 500 new customers in January at a cost of £200 each. Both charge £30 per month. On a single month’s P&L they look identical. But one has 5% monthly churn and the other has 2% monthly churn. Fast-forward two years: the first business has retained just 28% of that January cohort — 140 customers still paying. The second has retained 63% — 315 customers still paying. Over those 24 months, the difference in cumulative gross profit generated by that single cohort, at 70% margins, is more than £60,000. And that is just one cohort, from one month.

This is the problem that cohort analysis exists to solve. Monthly revenue figures and aggregate churn rates smooth over the most important dynamics in a subscription business: how quickly customers are leaving, whether recent cohorts are behaving better or worse than older ones, and what a customer acquired today is actually worth over their lifetime. Without a cohort model, you are flying on instruments that are accurate in aggregate but useless for the decisions that actually determine long-run profitability.

This post builds an LTV and cohort model for SubCo, a B2C subscription business, covering three methods of calculating LTV and the full mechanics of building a cohort retention table in Excel.

The Three LTV Formulas

Customer Lifetime Value is the total gross profit a business expects to generate from a customer over their entire relationship with the company. There are three ways to calculate it, in increasing order of sophistication and accuracy.

Method 1 — Simple LTV

LTV = ARPU × Gross Margin % / Monthly Churn Rate

For SubCo: ARPU = £30, Gross Margin = 70%, Monthly Churn = 3%.

LTV = £30 × 70% / 3% = £700

This formula treats LTV as the present value of a perpetuity — it assumes the customer pays the same amount every month until they churn, churn occurs at a constant rate, and there is no time value of money. It is a useful back-of-the-envelope figure and works well for quick comparisons. But it tends to overstate true LTV because real churn is rarely constant.

Method 2 — Discounted LTV

If you want to account for the time value of money — which you should for any serious capital allocation decision — you discount the future monthly cash flows back to today:

LTV = Monthly GP / (Monthly Churn Rate + Monthly Discount Rate)

Where Monthly Discount Rate is the company’s annual discount rate divided by 12. Using SubCo’s 12% annual cost of capital: Monthly Discount Rate = 1%.

LTV = £21 / (3% + 1%) = £525

The discounting reduces the estimate from £700 to £525 — a 25% haircut that becomes even more significant for businesses with higher churn or higher discount rates.

Method 3 — Cohort-Based LTV

Both formulas above assume constant churn. This is almost never true. Subscription businesses typically experience high early churn (customers who try the product and leave quickly) followed by lower steady-state churn among genuinely engaged customers. If you assume constant churn calibrated to the average, you will overestimate the long-run value of new customers who have not yet passed through the high-churn early months.

Cohort-based LTV uses actual observed retention data to project cash flows, and is the most accurate method available when you have sufficient history. The rest of this post explains how to build it.

Building the Cohort Retention Table

A cohort is a group of customers who all started their subscription in the same calendar month. A cohort retention table tracks what percentage of each cohort is still active in each subsequent month. It is the single most important analytical tool in subscription finance.

The table has a simple structure:

Rows — one per cohort month (Jan-24, Feb-24, Mar-24, and so on)
Columns — months since acquisition (Month 0, Month 1, Month 2, up to however many months of history you have)
Values — the percentage of the original cohort still active at that point

For SubCo, the first six months of data for three cohorts looks like this:

Cohort	M0	M1	M2	M3	M4	M5	M6
Jan-24	100%	88%	79%	73%	68%	65%	63%
Feb-24	100%	87%	80%	74%	69%	66%	—
Mar-24	100%	89%	81%	75%	70%	—	—

Two patterns stand out immediately. First, churn is heavily front-loaded: the largest drop for every cohort is in Month 1 (12 percentage points for Jan-24), tapering to 2–3 points per month by Month 4. This is classic for subscription businesses — many customers sign up, try the product, and decide quickly whether it fits into their life. After the initial drop-off, the remaining customers are disproportionately engaged and churn much more slowly.

Second, later cohorts show slightly better Month 1 retention (89% for Mar-24 vs 88% for Jan-24). A small improvement like this is worth monitoring — it may indicate that onboarding improvements are working, or it may be seasonal noise. A longer time series would tell you which.

Building the table in Excel

To construct this in Excel, you need a source table of individual customer data — specifically, for each customer: their acquisition month and their most recent active month (or cancellation month). From this, you can compute the retention rate for each cohort-month cell.

The practical approach for most analysts is:

Create a pivot table with cohort month as rows and “months since acquisition” as the column grouping.
For each cell, count active customers and divide by the size of the cohort at Month 0.
Format as percentages.

If you are working from an aggregate monthly active customer report rather than individual customer records, you can back into retention rates by tracking each cohort’s active count over time and dividing by its starting size.

The Cohort Heatmap

Once the retention table is built, apply conditional formatting to transform it into a heatmap. Set the scale from dark green (100%) through white (around 50%) to dark red (0%). This produces the standard cohort retention heatmap that appears in investor decks, board packs, and product analytics dashboards across the industry.

The heatmap conveys several things simultaneously that a table of numbers cannot:

The retention curve shape — a steep drop in the first two columns that flattens thereafter is healthy (engaged survivors). A curve that continues declining steeply into Month 6 and beyond suggests a fundamental product-fit problem.
Cohort-over-cohort trends — scanning diagonally from top-right to bottom-left, you can compare how all cohorts performed at the same lifecycle stage. If the diagonal is getting greener over time, retention is improving.
Seasonal effects — if a particular calendar month (say, December) consistently shows higher churn across all cohorts at that age, it may reflect seasonal disengagement or renewal decision timing.

This single chart, properly constructed, contains more information about a subscription business’s health than most of the slides in a typical board pack.

Cohort-Based LTV Calculation

With the retention table in hand, you can calculate LTV for any cohort using actual observed cash flows rather than assumed constant churn.

The formula for each month’s contribution from a cohort is:

Cohort Revenue (month t) = Cohort Size × Retention Rate (t) × ARPU × Gross Margin %
Cohort LTV = Σ [Cohort Revenue (t)] over observed lifetime

For SubCo’s January cohort of 500 customers, at £21 monthly gross profit per customer:

Month	Retention	Active Customers	Monthly GP (£)
M0	100%	500	10,500
M1	88%	440	9,240
M2	79%	395	8,295
M3	73%	365	7,665
M4	68%	340	7,140
M5	65%	325	6,825
M6	63%	315	6,615

Cumulative gross profit over six months: approximately £56,300. For the full 12-month period (projecting months 7–12 using the average monthly churn rate from months 4–6, approximately 1.5%), cumulative 12-month cohort GP is approximately £85,000.

Per-customer 12-month LTV: £85,000 / 500 = £170.

Compare this to the simple formula estimate of £700. The discrepancy is stark — and it illustrates exactly why the simple formula is dangerous. The simple formula assumes 3% constant monthly churn from Month 0. The actual data shows 12% churn in Month 1. The simple formula credits the business with 33 months of average customer life; the cohort data shows that the median customer is gone before Month 2. The simple LTV is four times the 12-month reality.

This does not mean the simple formula is always wrong. For mature businesses with stable, low churn where customers have been around long enough that the front-loaded drop-off is a distant memory, it is a reasonable approximation. For growth-stage businesses with limited cohort history, it almost always overstates LTV significantly.

LTV/CAC and the Unit Economics Story

The LTV/CAC ratio is the fundamental measure of whether a subscription business’s growth model is economically viable:

LTV/CAC Ratio = LTV / CAC

The widely cited benchmark is 3× — meaning the lifetime gross profit from a customer should be at least three times what it cost to acquire them. Below 3×, the business is likely spending growth capital inefficiently. Below 1×, the business is destroying value with every customer it acquires.

For SubCo:

CAC = £200 (from the marketing ROI model)
12-month cohort-based LTV = £170 → LTV/CAC = 0.85× — not yet profitable on a 12-month basis
Simple/perpetuity LTV = £700 → LTV/CAC = 3.5× — healthy long-run unit economics
CAC payback: £200 / £21 = 9.5 months — acceptable, meaning SubCo recovers its acquisition cost within a year

The gap between these two views is the central tension in growth-stage finance. Investors and founders often cite the long-run LTV/CAC ratio (3.5×) to justify continued growth investment. Finance teams need to hold both numbers simultaneously: the long-run economics may be attractive, but the business must survive long enough to realise them. The 12-month LTV being below CAC means SubCo is net cash-negative on each customer for the first year — which is manageable if churn stabilises at the levels observed in months 4–6, but catastrophic if it does not.

Modelling a Churn Improvement

One of the most powerful uses of the cohort model is quantifying the financial impact of retention initiatives. Product improvements, onboarding redesigns, and customer success programmes all claim to reduce churn — the cohort model lets you put a number on what that is worth.

Suppose SubCo’s product team proposes an onboarding improvement that they believe will increase Month 1 retention from 88% to 92%. What is that worth?

Recalculating the January cohort with improved Month 1 retention (and assuming subsequent months are proportionally better):

At 92% M1 retention: the cohort starts M1 with 460 active customers instead of 440
Over 12 months, the additional 20 customers each generating £21/month = approximately £3,600 of additional cohort GP in Year 1
Across all future cohorts (assuming SubCo acquires 500 new customers per month), that improvement generates approximately £43,000 of incremental gross profit per year — from a single 4-percentage-point retention lift

This is a compelling internal business case. A retention improvement that saves 4 percentage points of M1 churn across a modest acquisition rate of 500 customers per month is worth more than £40k per year in additional gross profit, with a compounding benefit as cohorts with improved retention accumulate on the active customer base.

The cohort model does not just measure the present — it quantifies the future value of operational decisions made today.

Key Takeaways

The simple LTV formula is a useful approximation but almost always overstates true LTV for growth-stage businesses where churn is front-loaded
Churn is nearly always higher in early months — building this front-loading into your model produces materially more accurate LTV estimates
The cohort retention table is one of the most information-dense tools in subscription finance; the heatmap makes its patterns instantly readable
LTV/CAC gives you the long-run unit economics verdict; CAC payback gives you the short-run cash position — both are essential and neither is sufficient on its own
Even a small improvement in early-month retention compounds into significant gross profit over time; the cohort model lets you quantify exactly how much

Practice

Build the SubCo cohort retention table for six cohorts across six months using the data in this post. Apply conditional formatting to create the heatmap. Calculate the per-customer 12-month LTV for the January cohort. Then model the revenue impact of a 3-percentage-point improvement in Month 1 retention (from 88% to 91%) applied to all six cohorts. What is the cumulative additional gross profit across the six cohorts over their first 12 months? Present your answer as a one-paragraph business case for the onboarding improvement, addressed to the product director.

LTV and Cohort Analysis: Understanding the Long-Term Value of Your Customers