How to Calculate Customer Churn Rate Using Formula in Excel

The Churn Rate measures how many customers were lost over a specific period of time. This is a critical metric in subscription-based industries but is also applied in other revenue models. If your company's growth rate (i.e., the influx of new customers) is higher than its churn rate, it means that the number of customers is increasing. Conversely, if churn is higher, the company is losing customers faster than it can acquire new ones, indicating a need for action.



How to Calculate Customer Churn for a Specific Period in Excel

The image below shows a table that calculates the churn rate for a company with monthly revenue. To perform these calculations, you need the number of customers at the start and end of the month, as well as the number of new customers acquired.

Source Data Table:

ItemsIndicators
Number of Clients at the Beginning of the Month4,215
New Clients7,415
Number of Clients at the End of the Month10,664
Number of Lost Clients966
Customer Churn Rate12.98%
Calculate customer churn.

To determine the number of customers lost during the month: add the number of new customers to the number of existing customers at the start of the month. Then, subtract the number of customers at the end of the month from the result. Finally, divide the number of customers lost during the month by the number of customers at the start of the month. This is how the customer churn rate is calculated.

In this example, the final churn rate is 9.21%. Since more new customers were acquired than were lost, the churn rate does not present any issues for the company. However, if the churn exceeds expectations, the company should identify the reasons for customer loss and adjust the product or other aspects of its operations.

Customer Churn Analysis Over a Year for Monthly Revenue Companies

If a company generates monthly revenue, its customers likely sign contracts and pay for services each month. In such cases, it is important to calculate the monthly churn rate regularly. Customers acquired in a given month will not churn immediately since they have already paid in advance for services for the entire month.

However, for companies like magazine publishers, mobile operators, or internet service providers where subscribers purchase annual subscriptions, it is more logical to calculate the yearly churn rate. If you need to calculate churn over a longer period than the revenue period, you should use a different formula. For example, to calculate the annual churn rate for customers who pay monthly, the image below shows the calculation of the yearly churn rate:

Annual customer churn analysis.

Download the customer churn rate calculation example in Excel download file

The number of lost subscribers is divided by the average number of subscribers at the beginning and end of the year. The churn period differs from the revenue period, meaning that some of the 7,415 subscribers canceled their subscription throughout the year, but after the month in which they originally signed up.