Cohort Analysis in Excel: Step-by-Step Retention Tables for SaaS

Cohort analysis in Excel groups customers by the month they signed up and tracks how each group retains over time in a triangular matrix. The standard for SaaS is a 60 × 60 monthly cohort grid that shows both logo retention (percentage of customers still active) and revenue retention (percentage of original MRR preserved) for each cohort across 60 months. Building one from scratch takes four steps: structure the raw data, build the cohort matrix with SUMIFS, calculate retention rates row by row, and fit a decay curve to estimate LTV honestly.

What a cohort actually is

A cohort is a group of customers defined by a shared timing event. In SaaS the standard cohort definition is "customers who signed up in the same calendar month." Other cohort definitions exist — cohort by acquisition channel, by plan tier, by industry — but signup-month cohorts are the foundation everything else builds on.

The power of cohort analysis is that it separates signal from noise. Blended metrics — "overall churn was 3% last month" — mix new customers with mature ones, healthy cohorts with struggling ones. A March 2024 cohort behaves very differently than a March 2026 cohort. Blending them hides the dynamics.

A cohort matrix is the triangular table that shows, for each signup month (rows), the retention rate at each subsequent month (columns). The triangle exists because newer cohorts haven't had time to reach later months yet. A cohort that signed up in January 2026 can only be observed through months 1 through 4 by April 2026; columns 5 through 60 for that row are empty.

This structure is what lets an operator answer questions like: "Are our April 2025 cohorts retaining better than our April 2024 cohorts at the same tenure?" That is a question blended metrics cannot answer.

The two retention curves every SaaS should track

There are two distinct cohort analyses every SaaS company should run, and they answer different questions.

Logo retention tracks whether the customer is still a customer. The cell contains 1 if the customer account was active in that month, 0 if they churned. Aggregated across the cohort, it produces a retention percentage: "75% of the January 2024 cohort was still a customer in month 12."

Revenue retention tracks how much of the original MRR the cohort still represents, including expansion. The cell contains the MRR the customer was paying in that month. Aggregated and divided by the cohort's original MRR, it produces: "The January 2024 cohort is at 118% of its original MRR in month 12, because expansion outpaced churn."

Logo retention answers: "Are we keeping our customers?" Revenue retention answers: "Are we growing revenue per customer?" A healthy SaaS company wants both above 100% net of churn by month 12, which means expansion from the remaining customers more than compensates for the revenue from the ones who left.

2025 benchmarks, per Benchmarkit Q4 2025 data:

• Logo retention at month 12: median 80%, top quartile 88%+
• Gross revenue retention (GRR) at month 12: median 90%, top quartile 95%+
• Net revenue retention (NRR) at month 12: median 106%, top quartile 120%+

The gap between GRR (no expansion) and NRR (with expansion) tells you how effective the expansion motion is. A gap of 20+ points (GRR 90%, NRR 115%) is excellent.

Step 1: Structure the raw data

The cohort matrix is only as good as the raw data feeding it. The source data needs a specific structure.

Each row should be one customer-month — one observation per customer per month they were active. The required columns are: customer ID, signup month (formatted as a date, always the first of the month), observation month (also first of the month), MRR in that observation month, and plan tier.

If your data lives in Stripe, HubSpot, or a data warehouse, the query that produces this structure looks like: "For each customer, and for each month from signup through today, return the MRR they were paying that month. Return zero if they had churned."

The common mistakes in this data prep step: confusing signup date with subscription start date (use whichever represents "became a paying customer"), forgetting to include churned customers as zero-MRR rows in post-churn months (needed for accurate retention calc), and using subscription dates instead of calendar months (normalize to first-of-month always).

Once the raw data is in this shape, the cohort matrix is mechanical.

Step 2: Build the cohort matrix with SUMIFS

The cohort matrix has signup months as rows and tenure (not calendar months) as columns. Month 0 is the signup month. Month 1 is one month after signup. Month 60 is five years after.

The core formula for a logo retention matrix cell is:

=COUNTIFS(RawData[SignupMonth], $A5, RawData[ObservationMonth], EDATE($A5, B$4), RawData[MRR], ">0") / COUNTIFS(RawData[SignupMonth], $A5, RawData[ObservationMonth], $A5, RawData[MRR], ">0")

Parsing this:

• $A5 is the signup month label in the row header (like 2024-01-01).
• B$4 is the tenure month in the column header (0, 1, 2, ... 60).
• EDATE($A5, B$4) calculates the observation month for this cohort at this tenure.
• The numerator counts customers from that cohort who were active (MRR > 0) at the observation month.
• The denominator counts customers in that cohort at month 0 (the signup month).
• Dividing gives the retention rate for that cell.

The formula for revenue retention replaces COUNTIFS with SUMIFS summing the MRR column:

=SUMIFS(RawData[MRR], RawData[SignupMonth], $A5, RawData[ObservationMonth], EDATE($A5, B$4)) / SUMIFS(RawData[MRR], RawData[SignupMonth], $A5, RawData[ObservationMonth], $A5)

Drag this formula across 60 columns and down however many rows of signup months you have. The result is the triangular retention matrix.

