Free CLV Excel Template

 

Click this link to download the free CLV Excel spreadsheet template… free-clv-template-download-Excel

How to Use the Free Customer Lifetime Value (CLV) Template

There are four steps to follow on the spreadsheet. When you open the CLV template (after downloading the file from the above link) you should have a spreadsheet that looks like this…

image of free Excel template

Step One: Enter Acquisition Costs PER New Customer

In this step, you need to enter two numbers in the gold cells. The first number needed is the total spend on new customer acquisition.

Directly under that number, you need to input the number of new customers acquired. The new customer acquisition cost is then automatically calculated.

As it says in the note on the side, if you already know the average acquisition cost, then simply enter it as the total promotional spend and then set the number of new customers to 1.

Step Two: Select and Enter the Discount Rate

Customer lifetime value discount rate

Step two is an optional, but recommended, step in the calculation of customer lifetime value. Most firms will have an average return on their investments, or possibly use a hurdle rate to evaluate new investment opportunities – this rate should be used.

If this is not available, usually as a rate of 10 to 20% is appropriate depending upon the investment return required. Please review articles on discount rates.

 Step Three: Enter customer revenues and costs

Image of revenues and costs four customer lifetime value

As you can see from the above diagram, there are four rows to complete – the first is customer revenues, which is the total income received from the average customer across all products and services.

The average customer product cost (underneath) is the variable cost required to provide those products and services to the average customer.

Customer revenue less the average customer product costs automatically calculates the gross profit contribution per customer each year (in the first blue row).

Underneath the gross profit contribution there are two more rows of costs. The first is for any loyalty/retention costs and any up selling costs. Basically, these are the costs of marketing investments aimed at existing customers in order to grow and hold the business.Please refer to the article on retention goals.

The final row is actually a cost saving – which is appropriate to use when the brand has supporting customers that refer new business (non-customers) – so word-of-mouth (WOM) actually saves the firm money by reducing acquisition costs. Please refer to the article on word-of-mouth cost savings.

Step Four: Enter customer retention rate each year

Customer retention rates

For the fourth step in using the free CLV template, you only need to enter the annual customer retention (or loyalty) rate. The customer lifetime in years is then automatically calculated for you. It is more likely that a firm would know its retention or loyalty rate, rather than its lifetime period (in years) for customers.

A formula can generally be used to calculate the second number. The formula is 1/(1 – retention rate). For example, if the retention rate was 80%, then the formula would be 1/(1-0.8) = 1/0.2 = 5 years.

In the spreadsheet calculation, customer retention rate is used as an estimate of probability of receiving the future customer cash flows (that is, revenues and costs).

Related topics

Video on using the free CLV Excel template

The customer lifetime value formula

Quick customer lifetime value calculator

Full customer lifetime value calculator

CLV Banking Template