Click this link to download the free CLV Excel spreadsheet template… free-clv-template-for-download
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…
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
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
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
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