For the future cells (where the observation month hasn't happened yet), wrap in an IFERROR that returns blank: =IFERROR(the_formula, ""). This keeps the matrix clean without false zeros.

Step 3: Calculate the average curve

Once the full matrix is built, the summary row at the bottom calculates the average retention across all cohorts at each tenure. This is the "retention curve" most people think of when they hear cohort analysis.

The formula for the average is straightforward — a simple AVERAGE across the column, excluding empty cells:

=AVERAGEIF(B5:B100, ">0")

The curve almost always shows the same shape: a steep drop in months 1-3 (acquisition-phase churn), a gradual flattening through months 6-12, and a near-horizontal tail from month 18 onward. The shape tells you which kind of business you have. A curve that flattens quickly at 70% means you have a sticky product with a meaningful activation problem. A curve that never flattens means you have a churn problem that compounds.

What to plot: the average retention curve, the top quartile of cohorts, the bottom quartile, and the most recent cohort with at least 6 months of data. The comparison between top and bottom quartile cohorts tells you how much variance there is in who you sell to. The most recent cohort tells you whether retention is improving or deteriorating over time.

Step 4: Fit a curve and extract honest LTV

The retention curve is the foundation of the honest LTV calculation. The naive LTV formula you've seen elsewhere — LTV = ARPA × Gross Margin / Monthly Churn Rate — assumes churn is constant, which it almost never is.

The honest LTV is calculated by fitting a decay function to the retention curve, then integrating (summing) the area under the curve times the monthly revenue contribution.

The simplest useful fit for SaaS retention is a two-parameter negative exponential: Retention(t) = a × EXP(-b × t) + c, where t is the tenure in months, a is the fast-decay amplitude, b is the decay rate, and c is the long-run floor.

In Excel, fit this with Solver by minimizing the sum of squared errors between observed retention and modeled retention. The constants for a typical SaaS cohort curve end up in the range of a ≈ 0.25, b ≈ 0.15, c ≈ 0.55 — meaning 55% of customers eventually stabilize as long-term subscribers.

Once you have the fitted curve, LTV is:

LTV = ARPA × Gross_Margin × SUM(Retention(t) for t from 0 to 60)

The sum of monthly retention values over 60 months, multiplied by average monthly revenue and gross margin, is the LTV cap at 60 months. Capping at 60 months — rather than integrating to infinity — is the honest operator move, because retention data beyond 60 months is unreliable for most SaaS businesses.

The difference between the honest LTV and the naive "ARPA × GM / churn" formula is usually 30-60% — the naive formula overstates LTV because it extrapolates early-month churn rates forward in time, when in reality late-stage cohorts churn much more slowly.

Interpreting the matrix: three patterns to look for

Once the matrix is built, the value comes from reading it. Three patterns matter most.

Pattern 1: The diagonal comparison. Compare cohorts at the same tenure across different signup months. If January 2024's month-12 retention was 78% and January 2026's month-12 retention was 85%, the business has improved. The reverse is a warning.

Pattern 2: The early-month bleed. The retention drop from month 0 to month 3 captures activation and onboarding quality. A 15-point drop in the first 90 days means one in seven customers is leaving before they see value. This is almost always fixable with onboarding improvements.

Pattern 3: The tail slope. After month 12, retention should flatten. If it's still dropping 1-2 points per month past month 18, the business has a structural retention problem — usually related to product-market fit within a segment.

The three questions to always ask:

1. "Which cohorts are my best?" — Look at month-12 retention across rows. Sort descending.
2. "What did those cohorts have in common?" — Cross-reference with acquisition channel, plan tier, or segment.
3. "Can I acquire more of them?" — The highest-ROI question in SaaS growth.

Common mistakes in cohort analysis

Using calendar-month retention instead of tenure-month. A common mistake is to plot retention by calendar month (January, February, March) instead of tenure month (month 1, month 2, month 3 post-signup). Calendar-month analysis mixes cohorts at different ages and hides cohort-specific patterns.

Not separating new business from expansion. Revenue retention above 100% requires expansion, which is good. But if you don't separate expansion from gross retention in reporting, you can hide gross retention problems. Always report both: GRR (no expansion) and NRR (with expansion). The gap shows expansion effectiveness; GRR alone shows stickiness.

Using too few cohorts. A retention curve fit to four cohorts is noise. The minimum for a useful analysis is 12 cohorts (one year); the standard is 24-36 cohorts for anything statistically meaningful.

Treating all cohorts equally in the average. Newer cohorts have fewer data points. Older cohorts have more. The average curve should be weighted by cohort size, not a simple mean. This matters especially if cohort size has grown significantly over time.

Hard-coding retention assumptions from the blended curve into a financial model. The financial model should use cohort-specific retention per segment and plan, not a single blended curve. A template that uses blended retention is fundamentally less accurate than one that uses cohort-tier logic.

Key takeaways

A cohort retention matrix in Excel is the single most valuable analytical artifact a SaaS operator can build. It replaces blended churn assumptions with real data, enables honest LTV calculations, and reveals which cohorts (and therefore which customer types) drive the business. The four-step build — structure data, SUMIFS the matrix, average the curve, fit and extract LTV — is a one-time investment that pays back every month.

For a pre-built cohort engine with fitted LTV and 2025 benchmarks, see the Meritra SaaS Financial Model. For the formulas behind the KPIs cohort retention produces, see our guides on NRR, GRR, LTV, and LTV:CAC.