How to calculate churn rate in Excel

Step 1: Monthly churn from a summary table

If your billing tool gives you monthly numbers, set up one row per month: customers at the start, new sign-ups, cancellations. Customers at the end is =B2+C2-D2, and next month's start is this month's end (=E2), so you type only New and Cancelled each month.

Churn for each month is cancellations / customers at the start. June is 15 / 380 = 3.9%, and October is 27 / 450 = 6.0%, the worst of the five months. Format column F as a percentage with one decimal.

Divide by the start, not the end. The end count includes new sign-ups who arrived during the month, which hides churn when you're growing fast.

Step 2: Yearly churn: compound it, don't multiply

The average monthly churn here is 4.73%. Times 12 that's 56.8%, but that overstates it. Each month you lose 4.73% of a smaller base, so the losses compound. Retention for the year is (1 - 4.73%)^12 = 55.9%, which means yearly churn is 44.1%.

The same idea works for the months you have: =PRODUCT(1-F2:F6) gives 78.5%, the share of a starting group you'd expect to keep after these five months at these rates. In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter.

Should you average the monthly rates or pool the counts? Pooled churn is total cancellations / total starting customers: 100 / 2,097 = 4.77%, close to the 4.73% average here. They split apart when one month is much bigger than the others. Pooled weights each customer equally, so it's the safer choice.

Step 3: Churn from a list of start and cancel dates

Often you only have an export: one row per customer with a start date and a cancel date that's blank while they're active. Put the month's first and last day in O2 and O3.

Customers at the start are those who started before the month and either have no cancel date or cancelled on or after the 1st. As with headcount, COUNTIFS can't do "blank OR later", so add two counts. That gives 10.

Lost customers are those who were active at the start and cancelled during the month: C-104, C-105 and C-110, so 3. C-113 signed up on Oct 6 and cancelled on Oct 29. It wasn't in the start count, so the standard rate leaves it out, even though there are 4 cancellations in October. Track same-month cancels separately: they usually point to an onboarding problem, not a retention one.

Step 4: Revenue churn vs customer churn

Customer churn treats every customer the same. Revenue churn weighs them by what they pay. Swap COUNTIFS for SUMIFS on the MRR (monthly recurring revenue) column with the same conditions.

The customers active on Oct 1 paid 440 a month. The three who left paid 167 between them, because one of them, C-110, was on the 99 Team plan. Customer churn is 30%, but revenue churn is 38%. When the bigger accounts are the ones leaving, revenue churn is the number to watch.

This is gross revenue churn: it ignores upgrades. If you track plan changes, net revenue churn subtracts the extra MRR from upgrades, and it can go below zero when upgrades outweigh losses.

Step 5: A simple cohort view

A cohort is everyone who signed up in the same period. Count each cohort, then count how many are still active (blank cancel date). Of the 6 customers who joined from June to August, 3 are still here, so that cohort's retention is 50%.

Put the cohort months down column A and use the formula with each row's month start and end. Comparing cohorts tells you whether a change you made, like a new onboarding email, keeps people longer.