Free CLV Calculator: Calculate Customer Lifetime Value

Customer lifetime value calculator, also referred to as CLV calculator or lifetime value (LTV), is a metric that businesses use to estimate the total revenue a customer will generate throughout their relationship with the company.

It considers not just the value of a single purchase, but the entire stream of revenue expected from that customer over time.

Customer Lifetime Value (CLTV) Formula

CLV = Customer Value * Average Customer Lifespan

• Customer Value = Average purchase value x Average number of purchases per customer

This formula multiplies the average value a customer brings in by their average lifespan as a customer.

Looking to create reporting system fast & painlessly?
Build dashboards and track KPIs in one place with Ajelix BI

Start free

How To Calculate CLTV? (With Example From Excel)

Step-by-step guide on how to calculate CLV using the simple formula, along with an example:

1. Gather Customer Data

You’ll need some data about your customers to calculate CLV. Here’s what you’ll typically need:

Screenshot from Excel with example data, image by author

• Customer ID: A unique identifier for each customer.
• Purchase Value: The amount a customer spends on each purchase.
• Number of Purchases: The total number of purchases made by each customer within a specific timeframe (e.g., a year).

2. Calculate Customer Value

Average Purchase Value: Add up the total purchase value for all customers and divide it by the total number of purchases. This will give you the average amount a customer spends per purchase.

Screenshot from Excel on how to calculate average purchase value, image by author

Let’s say you have the following data for 3 customers:

Total purchase value = $50 + $100 + $75 = $225 Total number of purchases = 2 + 1 + 3 = 6

Average purchase value = $225 / 6 = $37.50

Average Number of Purchases per Customer: Divide the total number of purchases by the total number of customers. This will tell you how many times a customer makes a purchase on average within the chosen timeframe.

Screenshot in Excel on how to calculate the average number of purchases per customer, image by author

Example continuing from the above data:

Average number of purchases per customer = 6 / 3 = 2

Customer Value: Multiply the average purchase value by the average number of purchases per customer. This represents the average revenue generated by a single customer.

Example: Customer value = $37.50 * 2 = $75.00

Screenshot from Excel on how to calculate customer value, image by author

3. Estimate Average Customer Lifespan

This refers to the average length of time a customer remains engaged with your business. You can estimate this based on historical data or industry benchmarks.

Example: Let’s assume the average customer lifespan for your business is 2 years.

4. Calculate CLTV

Multiply the customer value by the average customer lifespan. This will give you the Customer Lifetime Value.

Example: CLTV = $75.00 * 2 years = $150.00

Screenshot from Excel on how to calculate CLTV, image by author

In this example, the CLV is $150.00, indicating that the average customer brings in $150 in revenue over their 2-year relationship with the company.

You can also watch a video walkthrough 👇

Download free CLV Excel Template

Click the download button below and get your Excel template with calculations.

How To Generate Formulas With AI?

1. Create a free Ajelix account

Ajelix provides over 15 AI-powered tools to simplify tasks in Excel and Google Sheets, including generating formulas. Sign-up is easy – you can use your Gmail account or any other email address.

Screenshot from Ajelix registration page, image by author

2. Write a clear and concise prompt

To get the most accurate formula, describe your desired outcome clearly. For instance, to calculate Customer Lifetime Value (CLV) for 2 months with customer value in cell B4, you could ask: “Give me the formula for CLV for 2 months if customer value is in cell B4.”

Screenshot from Ajelix formula generator with CLV prompt, image by author

Crafting Effective Prompts:

• Focus on results: Instead of “need a formula for calculations,” state the desired outcome. Example: “Calculate average sales in column B for approved items (marked ‘Yes’ in column A).”
• Use cell references (optional): If your platform allows, specify cell ranges or names. Example: “Calculate total cost (column C) by multiplying quantity (A) by price (B).”
• Clarify conditions: If there are conditional elements, explain them precisely. Example: “Return ‘Bonus’ in D2 if sales (B2) exceed target (E1), otherwise return ‘No Bonus’.”

3. Get your formula

After you submit the prompt AI will give you a ready-to-use formula you can insert in your spreadsheet. You can also use Excel or Google Sheets add-on for easier formula writing. In the screenshot below you can see that AI gave the formula.

Screenshot from Ajelix with CLV formula result, image by author

Why does CLTV matter?

Infographic with 4 main points about why CLTV metric is important for business, image by author

CLTV, or Customer Lifetime Value, is a big deal for businesses because it goes beyond just making a single sale. Here are 4 things why this metric matters:

• Focuses on Long-Term Value: Instead of just chasing one-time sales, CLTV helps you understand the total revenue a customer might bring over their entire relationship with your business. This lets you prioritize strategies to keep those valuable customers happy and returning for more.
• Smarter Resource Allocation: Knowing your CLTV, you can allocate resources more effectively. For example, you might be willing to spend more on acquiring a customer with a high predicted CLTV, even if the initial acquisition cost is higher.
• Improves Customer Retention: Understanding CLTV helps you identify what drives customer loyalty. You can then use this knowledge to develop targeted programs and incentives to keep your best customers engaged and prevent churn (customers leaving).
• Profitability Benchmark: CLTV lets you compare the cost of acquiring a customer (Customer Acquisition Cost) to the revenue they generate over time. This helps assess the overall profitability of your marketing and sales efforts.

FAQ