Example of a Customer Lifetime Value Calculation

A step-by-step example of the customer lifetime value calculation

Please note that there is also another example in the article explaining the customer lifetime value formula.

In this example, the calculations have been summarized from a spreadsheet on a step-by-step basis. Please refer to the free Excel CLV templates on this site.

Example of a Customer Lifetime Value Calculation

In this example, the following assumptions have been made:

  • that the initial acquisition cost per customer is $250
  • that the average customer revenue starts at $500 in year one and increases by $50 per year – rising to $950 by year 10
    customer costs have been set at 50% of revenue – therefore, it costs the firm in (in year one) $250 in product and service costs to generate $500 revenue
  • only customer revenues and costs are considered up to year 10, with any subsequent revenues being disregarded in the calculation
  • a retention rate of 75% has been used
  • and a discount rate of 10% has been applied

Each of the numbers will be discussed below, but the key number – customer lifetime value – has been shown in the bottom right yellow cell and is equal to $583.

example clv calculation
Click to enlarge CLV example

Understanding the Customer Lifetime Value Calculation

Acquisition Costs

The first row simply shows acquisition costs per customer. In this case the firm has determined that their overall promotional, sales and set-up costs per customer is equal to this amount. You should note that this expenditure/marketing investment has been incurred in year zero. This is because there is a lag time between promotional campaigns and the generation of first-time customers. Therefore, acquisition costs typically set in year zero with revenues coming on-stream the following year.

Customer Profit Contribution

The next two rows show the average per customer revenue and the average costs incurred by the organization to obtain that revenue – resulting in the fourth row, which is the difference between those two and is the average profit contribution per active customer.

It is important to note that these three rows show revenues, costs and profit contribution for current/active customers only. That is, for continuing/existing customers. It is further down in the calculation that considers the retention/churn rate of customers.

Retention (loyalty) Rate

As you can then see, the fifth row of numbers (retention rate) reduces by a factor of 25% each year. In the first year, we assume that all first-time customers have purchased from the firm – which they would need to in order to be considered customers. In the second year, we have retained 75% of those, and in the third year we have retained 75% of the 75% (which is equal to 56% of customers).

Even with a relatively high retention rate of 75%, by year 6, only around 24% of those customers acquired have remained loyal. This calculates the following row, which is profit contribution of retained customers. This is calculated by the retention percentage of that year – say in year 6 (of 24%) multiplied by the product contribution received from active customers in that year (which is $375). This works out to be 24% X $375 = $89.

In other words, for the customers that have remained loyal, the firm is making $375 in profit contribution – but there are only around a quarter of them who are still existing customers – which means we have to reduce the profit contribution accordingly.

The best way to think about the role of the retention rate in the customer lifetime value calculation is to consider that it estimates the percentage probability of the firm receiving the customer’s revenue in future years.

Discount Rate

The next row shows the discount rate, which is compound at 10% per annum. The average profit contribution of a retained customer – say $89 in years six – is then divided by the discount rate, in order to adjust to present day value. As year six is a reasonable distance into the future, the customer lifetime value calculation reduces it to $50 (as an equivalent to today’s money for the organization).

Customer Lifetime Value (CLV)

The running total along the bottom row shows the cumulative customer lifetime value over time. As the firm has decided to only consider 10 years of revenue and costs, the end customer lifetime value calculation works out to be $583.

Graphing the customer lifetime value example

Using the example above, let’s graph three rows from the table (see below). The blue line trending upwards is the profit contribution of any active/loyal customers over time. In the example, the company was working to increase revenues during the life the customer.

The red line is trending downwards. This is the profit contribution per customer (that is, the blue line) adjusted for customer retention rates. Over time, this line will eventually run to zero dollars as all customers will cease to be loyal at some time.

The green line is the cumulative customer lifetime value calculation.

Graph of a CLV example calculation
Click to enlarge CLV graph

 

The key points to note about the pattern of the cumulative customer lifetime value line are:

  1. It starts negative, due to the upfront acquisition costs,
  2. In this case, acquisition costs are not fully paid back until sometime in year three, and
  3. Eventually the customer lifetime value will flatten, due to the combined effects of churn of customers and the application of a discount rate.