Growth 22 min read Advanced

Subscription Churn Analysis in Excel: Calculate MRR, Retention & Cohorts

You gained 50 subscribers and lost 30. Is that good? Calculate monthly churn rate, retention curves, and MRR impact in Excel. Predict which customers will cancel before they do.

Platforms: Shopify Stripe WooCommerce Subscriptions
Subscription Churn Analysis in Excel: Calculate MRR, Retention & Cohorts

Why Subscription Churn Matters More Than You Think

Your monthly numbers:
  • New subscribers: 50
  • Canceled subscribers: 30
  • Net growth: +20 subscribers
You think: "We're growing! +20 is good!" The reality:
  • You have 200 active subscribers
  • 30 cancellations = 15% monthly churn
  • At 15% monthly churn, you lose 84% of customers within a year
  • You need 168 new subscribers/month just to stay flat
The math that kills subscription businesses: 
Month 1: 200 subscribers × (1 - 0.15) = 170 remain
Month 2: 170 × (1 - 0.15) = 145 remain
Month 3: 145 × (1 - 0.15) = 123 remain
...
Month 12: 32 remain (84% churned)
Every 1% of monthly churn costs you 11% of annual customers. This guide teaches you to calculate, track, and predict churn before it kills your business.

The 5 Critical Subscription Metrics

1. Monthly Churn Rate

Formula: 
Monthly Churn Rate = (Customers Lost in Month) / (Customers at Start of Month) × 100
Example:
  • Jan 1: 200 active subscribers
  • Jan 31: 30 cancellations
  • Churn rate: 30 / 200 × 100 = 15%
Benchmarks:
  • <3% monthly: Excellent (consumer subscriptions)
  • 3-5% monthly: Good
  • 5-7% monthly: Concerning
  • >7% monthly: Crisis mode

2. Revenue Churn Rate (More Important)

Why it matters: Losing 10 small customers ($10/mo) hurts less than losing 1 big customer ($500/mo). Formula: 
Revenue Churn = (MRR Lost from Cancellations) / (MRR at Start of Month) × 100
Example:
  • Jan 1 MRR: $10,000
  • Cancellations: 30 customers totaling $2,000 MRR
  • Revenue churn: $2,000 / $10,000 × 100 = 20%
Reality check: You lost 15% of customers but 20% of revenue. Your churning customers were higher-value.

3. Monthly Recurring Revenue (MRR)

Formula: 
MRR = Sum of all active monthly subscription values
For annual subscriptions: 
MRR = (Annual subscription price) / 12
MRR Movement: 
New MRR = Revenue from new subscribers
Expansion MRR = Revenue from upgrades/add-ons
Contraction MRR = Revenue from downgrades
Churned MRR = Revenue from cancellations

Net New MRR = New + Expansion - Contraction - Churned

4. Retention Rate (The Flip Side of Churn)

Formula: 
Retention Rate = 100% - Churn Rate
Example:
  • 15% churn = 85% retention
Why track both:
  • Churn shows problem magnitude
  • Retention shows what you're keeping
  • Goal: Maximize retention, not just minimize churn

5. Customer Lifetime (How Long Until They Cancel)

Formula: 
Average Customer Lifetime (months) = 1 / Monthly Churn Rate
Example:
  • 5% monthly churn = 1 / 0.05 = 20 months average lifetime
  • 10% monthly churn = 1 / 0.10 = 10 months average lifetime
Why it matters: Affects how much you can spend on acquisition.

Build a Subscription Churn Dashboard in Excel

1

Export Subscription Data

From Shopify/ReCharge/Stripe: Export all subscriptions with Customer ID, Subscription Start Date, Cancel Date (if any), Monthly Value. Save as CSV. You need historical data (6+ months).

Export Subscription Data
2

Create Customer Status Table

In Excel, create columns: Customer ID, Start Date, Cancel Date, Monthly Value, Status. Add Status column: =IF(ISBLANK(Cancel_Date), 'Active', 'Churned'). This flags who's still subscribed.

Create Customer Status Table
3

Calculate Monthly Churn by Month

Create summary table with months in rows. For each month: Customers at Start = COUNTIFS(Start_Date, '<=month_end', Cancel_Date, '>month_end' OR ISBLANK). Cancellations = COUNTIFS(Cancel_Date, month). Churn % = Cancellations / Start.

