Using Excel to Calculate Customer Lifetime Value 

Thanks Keshav = glad it was helpful.

1. Retention rate is critical for identifying customer lifetime value, as highlighted in the video. Typically firms would use their historical/current retention rate as the foundation of their forecasts. Many firms would have some inbuilt loyalty/retention marketing activities, and of course, retention rates would slightly increase over time with the customer cohort has dissatisfied customers are more likely to leave earlier. 2. I have used a five year horizon in this video simply for the sake of simplicity and explaining the concept. However, in real life you could have an unlimited horizon. For the Excel template I have on the website, I provide customer lifetime value for 10 years and also for 50 years. While 50 years may seem a long period, in theory service firms such as banks and insurance companies, accounting firms, doctors, and so on, could have customers for their entire adult lifetime. However, if you utilize the Excel template you will see that there is minimal difference between a 10 year and a 50 year customer lifetime value, simply because the likelihood of the customer continuing over that time (which is the cumulative retention rate) is so low past the 10 year period. Therefore, I would suggest that a 10 year horizon for any customer across any consumer focused business would be quite acceptable and would be unlikely to vary significantly even if you took a longer time frame. Certainly beyond the 10 year horizon, there is a lot of competitive and environmental uncertainty anyway, which is likely to reduce the credibility of your forecast within a firm. However, calculating customer lifetime value in the business-to-business market may look at a longer time frame, particularly if long-term contracts are being put in place.

Perfect! Thanks for taking the time to explain Geoff. Super clear, especially the reasoning behind time horizon.

Hi Tanya - It is possible, as you have profit per customer and acquisition costs. You will need to estimate retention/churn rate, but the business may have some idea of that as their customers roll into year 2. And if they have data per customer, they may be able to have a good estimation of retention. They would also have data on cross-selling of other products to input to the CLLV calculation. Hope this helps as a start. Geoff

Retention/churn rates are an annual metric. They are used to determine the probability of keeping customers in future years. Example - at a churn rate of 80% each year, at year 2 there is a 64% probability of keeping the customer (80% X 80%). We look at customer segments or customer acquired from a certain channel/promotion, rather than the overall customer base - which means we will have multiple CLVs. A number of these issues are addressed on www.clv-calculator.com

MktgStudyGuide thank you for clearing that up!

I have an article on this at ...www.clv-calculator.com/customer-retention/converting-retention-rate/ Basically you are inverting the fraction. Say the churn rate is 25%, which is 1/4 - when you divide into 1 - as follows 1/(1/4) it becomes 4. And 1/5 becomes 5 and so on. Hope this helps - please see the article as well.

Marek Fajer Hi Marek, That's right - the acquisition cost is a negative cash flow. I tend to use the absolute number and then subtract it in the formula, but it could be shown as a negative value. Thanks, Geoff

+MktgStudyGuide Had the same question. Thanks for clarifying

Yes, the built-in net present value formula in Excel will deliver the same result.

Hi Scott, The future retention rate is a forecast based on our best knowledge (and historic data), making the cumulative retention % an estimate. So by Year 5, we estimate that 20% of customers are remaining, which then is used to derive "likely customer profit". Given this approach, the 80% of ex-customers are removed and do not provide profits, that all comes from the remaining 20%. This construction enables us to play with scenarios of what happens if we increase retention rate, or annual profit - and which would deliver more CLV $'s. Thanks, Geoff

Great news - happy to help...

It is just for simplicity. In some industries the acquisition cost comes first and the income is received over time - such as in banking and insurance. However, in most industries the income and acquisition cost would both occur simultaneously in year 0, before any discount effects. Geoff.

Likely customer profit is a probability measure = what is the % likelihood that they will still be our customer in X years.

Normally we would rely upon our customer base information for likely retention rates. If it is purely a forecast, then we need to apply a benchmark based upon other similar businesses.

@@MarketingStudyGuide Thank you so much for the clarification, absolutely love your work on the channel. Since the video was old, I was not expecting a reply, very thankful!

Great question. Retention rate works best with the customer's ability to switch at any time - such as a fitness center membership paid monthly. But what I think that your question refers to is some form of fixed contract for two years? In that case, retention would be 100% for years 1 and 2 and then 0% for year 3 (no customers continually for the 3rd year). Please note that this is based on my assumptions from reading your question that it is a form of a fixed two-year deal. Hope this helps.

Yes, it shows that the business makes $31 overtime for every $4 spent on acquiring a new customer. Even allowing for some variations with assumptions, the numbers look very good.

@@MarketingStudyGuide But if my total revenue per customer for 5 years is $20+21$..$25 = $115 . My LTV = $31 . DOES IT MEAN I'M lagging $84 ? Plus sir , I'm facing difficulty in explain the negative value at cummulative LTV to my juniors ..

JHORGE D'O Churn rate is the 'loss of customers' %. It is the opposite of the customer loyalty or retention rate (e.g. if the retention rate of customers is 75%, then the churn rate is 25%). The full CLV formula is discussed here... www.clv-calculator.com/customer-lifetime-value-formulas/clv-formula/

Sounds like an assignment question. I'm sure if you follow your lecture notes that you can work it out.

Sure, depending on the business model, a monthly customer lifetime value is probably more appropriate - particularly if customers can come and go at any time and the cash flow is on a weekly or monthly basis. Given your business does not have a fixed contract situation, a customer lifetime value would probably be very helpful, as you would need some estimates of likely income and customer value - which you cannot get from a simple contract times number of customers approach. You should look at new customers versus existing customers - and then calculate at least two types of customer lifetime value. Ideally, you should also look at key segments, if there are significant differences between types of customers in your business.

@@MarketingStudyGuide Thank you very much Geoff for your detailed explanation :)

We need to consider the "bottom-line" profit impact of a customer. Firms and industries differ with their profit margin. Let's take two firms that both receive sales/revenues of $1,000 from a customer. But Firm A has a 60% margin (profit = $600), whereas Firm B only has a 10% margin ($100 profit). Therefore, while both customers contribute the same revenue, they differ significantly with profit. CLV is designed to measure the overall value (profit contribution over time) per customer to help aid in marketing decisions of acquisition costs and the ROI on improving CLV.

Businesses in growth markets, start-up firms, small/medium sized enterprises could achieve this overall. However, the customer lifetime value relates to an individual customer, who would typically start with a low-value purchase and increase their relationship and spend overtime.

Hi KingKan, The CLV calculation shown in the video is correct at $3,500. The acquisition cost of a customer is only incurred once, not each year as suggested by your formula. Therefore, the formula is CLV = average years X annual profit - acquisition cost = 4 X $1,000 - $500 = $3,500. Geoff

That is not a mistake. The acquisition cost per custom is just one a one time cost, it is not a cost each month.