Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate churn rate in Excel and Google Sheets
Topic:SaaS metricsExcel & Google Sheets
=IFERROR((A2-B2)/A2*100,0)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| StartCustomers | EndCustomers | ChurnPct |
| 1200 | 1140 | =IFERROR((A2-B2)/A2*100,0) |
| 1140 | 1095 | =IFERROR((A3-B3)/A3*100,0) |
| 1095 | 1080 | =IFERROR((A4-B4)/A4*100,0) |
=IFERROR((A2-B2)/A2*100,0)→5
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Set up a table: column A = customers at start of period, column B = customers at end of period, one row per month.
- 2In C2, enter the churn rate formula: =(A2-B2)/A2*100. This gives the percentage of customers lost.
- 3Wrap with IFERROR to handle months with no starting customers: =IFERROR((A2-B2)/A2*100,0).
- 4Copy C2 down for each month. Format column C as Number with 2 decimal places.
- 5For annual churn from monthly rates, compound: =1-PRODUCT(1-C2:C13/100) gives the annual churn rate given 12 monthly rates in C2:C13.
Tips
- Distinguish gross churn (customers lost) from net churn (customers lost minus new customers gained). The formula above is gross churn; net churn subtracts new acquisitions from the numerator.
- New customers who join and leave within the same period should not be counted in the starting cohort — track them separately to avoid artificially inflating churn.
- Plot churn rate as a line chart over 12+ months to spot seasonality or product-release effects more clearly than looking at a table of numbers.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What is a good churn rate?
It depends heavily on the industry. SaaS B2B targets are typically 1–3% monthly; consumer apps tolerate higher rates. Always benchmark against your specific segment.
How do I calculate revenue churn instead of customer churn?
Replace the customer counts with MRR (Monthly Recurring Revenue): (MRR at start − MRR at end) / MRR at start × 100. The formula structure is identical.
Is there a difference between churn rate and retention rate?
Retention rate = 1 − churn rate (or 100% − churn%). They are complementary; some teams find it easier to set retention targets (e.g. 97% monthly retention = 3% monthly churn).
More on SaaS metrics
See all →Reference
RANDARRAY
Spills a grid of random numbers with specified rows, columns, min, max, and integer/decimal options. Excel 365 and Google Sheets only.
How-toHow to calculate monthly recurring revenue (MRR)
Learn how to calculate MRR in Excel and Google Sheets using SUMIF to sum active subscription revenue by billing period, normalized to a monthly figure.
Error fixFix #####
Fix ##### in Excel — the column is too narrow to display the number or date. Widen the column or change the format to resolve it.
ReferenceARRAYTOTEXT
Converts an array or range into a single text string, with concise or strict formatting options.
How-toHow to calculate annual recurring revenue (ARR)
Calculate ARR in Excel and Google Sheets by summing normalized annual subscription revenue across all active accounts, converting monthly contracts to their annual equivalent.
Error fixFix Array result
Array result was not expanded in Google Sheets means output cells are blocked. Clear adjacent cells or use a blank area.