Calculate Monthly Churn by Month
4

Calculate MRR by Month

For each month: MRR = SUMIFS(Monthly_Value, Start_Date, '<=month_end', Cancel_Date, '>month_end' OR ISBLANK). This sums monthly value of all active subscriptions on last day of month.

Calculate MRR by Month
5

Build Cohort Retention Table

Rows = Cohort (month customer subscribed). Columns = Months since subscription (0, 1, 2, 3...). Each cell = % of cohort still active at that month. Shows which cohorts retain best and when cancellations typically happen.

Build Cohort Retention Table
6

Create Retention Curve Chart

Select cohort retention table → Insert → Line Chart. X-axis = Months since subscription. Y-axis = % retained. Each line = one cohort. Reveals if retention improving over time or typical drop-off points.

Create Retention Curve Chart
7

Add MRR Trend Chart

Create line chart: X-axis = Month. Y-axis = MRR. Add trend line to show if MRR growing/flat/declining. Add second chart showing MRR components: New, Expansion, Churned stacked.

Add MRR Trend Chart
8

Set Up Churn Alert Thresholds

Add conditional formatting: If monthly churn > 7%, cell turns red. If MRR declines month-over-month, cell turns red. Create alerts section at top of dashboard for at-a-glance health check.

Set Up Churn Alert Thresholds

Cohort Analysis: When Do Customers Actually Cancel?

What Cohort Analysis Reveals:

