How to calculate customer lifetime value in Excel

calculate customer lifetime value in Excel

We really, really want you to have more of this at the end of the year. Read on to learn more about how to calculate customer lifetime value.

We’ll show you how to calculate customer lifetime value in Excel (sometimes abbreviated as LTV or CLTV) in this article, which is essential for database marketing. Customer lifetime value defines how much money to invest to gain a new customer. When you calculate customer lifetime value, you know what a customer is worth to your business.

Our previous article (Part 1) briefly touched on lifetime value, as it served as an introduction to this article. This article will dig much deeper however, and give you a fuller understanding of lifetime value. LTV can really improve the effectiveness of your marketing budget. It helps determine how you should market to returning customers.

We show three ways to calculate customer lifetime value in this article, from easy to complex.

Customer lifetime value definition

calculate customer lifetime value

You want more of this at the end of the year, right? Use customer lifetime value and you will!

Lifetime value is a projection of the net profit of a customer in the future.

Many marketers have heard of lifetime value, but may not have ever calculated it. We call it customer lifetime value (CLTV or CLV) or lifetime customer value (LCV).

The concept of lifetime value started gaining popularity among database marketers in the 1980s and was defined in the 1988 book Database Marketing by Robert Shaw and Merlin Stone (go ahead and click, that’s not an affiliate link). It’s more important than ever today with mountains of big data available to online marketers.

Net present value (NPV) of future profits

There are several ways to calculate LTV. In its simplest form, lifetime value projects how much revenue a customer will generate in their lifetime. In its more complicated versions, it calculates the net present value (NPV) of future profits from new or existing customers over a period of three to five years.

The goal of determining lifetime value is to make sure your marketing program is profitable. It is used to improve sales, to maximize revenue per customer, and to focus your strategy to improve profitability.

Lifetime value is helpful for marketers because you know how much to invest to market to a specific customer.

It is well known that different customers are more valuable to a business than others because they spend more. Lifetime value takes these customers and projects how valuable they will be in the future. By determining how valuable they will be, you will know exactly how much effort and money you should put toward each customer.  Lifetime value should be a key tool in any marketing campaign.

Normally, customer lifetime value is broken up into yearly subsets, with each year used to calculate a new lifetime value for the following year. Some businesses, like a food or coffee chain, may use more frequent periods of time, like weeks or months. Since customers and marketing change over time, it is important to recalculate your customer lifetime value periodically.

Customer lifetime value formula in Excel (simple version):

Frequency x Time x Gross Margin Dollars = Lifetime Value

Lifetime value is calculated by…
1. Determining the frequency of purchases;
2. Then determining the duration of time you expect a customer to be loyal to your business, or, this could also just be the amount of time you would like to estimate for;
3. The final calculation uses your gross margin dollars (net sales revenue – cost of goods sold).
4. Multiply all of three of these numbers together to get simple lifetime value.

An alternative to this simple LTV formula is to use sales dollars instead of gross margin dollars.

A more robust customer lifetime value calculation for Excel

A more complex, but insightful, way to calculate lifetime value in Excel is to factor in NPV, customer retention rate and marketing costs. We’ll create an Excel “spreadsheet” below.

1. Start with your total revenue per year, the number of new customers and your total marketing costs. In the example below, this website has sales of $2.7 million and they spend about $10,000 per month on paid search which is $120,000 per year. This business had 10,000 new customers in Year 1, which means the acquisition cost for each new customer is $12.00 ($120,000 divided by 10,000 customers).

Year 1: Acquisition Year 2 Year 3
Total Revenue $2,700,000
Marketing Costs $120,000
New Customers 10,000

2). Add sales and customers for future years, a customer retention rate, orders per year and average order size to this spreadsheet. If you’re not sure about some of these numbers just put in your best guess. Below we have added the 10,000 new customers added in Year 1 with a retention rate of 25% which means in the second year, 25% of those customers of 2,500 will come back and buy. Since those are now repeat customers we will give them a higher retention rate of 70% in Year 2, which means 1,750 of them stick around to buy in Year 3.

Year 1: Acquisition Year 2 Year 3
Total Revenue $2,700,000 $1,562,500 $1,203,125
Marketing Costs $120,000 $0 $0
New Customers 10,000 2,500 1,750
Retention Rate 25% 70% 80%
Acquisition Cost $12 $0 $0
Orders per Year 1.2 2.5 2.5
Average Order Size $225 $250 $275

In Years 2 and 3, we don’t have a marketing acquisition cost (you can argue with your accountant if you want to insert a cost here or not…accountants love to debate this kind of stuff).

Calculate customer lifetime value in Excel: all the way in

3. Finally let’s add gross margin, a discount rate (a combination of interest rate and business risk), which lets us calculate Net Present Value, cumulative NPV profit over three years, and finally customer lifetime value on the bottom line.

Year 1: Acquisition Year 2 Year 3
Total Revenue $2,700,000 $1,562,500 $1,203,125
Marketing Costs $120,000 $5,000 $5,000
New Customers 10,000 2,500 1,750
Retention Rate 25% 70% 80%
Acquisition Cost $12 $0 $0
Orders per Year 1.2 2.5 2.5
Average Order Size $225 $250 $275
Gross Margin 30% 30% 30%
Cost of Goods $1,890,000 $1,093,750 $842,188
TOTAL COSTS $2,010,000 $1,098,750 $847,188
Gross Profit $690,000 $463,750 $355,938
Discount Rate 1.00 1.08 1.16
Net Present Value $690,000 $429,398 $306,843
Cumulative NPV Profit $690,000 $1,119,398 $1,426,241
Customer LTV $69.00 $111.94 $142.62

In this example, in Year 1 we acquired 10,000 new customers. Using a lifetime of three years, each of these customers are worth $142.62 to the bottom line.

The importance of lifetime value in growing sales and profits

There are a few extremely important things in this spreadsheet. First, you know the profitability of a customer during that customer’s lifetime. That means you know how much you can spend to acquire a customer and still make money. Second, customers who make repeat purchases are worth significantly more to your business. You should invest in getting customers to make a second purchase, third purchase, and so on. After all, you already invested the money to acquire this customer.

Reach the right customers using LTV

One rule that is very important to understand, and applies to most businesses is the 80/20 rule. This rule states that 80% of your business comes from 20% of your customers. This stat is crucial to understand. If 80% of your business is coming from only 20% of your customers you need to make sure that you are focusing on the right customers. These customers tend to be ones that come back for repeat purchases and have a long lasting relationship with your business. Once you determine the most lucrative customers, contact them as much as your budget allows increasing profitability of your customers. Finding out the lifetime value of this 20% will be most beneficial to your business so you can plan the amount of money you would like to spend to market toward them.

Customer lifetime value conclusion

Hopefully now you understand lifetime value, so start an Excel spreadsheet and calculate it for your customers. Figure out who is going to be the most valuable customer in the future to optimize your marketing campaigns. Remember that the top percentiles of your sales is what drives your business, so focusing your efforts on them will help drive you business. Understanding lifetime value can be the key to all of your future marketing campaigns.

For more on customer retention, see our previous article.

1 reply

Comments are closed.