Excel templates · SaaS & subscriptions
Cohort Analysis Template (Excel) — Retention & Payback
One cohort followed across seven months: how many are still there, what they are worth cumulatively, and the month the cumulative revenue per signup passes what you paid to acquire them.
The download
Free download
Download: Churn & cohort analysis
- A retention curve, indexed to the cohort's first month
- Monthly churn, so you see where the curve flattens
- Cumulative revenue per signup, not per active customer
- CAC payback as a percentage that crosses 100%
No email, no sign-up. Formulas already wired up.
The sheet itself
- A retention curve, indexed to the cohort's first month
- Monthly churn, so you see where the curve flattens
- Cumulative revenue per signup, not per active customer
- CAC payback as a percentage that crosses 100%
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Churn & cohort analysis | |||||||||||||
| 2 | Fill in the blue cells — every other cell is a formula and works itself out. foreqast.app | |||||||||||||
| 3 | M0 | M1 | M2 | M3 | M4 | M5 | M6 | |||||||
| 4 | ||||||||||||||
| 5 | 100 | 88 | 79 | 73 | 69 | 66 | 64 | |||||||
| 6 | 100.0 % | 88.0 % | 79.0 % | 73.0 % | 69.0 % | 66.0 % | 64.0 % | |||||||
| 7 | 0 | 12 | 9 | 6 | 4 | 3 | 2 | |||||||
| 8 | 0.0 % | 12.0 % | 10.2 % | 7.6 % | 5.5 % | 4.3 % | 3.0 % | |||||||
| 9 | ||||||||||||||
| 10 | €89.00 | €89.00 | €89.00 | €89.00 | €89.00 | €89.00 | €89.00 | |||||||
| 11 | €8,900 | €7,832 | €7,031 | €6,497 | €6,141 | €5,874 | €5,696 | |||||||
| 12 | €8,900 | €16,732 | €23,763 | €30,260 | €36,401 | €42,275 | €47,971 | |||||||
| 13 | €89.00 | €167.32 | €237.63 | €302.60 | €364.01 | €422.75 | €479.71 | |||||||
| 14 | ||||||||||||||
| 15 | €210.00 | €210.00 | €210.00 | €210.00 | €210.00 | €210.00 | €210.00 | |||||||
| 16 | 42.4 % | 79.7 % | 113.2 % | 144.1 % | 173.3 % | 201.3 % | 228.4 % | |||||||
The blue cells are yours to fill in. Every other cell is a formula and works itself out.
How to use it
- 1
Take one month's signups and follow only them
That is what makes it a cohort. Adding later signups into the same row turns it back into a customer count, which tells you nothing about retention.
- 2
Index retention to month 0, always
Retention is against the cohort's original size, not against last month. The formula uses an absolute reference for exactly that reason.
- 3
Find the month the curve flattens
Most churn happens early. The month monthly churn drops below about 4% is your real retention floor, and everything before it is an onboarding problem.
- 4
Read where payback crosses 100%
That is the month the cohort stops costing you money. Anything under six months means you can spend faster; over twelve and growth is funded by your balance sheet.
A worked example
A cohort of 100 at €89 a month
- Retention, month 1
- 88.0%
- Monthly churn, month 1
- 12.0%
- Monthly churn, month 6
- 3.0%
- Cumulative revenue per signup, month 2
- €237.63
- CAC payback reached
- Month 3
Twelve percent leave in the first month and three percent in the sixth: the cohort's real problem is onboarding, not the product. Payback arrives in month 3, which is fast — but 12% of the cohort never reaches it, so the acquisition cost of those customers is pure loss. Fixing the first month is worth more than any pricing change.
The mistakes that break it
Dividing cumulative revenue by active customers
That measures the survivors and gets better as more people leave. Divide by the original cohort size, or the metric lies in your favour.
Reading one cohort as the whole business
A cohort from a discount campaign churns very differently from one from organic search. Run one sheet per acquisition channel.
Stopping the analysis at six months
The interesting part of a subscription business is month 12 to 24, when the flat part of the curve decides the LTV.
Where the template stops
One cohort at a time
A real cohort analysis is a triangle: every signup month on its own row, ageing across the columns. Building that by hand in Excel is where most attempts stop.
Retention is not revenue retention
Customers who stay but downgrade look identical here. Net revenue retention needs the euros, not the headcount.
MRR & ARR trackerIt is only as current as your last evening with it
Every number in here is typed in. The month you skip is the month the sheet quietly stops describing your business — and it never says so.
What comes after the spreadsheet
Losing a clientTake the biggest customer out and see what is left — in cash, per month, on the dates it actually happens.Calculators