Calculating Customer Lifetime Value with Excel

How much money should we spend on customer acquisition and retention? Who are the customers worth retaining? These questions can be answered by calculating a customer’s lifetime value. But what do you mean by customer acquisition? Well, for example, coupons. Selling products at a discounted rate is the most common tool for customer acquisition. If merchants do not believe in customer life time value, they will not sell their products at a discounted price, expecting the customers to return in the future. We’ve all heard of the company Groupon, its existence is the proof of how most people buy into the idea of customer life time value without noticing.

There is no one correct way to build a customer life time value model, or actually any models. Today I’ll build the model base off the method created by Gupta and Lehman to calculate the customer value multiplier. Their method utilizes discount and retention rates to forecast a customer’s long-term value denoted by multiplier. A multiplier of three means that the customer’s long term value is three times the profit he generates during the first period.

Now we start by making some basic assumptions. We assume that a fraction (1-retention rate) of the customers is lost each period. And we all know that the fraction of customers lost each period is the churn rate. We assume that the customer generates the same profit margin each year, which is pretty reasonable as we are a creature of habit. Lastly, we assume that the number of periods is limited to 360. If a period is a month, this covers 30 years. As churn and discount rate effects are compounded, the per period value of the earliest customer will become really small after this number of periods that it becomes negligible.

Step 1: Determine the Multiplier

So let’s get started! Note that this will be a model of constant margin. Since this is calculation of value over time, we would want to make sure to set up the periods correctly. If we assume profits are generated at the end, beginning or middle of a period, we will like the spreadsheet to adjust accordingly. List end, beginning and middle in any empty cells, in this case H3:H5. Then place our cursor at any empty cell, or in this case E1, select data validation. From the settings that popped up, select the List option and then select the cell range H3:H5 as the source.

Next, we go ahead to determine the number of customers generating profits during the period. It can be any number, this is just the set up of our model. Let’s go with 100. In cell B5 set up the initial customer =IF(E1="end",100*retention_rate,IF(E1="beginning",100,IF(E1="middle",0.5*(100+100*retention_rate)))) I have also set up a few formula shortcuts that we will use soon, not going to spend time explaining here.

And from B6:B360, we will use our retention formula =B5*retention_rate, which is 0.8 in this case. If we assume beginning of year profits, then in period two we can retain 80% of the customers and have 80 customers left. Period three, 64.

Continue on we need to calculate the discount factor for each year. Use the formula =(1/(1+discount_rate)^A5)*(1+discount_rate)^(VLOOKUP($E$1,lookdis,2,FALSE)) in C5:C360. If during year 1 the annual discount rate is 10% then the end of year discount factor is 1/1.10. Beginning of year discount factor is 1. Middle of year discount factor is 1/(1.10)^5. I think we all get the idea now.

Now, to compute the multiplier we sum up (# of customers left)*(discount factor) and divide by beginning number of customers. Use this formula =SUMPRODUCT(B5:B364,C5:C364)/100 in the cell E5. And now we have our multiplier.

Step 2: Sensitivity Analysis

When I was in still in school, our professor would pester us to develop the habit of conducting sensitivity analysis for most models built. This made more sense when I started working. Sensitivity analysis is so important because estimates of the model inputs may be wrong, so we need to at least have an idea how errors in our input estimates will affect the model’s output.

Now begin by listing values of the retention rate (0.6-0.9) in anywhere on the spreadsheet. In this case, F13:F16. Place in the upper-left corner the output formula, which is our multiplier, E5. Then we select the table range as F12:J16, use our What-If Analysis and select Data Table. Select the column input cell as B2 and row input cell as B1.

We have our sensitivity analysis done! As we can see from the table, a 10% discount rate (df) and 60% retention rate yields a multiplier of 2.2. However, a 10% discount rate and 90% retention rate yields a multiplier of 5.5! This demonstrates the importance of retaining these old customers.

The concept of customer value gained attention when Frederic Reichfeld pointed out in Loyalty Effect that for a credit card company, an increase in an annual retention rate from just 80% to 90% could double the multiplier. But obviously we can also assume that the per-period generated by a customer is independent from how long he or she has been a customer. Models can always be altered based on different assumptions and scenarios. There is no one way to build a model, and that is the interesting part!

The basic customer life time value we’ve just built is based off constant margin. In many cases, however, customer margins tend to increase with the length of his or her or other- gender-neutral-pronoun’s tenure. Next time, I’ll practice on how to build a CLV model with growing margin.