# Cohort Retention A 12-cohort retention model tracked across 13 cohort ages - logo retention, net revenue retention, per-cohort customer and revenue triangles, cumulative gross-margin LTV, DCF-adjusted LTV, and LTV-to-CAC side-by-side. - Canonical: https://finamodel.com/templates/cohort-retention - Excel download: https://finamodel.com/templates/cohort-retention.xlsx - Category: Corporate Finance - Model type: Operating model - Difficulty: Intermediate - Audiences: Founders & operators, CFOs & FP&A, Founders, Revenue leaders, Growth analysts - Tags: cohort, retention, ltv, nrr, saas ## Overview 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's included - 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 - 12 monthly cohorts tracked across 13 cohort ages (M0 through M12) - Shared logo retention curve and a separate net revenue retention curve - Per-cohort customer triangle: initial size, retained customers at every age - Cohort LTV (gross-margin) and a DCF-adjusted LTV using a monthly discount factor ## 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. ## Built for vintage-by-vintage retention Blended whole-book retention hides churn from older cohorts. This template builds one retention curve and then projects every cohort against it, so the dollar and logo story stay separate. ## Calendar-aware ARPU Revenue at cohort age n is priced at the calendar month, not the cohort's birth month. Older cohorts catch the annual price increase at the right time so dollar curves do not artificially flatten. ## Audit-friendly mechanics Every input is a named range, every formula is one or two operations, and the workbook passes static-value, self-reference, dead-assumption, and unused-named-range scans. ## Built for vintage-by-vintage retention Blended whole-book retention hides churn from older cohorts. This template builds one retention curve and then projects every cohort against it, so the dollar and logo story stay separate. ## Calendar-aware ARPU Revenue at cohort age n is priced at the calendar month, not the cohort's birth month. Older cohorts catch the annual price increase at the right time so dollar curves do not artificially flatten. ## Audit-friendly mechanics Every input is a named range, every formula is one or two operations, and the workbook passes static-value, self-reference, dead-assumption, and unused-named-range scans. ## Workbook structure ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: cohort sizing, retention curve, ARPU, expansion / contraction, gross margin, CAC, discount rate. - Cohort 1 initial size and monthly cohort growth - Retention milestones at every age (M0 through M12) - Base ARPU, annual price increase, monthly expansion and contraction - Gross margin, CAC per customer, annual discount rate ### Retention Shared logo retention curve, NRR factor, revenue retention curve, monthly discount factor. - Logo retention from the milestone inputs - NRR factor = (1 + expansion − contraction)^age - Revenue retention = logo retention × NRR factor - Monthly discount factor for DCF-adjusted LTV ### Cohort_Customers 12 cohorts × 13 ages: customers retained per cohort per age. - Age 0 = cohort initial size compounded at the cohort-growth rate - Ages 1–12 = initial size × logo retention at that age - One row per cohort (M1 through M12) ### Cohort_Revenue 12 cohorts × 13 ages: monthly revenue per cohort per age. - Revenue = customers × ARPU at calendar month × NRR factor - Calendar month = cohort index + age − 1 - Annual price increase applies once calendar month crosses 12 ### Summary Per-cohort retention, revenue, LTV, and LTV-to-CAC, with weighted totals. - Initial size and M12 customers per cohort - GRR (M12) and NRR (M12) per cohort - Cumulative revenue, cumulative gross margin, LTV per customer - LTV-to-CAC and DCF-adjusted LTV per cohort - Weighted totals across all 12 cohorts ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: cohort sizing, retention curve, ARPU, expansion / contraction, gross margin, CAC, discount rate. - Cohort 1 initial size and monthly cohort growth - Retention milestones at every age (M0 through M12) - Base ARPU, annual price increase, monthly expansion and contraction - Gross margin, CAC per customer, annual discount rate ### Retention Shared logo retention curve, NRR factor, revenue retention curve, monthly discount factor. - Logo retention from the milestone inputs - NRR factor = (1 + expansion − contraction)^age - Revenue retention = logo retention × NRR factor - Monthly discount factor for DCF-adjusted LTV ### Cohort_Customers 12 cohorts × 13 ages: customers retained per cohort per age. - Age 0 = cohort initial size compounded at the cohort-growth rate - Ages 1–12 = initial size × logo retention at that age - One row per cohort (M1 through M12) ### Cohort_Revenue 12 cohorts × 13 ages: monthly revenue per cohort per age. - Revenue = customers × ARPU at calendar month × NRR factor - Calendar month = cohort index + age − 1 - Annual price increase applies once calendar month crosses 12 ### Summary Per-cohort retention, revenue, LTV, and LTV-to-CAC, with weighted totals. - Initial size and M12 customers per cohort - GRR (M12) and NRR (M12) per cohort - Cumulative revenue, cumulative gross margin, LTV per customer - LTV-to-CAC and DCF-adjusted LTV per cohort - Weighted totals across all 12 cohorts ## Features - **Logo and revenue retention side-by-side:** A single retention curve drives the customer triangle; the same retention multiplied by a monthly NRR factor (expansion minus contraction) gives revenue retention so the dollar story is never confused with the logo story. - **Calendar-aware ARPU:** Revenue at cohort age n is priced at the calendar month, not the cohort's month 1. Older cohorts catch the annual price increase at the right time so cohort dollar curves don't artificially flatten. - **DCF-adjusted LTV:** Every cohort produces a perpetual gross-margin LTV and a DCF-adjusted LTV using a monthly discount factor row, so growth-stage LTV claims have a credible discount lens against CAC. ## Use cases - **Investor due diligence:** Drop the workbook into a data room and let analysts see the retention curve, NRR, and LTV/CAC per cohort instead of one blended whole-book number. - **Pricing and packaging decisions:** Flex the annual price increase, expansion rate, and contraction rate to see how dollar retention and LTV change cohort by cohort before signing off on a price change. - **Quarterly retention reviews:** Replace the curve assumptions with realised retention per age from the warehouse and the cohort triangles, GRR / NRR, and LTV recompute against the same baseline curve. ## Frequently asked questions ### 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. ## Related templates - [SaaS MRR/ARR Forecast Model](https://finamodel.com/templates/saas-mrr-arr-model) - [Unit Economics Dashboard](https://finamodel.com/templates/unit-economics-model) - [Subscription Box Economics](https://finamodel.com/templates/subscription-box-model)