How to Build a Cohort Revenue Model in Excel

A cohort revenue model groups customers by the month they joined and tracks each group as it ages and decays. Laid out as a triangle, every cohort starts at full size and retains a shrinking share of revenue over time. Summing down each calendar column rolls every cohort's contribution into total revenue, giving a bottoms-up forecast grounded in real retention behavior.

What a cohort model does

A cohort is a set of customers acquired in the same period, say all the customers who signed up in January. The model tracks each cohort separately as it ages, applying a retention curve that captures how revenue from that group decays month by month.

It fits the model as a high-fidelity revenue forecast. Instead of a single blended churn rate, it lets early and late cohorts retain differently and makes the revenue mix transparent. Summing every active cohort in a given calendar month produces total revenue for that month, which then feeds the income statement.

Build it step by step

Lay out acquisition cohorts down the rows, one per month, and calendar months across the columns. Each cohort starts at its initial revenue in its acquisition month and steps down the diagonal as it ages, forming a triangle.

Drive each cell from the cohort's starting revenue and a retention factor based on the number of months since acquisition.

1. List each cohort's starting revenue: new customers times average revenue per customer.
2. Build a retention curve by age: month 0 at 100 percent, then declining percentages.
3. Cohort revenue in a month is =cohort_starting_revenue * retention[age], where age is months since acquisition.
4. Lay the cohorts on a diagonal so each ages one column to the right.
5. Total revenue for a calendar month is the column sum: =SUM of all active cohorts that month.

=90+82 plus the Mar cohort sums the column into 172 of total revenue.

The formulas that build the triangle

Each cell multiplies a cohort's starting revenue by its retention at the current age: =cohort_start * retention_factor. The retention factor is indexed by months since acquisition, so a cohort in its third month uses the month-3 retention value regardless of which calendar month that falls in. An INDEX or OFFSET against the retention curve keyed on =current_month - cohort_month keeps the diagonal consistent.

The total revenue line is a vertical sum down each calendar column: =SUM(all_cohort_cells_in_column). Because cohorts enter on a staggered diagonal, early columns sum few cohorts and later columns sum many, which is why revenue grows even when each individual cohort decays. The triangle shape is the visual proof that the math is set up correctly.

• Cohort revenue: =cohort_start_revenue * retention[months_since_acquisition].
• Cohort age: =current_calendar_month - cohort_start_month.
• Retention lookup: =INDEX(retention_curve, age + 1).
• Total monthly revenue: =SUM of every active cohort in that column.

Pitfalls and what reviewers check

The most common error is a misaligned diagonal, where a cohort's revenue lands in the wrong calendar month and the age index drifts. Driving the age from =current_month - cohort_month rather than dragging a fixed offset prevents this, because every cell computes its own age explicitly.

Reviewers check that the column totals reconcile to total revenue, that each cohort's age-0 cell sits in its acquisition month, and that the retention curve is a single shared input rather than retention percentages hardcoded into individual cells. A cohort triangle riddled with pasted numbers cannot flex when the retention assumption changes, so clean inputs are what make the model reusable.