Most subscription businesses see this pattern:
  • Month 0-1: 5-10% cancel (immediate buyer's remorse)
  • Month 2-3: 15-25% cancel (didn't get expected value)
  • Month 4-6: 10-15% cancel (forgot about it or tried alternative)
  • Month 7-12: 5-10% cancel (steady churn)
  • Month 12+: 2-5% cancel (loyal customers)

Building a Cohort Table in Excel:

Step 1: Define Cohorts by Start Month Add column to customer data: 
Cohort = TEXT(Start_Date, "MMM YYYY")
Now each customer tagged with their start month: "Jan 2024", "Feb 2024", etc. Step 2: Calculate Months Since Subscription For each customer each month: 
Months_Since = DATEDIF(Start_Date, Analysis_Month_End, "M")
Step 3: Count Active Customers by Cohort by Month Create table:
  • Rows: Cohort (Jan 2024, Feb 2024, etc.)
  • Columns: Months Since Subscription (0, 1, 2, 3...)
  • Cells: % of original cohort still active
Formula for each cell: 
=COUNTIFS(Cohort, Row_Cohort, Months_Since, Column_Month, Status, "Active") / COUNTIFS(Cohort, Row_Cohort)
Example Table:
Cohort Month 0 Month 1 Month 2 Month 3 Month 6 Month 12
Jan 2024 100% 92% 85% 78% 65% 52%
Feb 2024 100% 94% 88% 82% 70% -
Mar 2024 100% 95% 90% 85% - -
What This Reveals:
  • Feb/Mar cohorts retain better than Jan
  • Biggest drop-offs: Month 1 (8-10%), Month 2 (7-8%)
  • By month 12, you keep ~50% of customers
  • Newer cohorts improving (learning from churn data)

MRR Analysis: The Revenue Health Check

MRR Movement Breakdown

Track 5 components that affect MRR: 1. New MRR:
  • New customers who signed up this month
  • Formula: `=SUMIFS(Monthly_Value, Start_Date, ">="&Month_Start, Start_Date, "<="&Month_End, Status, "Active")`
2. Expansion MRR:
  • Existing customers who upgraded
  • Track: Price changes where new > old for active customers
  • Formula: `=SUMIFS(Price_Increase, Change_Date, Month)`
3. Contraction MRR:
  • Existing customers who downgraded
  • Track: Price changes where new < old for active customers
  • Formula: `=SUMIFS(Price_Decrease, Change_Date, Month)`
4. Churned MRR:
  • Customers who canceled
  • Formula: `=SUMIFS(Monthly_Value, Cancel_Date, Month)`
5. Reactivation MRR:
  • Churned customers who came back
  • Track: Customers with cancel date AND new start date after cancel

Net New MRR Formula:

Net_New_MRR = New_MRR + Expansion_MRR + Reactivation_MRR - Contraction_MRR - Churned_MRR

Example Monthly MRR Movement:

Starting MRR (Jan 1):     $50,000
New MRR:                  +$8,000  (80 new customers × $100/mo)
Expansion MRR:            +$1,500  (15 upgrades avg $100 increase)
Reactivation MRR:         +$500    (5 returns × $100/mo)
Contraction MRR:          -$800    (8 downgrades avg $100 decrease)
Churned MRR:              -$6,000  (60 cancels × $100/mo)

Net New MRR:              +$3,200
Ending MRR (Jan 31):      $53,200

MRR Growth Rate:          6.4% ($3,200 / $50,000)

MRR Growth Rate Targets:

  • <5% monthly: Slow growth
  • 5-10% monthly: Healthy growth
  • 10-20% monthly: Fast growth
  • >20% monthly: Hypergrowth (unsustainable long-term)

Negative Churn (The Holy Grail):

When expansion MRR > churned MRR, you have negative churn. Example:
  • Churned MRR: -$6,000
  • Expansion MRR: +$7,000
  • Net: +$1,000 from existing customers
You grow even if you get zero new customers. Rare but powerful.

Predictive Churn: Identify At-Risk Customers Before They Cancel

Early Warning Signs (Churn Predictors):

1. Declining Usage/Engagement:
  • Subscription box: Orders skipped 2+ times
  • Software: Login frequency dropped 50%+
  • Content: Last login >30 days ago
2. Support Ticket Volume:
  • Multiple support requests in short period
  • Unresolved complaints
  • Negative sentiment in communications
3. Payment Issues:
  • Failed payment attempts
  • Expired credit card not updated
  • Downgraded after failed payment
4. Plan Changes:
  • Downgraded from premium to basic
  • Reduced quantity (3 boxes → 1 box)
  • Moved from annual to monthly (less commitment)
5. Cohort Risk Patterns:
  • Customer approaching high-churn month (month 3 if that's your pattern)
  • Belongs to historically high-churn cohort
  • Hasn't engaged since first month

Building a Churn Risk Score in Excel:

Assign Points for Risk Factors: 
Risk_Score = 
  IF(Last_Login > 30 days, 25, 0) +
  IF(Support_Tickets > 2, 20, 0) +
  IF(Payment_Failed = TRUE, 30, 0) +
  IF(Downgraded = TRUE, 15, 0) +
  IF(Months_Since < 4, 10, 0)
Risk Categories:
  • 0-20 points: Low risk (healthy customer)
  • 21-40 points: Medium risk (monitor)
  • 41-60 points: High risk (intervention needed)
  • 61+ points: Critical risk (likely to churn)
Action Matrix:
Risk Level Action Owner Timeline
Critical (61+) Personal outreach, discount offer Account Manager 24 hours
High (41-60) Email survey, offer help Customer Success 3 days
Medium (21-40) Educational content, engagement email Marketing 1 week
Low (0-20) Standard nurture Automated Ongoing
Metric Formula Good Target Bad Sign Action If Bad
Monthly Churn % Cancels / Start Customers <5% >7% Improve onboarding, reduce friction
MRR Growth % Net New MRR / Starting MRR >5% <0% Increase acquisition, reduce churn
Customer Lifetime 1 / Monthly Churn >20 months <10 months Fix product-market fit issues
LTV Avg Monthly Value × Lifetime >3× CAC <CAC Can't afford to acquire customers
Retention (Month 3) % of cohort still active >75% <60% Onboarding failure, fix first 90 days

Advanced: Calculate Customer Lifetime Value (LTV) from Churn

Simple LTV Formula:

LTV = Average Revenue Per User (ARPU) × Average Customer Lifetime

Where:
Average Customer Lifetime = 1 / Monthly Churn Rate

Example:

  • ARPU: $50/month
  • Monthly churn: 5%
  • Average lifetime: 1 / 0.05 = 20 months
  • LTV: $50 × 20 = $1,000

Why This Matters:

If LTV = $1,000, you can spend up to ~$333 to acquire a customer (targeting 3:1 LTV:CAC ratio).

More Accurate LTV (Accounting for Time Value):

LTV = (ARPU × Gross Margin %) / (Monthly Churn Rate + Discount Rate)
Example:
  • ARPU: $50
  • Gross margin: 80% (product costs 20% of revenue)
  • Monthly churn: 5% (0.05)
  • Discount rate: 1% monthly (0.01)
LTV = ($50 × 0.80) / (0.05 + 0.01) = $40 / 0.06 = $667 More conservative than simple formula ($667 vs $1,000).

Excel Formula:

=AVERAGE(Monthly_Value) * 0.8 / (Average_Churn_Rate + 0.01)

The Churn Death Spiral

At 10% monthly churn, you need 120 new customers/year just to replace 100 customers. Each replacement costs CAC. If CAC > LTV, you burn cash faster as you grow. This kills subscription businesses that focus only on growth, not retention.

Reducing Churn: The Retention Playbook

Phase 1: Onboarding (First 30 Days)

Goal: Get customer to "aha moment" fast Tactics:
  • Welcome series (5-7 emails over first month)
  • Onboarding checklist (get 3 wins in first week)
  • Personal check-in (founder/success manager email at day 7)
  • Usage milestones ("You've unlocked X feature!")
Excel Tracking:
  • % of customers who complete onboarding
  • Churn rate: Completed onboarding vs didn't
  • Typical: 3-5× lower churn if onboarded properly

Phase 2: Value Reinforcement (Days 30-90)

Goal: Build habit, demonstrate ongoing value Tactics:
  • Weekly wins emails ("You saved $X this month")
  • Educational content matching their use case
  • Feature spotlights (show underused features)
  • Community building (user group, forum)
Excel Tracking:
  • Engagement score (logins, feature usage)
  • Cohort retention curves (is month 3 drop-off decreasing?)

Phase 3: Loyalty & Expansion (90+ Days)

Goal: Upsell, prevent boredom, create advocates Tactics:
  • Upgrade prompts based on usage patterns
  • Annual plan incentive (2 months free)
  • Referral program (advocate creation)
  • Exclusive features for long-term customers
Excel Tracking:
  • Expansion MRR from existing customers
  • Negative churn rate (expansion > churn)

Rescue Mission: Cancellation Flow

When customer clicks "Cancel", offer: 1. Pause option: "Take a break, come back later" 2. Downgrade option: "Try smaller plan" 3. Discount offer: "Stay for 50% off next 3 months" 4. Exit survey: "What would make you stay?" (data gold) Excel Tracking:
  • Cancellation reason categories
  • Save rate from cancellation flow interventions

Common Mistakes to Avoid

Mistake: Calculating churn rate wrong: using end-of-month customers instead of start-of-month
Solution: Churn = Cancels / Customers at START of period. If you gained 50 and lost 30, denominator is start count, not end count.
Mistake: Ignoring revenue churn vs customer churn
Solution: Losing 10 small customers is different from losing 1 whale. Track both customer count churn AND revenue churn (MRR lost).
Mistake: Not segmenting churn by cohort or plan type
Solution: Overall churn hides patterns. Segment by: acquisition month, plan tier, acquisition channel. Different segments need different retention tactics.
Mistake: Treating all churn as equal - some customers you WANT to lose
Solution: Low-value, high-support customers who churn might improve your unit economics. Focus retention on high-LTV segments.

Verification Checklist

  • Your monthly churn rate stays consistent month-to-month (or shows improving trend)
  • MRR growth rate = (New + Expansion - Churn - Contraction) / Starting MRR matches your manual calculation
  • Cohort retention table shows clear patterns of when customers typically cancel
  • LTV calculation (ARPU / Churn Rate) gives you reasonable CAC budget (LTV should be 3-5× CAC)
  • Sum of all customer monthly values = your total MRR for that month

Frequently Asked Questions

Consumer subscription boxes: 5-8% monthly is typical. Under 5% is excellent. Above 10% is unsustainable. SaaS has lower benchmarks (2-5%).
Treat paused subscriptions separately. Track: Active, Paused, Churned. Calculate 'active churn' (canceled / active) and 'pause rate' (paused / active). Paused customers often come back.
If LTV &lt; 3× CAC, fix churn first. Acquiring customers you can't retain burns cash. Once retention is healthy (LTV &gt; 3× CAC), then scale acquisition.
Minimum 6 months for patterns. Ideal: 12+ months to see full customer lifecycle. Less than 3 months is too noisy to draw conclusions.
Calculate rolling 3-month average churn to smooth noise. Wild swings indicate: small sample size, seasonal patterns, or major product/pricing changes.