Chapter 15: Using Formulas for Financial Analysis
15
Calculating annual churn rate
If a business has monthly recurring revenue, it means that customers sign up and pay for
one month at a time. For those companies, it makes sense to calculate the churn rate on
a monthly basis. Any new customers during the month will not churn in the same month
because they’ve already paid for the month.
A typical magazine, however, signs up subscribers for an annual subscription. A meaningful
churn rate calculation for them would be an annual churn rate. If a business wants to cal-
culate a churn rate for a longer period than its recurring revenue model, such as calculat-
ing an annual churn for a business with monthly subscribers, the formula changes slightly.
Figure 15.8 shows an annual churn rate calculation.
Annual churn rate: =C6/AVERAGE(C2,C4)FIGURE 15.8
Annual churn rate of monthly recurring revenue
The number of lost subscribers is divided by the average of beginning and ending subscrib-
ers. Because the period of the churn rate is different than the period of the recurring
revenue, some of those 7,415 new subscribers canceled their subscriptions within the year,
albeit in a later month than they first subscribed.
Calculating average customer lifetime value
Customer lifetime value (CLV) is a calculation that estimates the gross margin contributed by
one customer over that customer’s life. The churn rate calculated in Figure 15.7 is a compo-
nent of CLV.
Figure 15.9 shows a calculation of CLV using the churn rate previously calculated. The first
step is to calculate the average gross margin per customer.
Gross Margin
=F2-F3