How to Calculate Customer Lifetime Value CLV in Excel

Customer Lifetime Value (CLV) represents the total gross profit generated by a customer over the entire period of their relationship with a company. The customer churn rate (Churn Rate) is one of the key metrics used to calculate CLV.



Step-by-Step Calculation of Customer Lifetime Value (CLV) in Excel

Below is an illustration of the CLV calculation using the churn rate. First, we calculate the average gross profit per customer, which includes both new and existing customers.

Source Data Table:

ItemsIndicators
Number of Customers at the Beginning of the Month:4,215
New Customers:614
Number of Customers at the End of the Month:4,441
Number of Lost Customers:388
Customer Churn Rate:9.21%
Monthly Revenue: $564,810.00
Cost of Goods Sold: $225,924.00
Gross Profit: $338,886.00
Average Revenue per Customer: $78.30
Customer Lifetime Value: $850.61

To calculate the customer lifetime value, follow these step-by-step instructions:

  1. Calculate gross profit (B11) – subtract the cost of goods sold (COGS) from the revenue.
    2. gross profit B11.
  2. Calculate the average gross profit per customer (B13) – divide the value in cell B11 (gross profit) by the average number of customers for the current month. This is the arithmetic mean of the numbers in cells B2 and B4. Since the gross profit is for the last full month, it must be divided by the average number of customers, not just the starting or ending customer count.
    3. per customer B13.
  3. Calculate the CLV in cell B14 by dividing the average gross profit per customer by the customer churn rate.
    4. CLV B14.

Download CLV calculation formula example in Excel download file

In this example, each customer generates an estimated profit of 850.61 over the course of their relationship with the company.