This customer lifetime value calculator can be used to calculate the value of the future profits earned from a monthly subscription customer.
Lifetime Value of a Customer
A business operating a recurring monthly payment plan model (such as a saas subscription based business), will retain a customer for a period of time before they eventually cancel their subscription and leave (churn).
The value of that customer to the business will depend on how many months the customer is retained for, the profit they generate each month, and the cost the business has to pay for capital to fund its operations. The purpose of this customer lifetime value calculator is to help forecast this lifetime value.
Using the Customer Lifetime Value Calculator
By entering details of the churn rate, revenue and revenue growth rate, gross margin percentage, and the discount rate, the customer lifetime value calculator can be used to estimate the value of the profit earned from the customer during their lifetime with the business.
Customer Lifetime Value Calculation Inputs
The customer lifetime value calculator is used by entering information as follows.
1. Enter the Churn Rate
The churn rate is the rate per month at which customers cancel their subscription service. Either enter an estimate based on industry comparisons or if historical information is available, calculate the rate using the following formula.
So for example, if the cancellations during the month chosen are 90 and the customers at the start of the month are 2,000, then the churn rate is calculated as follows.
Churn rate = Cancellations during the month / Customers at the start of the month Churn rate = 90 / 2,000 = 4.50%
Negative Churn Rate
The churn rate is normally entered as a positive number indicating that the customer base will decline. If the churn rate is entered as a negative amount it implies viral growth. In these circumstances, depending on the values set for the revenue growth rate and the discount rate (see steps below), the lifetime value of a customer may well (in theory) be infinite, in which case the calculator will indicate this.
For additional information, the customer lifetime value calculator also shows the rate at which customers are retained each month. The retention rate is simple calculated as follows
Customer Gross Margin
2. Enter the Customer Revenue
Enter the amount of recurring revenue paid each month by a customer. For example, if the current monthly subscription for the service is 250, enter 250. If there is more than one level of subscription then enter the average revenue for all subscriptions, sometimes referred to as the average revenue per user or ARPU.
3. Enter the Gross Margin Percentage
Enter the gross margin percentage for the product or service used by the customer.
The gross margin is the real income a business earns by selling its products and services, it is the revenue left after deducting the variable cost of sales.
The variable costs to include in the customer lifetime value calculator are identified by checking whether the cost varies with demand for the product or service. Further details of what to include in the cost of sales for different types of businesses are available in our cost of revenue post.
The gross margin percentage is then calculated by expressing the gross margin as a percentage of revenue.
For example if the revenue from a customer subscription is 250, and the cost of sales is 75, then the gross margin percentage is calculated as follows.
Gross margin % = (Revenue - Costs of sale) / Revenue Gross margin % = (250 - 75) /250 = 70%
In this example, the gross margin percentage is 70% and this amount should be entered in the calculator.
The customer lifetime value calculator calculates the current gross margin of the product.
It should be noted that if you simply want to calculate the customer lifetime value based on the revenue generated by the customer then enter a gross margin percentage of 100%.
4. Enter the Revenue Growth Rate
The revenue growth rate is the percentage by which the monthly revenue subscription entered in #2. above grows each month. The growth can be for a number of reasons, but typically reflects unit subscription price increases or perhaps a change of mix in the subscriptions offered causing the average unit price to increase.
For example, if the starting average customer revenue is entered as 250.00 and is expected to grow at the rate of 1.50% each month, the calculator will calculate the following months revenue as 250 x 101.50% = 253.75, and the next month as 253.75 x 101.50% = 257.56 and so on.
Customer Lifetime Value Calculation
5. Enter the Discount Rate
The profits from each customer occur over a period of months depending on the churn rate set. In order to calculate the lifetime value of the customer these profits need to be discounted back to their values today. The discount rate to be used depends on the cost of capital the business has to pay to finance its operations. Since the capital will usually be a combination of debt and equity, the weighted average cost of capital (WACC) is normally used as the discount rate.
Enter the monthly discount rate used by the business. For information purposes the calculator also shows the equivalent annual rate to allow comparisons to be made with the cost of capital discussed above.
The customer lifetime value calculator calculates the lifetime value of the profits earned from the customer.
It should be noted that the customer lifetime value formula used in the calculator depends on the relative values of the churn rate, growth rate, and discount rate. In certain circumstances the effect of the combination of the three rates causes the customer lifetime value to grow (in theory) to an infinite amount. When this happens the calculator will indicate an infinite lifetime value.
It should be noted that. while you may be one of the lucky businesses to experience this type of growth, it usually implies that at least one of the three rates entered in the lifetime value calculator needs adjusting to more accurately reflect the true circumstances of the operation.
Customer Lifetime Value Calculator Download
The customer lifetime value calculator is available for download in Excel format by following the link below.
Users use this customer lifetime value calculator at their own risk. We make no warranty or representation as to its accuracy and we are covered by the terms of our legal disclaimer, which you are deemed to have read. This is an example of a customer lifetime value calculation that you might use when preparing subscription model financial projections in Excel. It is purely illustrative of customer lifetime value software. This is not intended to reflect general standards or targets for any particular company or sector. If you do spot a mistake in the lifetime customer value calculator, please let us know and we will try to fix it.
About the Author
Chartered accountant Michael Brown is the founder and CEO of Plan Projections. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a BSc from Loughborough University.