Memberships live and die by retention. Member churn is cancellations over members at the start; retention is its complement — the metric that compounds into recurring revenue.
The example
12 cancelled of 400.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Cancelled | 12 |
| 3 | At start | 400 → 3% |
The formula
The formula:
How it works
How it works:
- Divide members cancelled by members at the start of the period.
- Retention =
1 - churn; average membership length ≈1 / churn. - A 3% monthly churn means the average member stays about 33 months.
- Tie churn to member lifetime value: lower churn lifts LTV across the whole base.
Educational use only. These are general Excel formulas for learning spreadsheet techniques — estimates only, not training, medical, or nutrition advice. Individual results vary; consult a qualified professional before acting on them.
Try it: interactive demo
Cancellations and members at start.
Variations
Retention rate
The flip side:
Average membership length
In months:
Member LTV
Value of a member:
Pitfalls & errors
Starting count. Divide by members at the start, not the average.
Monthly compounds. Don’t just ×12 for annual — use 1-(1-m)^12.
Pauses vs cancels. Decide whether frozen memberships count as churn.
Practice workbook
Frequently asked questions
How do I calculate gym member churn in Excel?
How long does the average member stay?
How does churn relate to member value?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class