Cohort Retention
Corporate Finance Financial Model (Free Excel Download)
Measure customer and revenue retention by cohort with GRR, NRR, gross-margin LTV, DCF-adjusted LTV, and LTV-to-CAC outputs for product and investment decisions.
professionals from Deloitte
Used by professionals from
About this model
A cohort retention model tracks twelve monthly cohorts across thirteen cohort ages (M0 through M12) so the founder, CFO, or analyst can see retention quality vintage-by-vintage rather than the blended whole-book number that hides churn from older cohorts. The model is built as four parallel sheets - Retention, Cohort_Customers, Cohort_Revenue, and Summary - plus an Assumptions sheet that holds every driver as a named range.
The Retention sheet expresses one shared logo retention curve from milestones at every age, then layers on a monthly net revenue retention factor (expansion minus contraction, compounded each month) to produce a separate revenue retention curve. The Cohort_Customers sheet builds a customer triangle for each cohort: cohort initial size compounds at a monthly cohort-growth rate, and at every age the customer count is initial size times the retention at that age. The Cohort_Revenue sheet multiplies that customer count by an ARPU that steps up annually at the calendar month - not the cohort's birth month - and by the NRR factor at the cohort's age, so older cohorts catch the price increase at the right time and the dollar retention curve does not artificially flatten.
The Summary sheet rolls each cohort into initial size, M12 customers, GRR, NRR, cumulative revenue, cumulative gross margin, LTV per customer, LTV-to-CAC, and a DCF-adjusted LTV using a monthly discount factor row. Founders, FP&A, revenue leaders, and growth analysts use this template for investor due diligence, pricing decisions, and quarterly retention reviews: the curve assumptions are easy to overwrite with realised retention from the warehouse, and the cohort triangles plus Summary recompute against the same baseline so the story can be told in either logo or dollar terms without re-wiring the workbook.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Cohort Retention
- 12 monthly cohorts × 13 cohort ages (M0 through M12)
- Shared logo retention curve plus a separate revenue retention curve
- Per-cohort customer triangle: initial size and customers retained at every age
- Per-cohort revenue triangle with calendar price step-ups and monthly NRR compounding
- Cohort LTV (gross-margin) and DCF-adjusted LTV using a monthly discount factor
- Summary with GRR, NRR, cumulative revenue, cumulative GM, LTV, LTV-to-CAC per cohort
Cohort Retention: Inside the Unit Economics Model
Cohort retention analysis reveals how subscription businesses retain customers and revenue over time. This model builds a monthly cohort triangle for up to 12 vintages, tracking logo and net revenue retention, lifetime value, CAC payback, and DCF-adjusted LTV.
It is a diagnostic tool for pinpointing which acquisition vintage is leaking value.
Operating drivers behind each cohort's economics
The model's operating engine starts with customer acquisition. A base monthly cohort size grows at a user-supplied rate, creating twelve acquisition vintages.
- Each cohort's initial count is then shaped by a shared monthly logo retention curve and a cohort-specific quality multiplier, which lets each vintage retain differently. Plan mix adds another layer: Basic, Pro, and Enterprise tiers carry different ARPU and retention scalars, so the blended revenue per customer and blended churn risk depend on the mix.
- Expansion, contraction, and reactivation rates further adjust recurring revenue, while a refund rate hits only the first month and a free-month setting can delay revenue for promotional cohorts. Together, these inputs determine how many customers survive and what they pay over the first year.
Calculation flow from assumptions to cohort triangles
Calculations flow in one direction, from Assumptions through Retention and the cohort sheets to the Summary, Cover, and Checks. Customer counts for each cohort and age multiply the initial cohort size by its growth factor, the survival rate from the retention curve, the cohort quality multiplier, and the plan-weighted retention scalar.
- Revenue per cohort and age then takes those surviving customers, multiplies by plan-weighted ARPU, applies an annual price step when the age crosses a twelve-month boundary, applies a net revenue retention factor that compounds expansion, contraction, and reactivation, and finally gates for refunds at age zero and free months.
- The NRR Bridge decomposes cohort one's monthly recurring revenue into opening, logo churn, contraction, expansion, reactivation, and ending balances, showing where revenue leaks.
Outputs: retention, LTV, CAC payback, and DCF LTV
The Summary sheet presents per-cohort metrics: initial size, month-12 customers, gross revenue retention, net revenue retention, cumulative revenue, cumulative gross margin, LTV, LTV-to-CAC, CAC payback in months, and discounted LTV. Payback uses a nested IF cascade to find the first month when cumulative gross margin covers the blended CAC.
- DCF LTV discounts each month's revenue by a monthly factor derived from an annual discount rate, multiplies by gross margin, and divides by initial cohort size. A blended total row aggregates these metrics, weighting net revenue retention by initial cohort size.
- The Cover sheet surfaces six headline dashboard cards, including blended GRR, blended NRR, month-12 retention, LTV-to-CAC, payback, and DCF LTV per customer, so a reviewer can see the portfolio picture at a glance.
Practical use in unit-economics reviews
This template is designed for diagnosing retention quality by acquisition vintage, not for forecasting. It helps answer which cohort is underperforming and how much revenue is leaking through churn, contraction, or weak reactivation.
- By adjusting the cohort quality multipliers, a user can model improving or deteriorating acquisition quality, a single bad cohort, or a regime change, and see the effect on per-cohort NRR and blended LTV-to-CAC. The model also supports plan-tier and channel-mix analysis: blended CAC comes from a weighted mix of paid, organic, and outbound channels, and blended ARPU and retention reflect plan mix.
- Twelve built-in checks flag mix that does not sum to 100 percent, retention that increases, or LTV-to-CAC below one, helping maintain integrity during scenario work.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is a cohort retention model?+
A cohort retention model groups customers by the month they were acquired and tracks each group's logo retention and dollar retention over time. It is how SaaS, subscription, and consumption businesses see whether retention quality is improving, holding, or eroding - and how cohort-level LTV compares to the CAC paid to acquire them.
How is NRR calculated here?+
NRR per cohort is revenue at age 12 divided by revenue at age 0 for that cohort. Logo retention drives customer count and a separate NRR factor lifts per-customer ARPU each month via expansion minus contraction, so the reported NRR captures both churn and expansion in one number.
Why does the model price ARPU on the calendar month rather than the cohort month?+
Because real subscription pricing steps up on a calendar date - every customer renews at the new price at the same time, regardless of when they were acquired. Pricing on the cohort's own month-1 would understate ARPU for older cohorts and distort dollar retention.
Can I replace the modelled retention with my own data?+
Yes. Overwrite the retention milestones on the Assumptions sheet (Age 0 through Age 12). The Retention curve, both cohort triangles, and the Summary all recompute from those values. The shape of the workbook is unchanged.
Does this replace a full unit-economics model?+
No. This template focuses on cohort retention and cohort LTV. Pair it with the unit-economics or saas-mrr-arr template if you need channel-level CAC, payback period decomposition, or expansion bookings by motion.
Have more financial modelling questions? Contact us
Related templates
SaaS MRR/ARR Forecast Model
Subscription forecast with logo motion, MRR build, ARR, NRR/GRR, and CAC payback for SaaS businesses.
Unit Economics Dashboard
Track customer acquisition cost, lifetime value, payback period, and margins by customer segment.
Subscription Box Economics
Model recurring revenue, churn, customer acquisition, and fulfillment costs for a subscription service.
3 Statement Model
Integrated income statement, balance sheet, and cash flow forecasts.
