Forecasting Customer Lifetime Value (CLV) in Excel: A Step-by-Step Guide for Small Businesses
- AnalytiCore Writer
- Feb 22
- 1 min read
Updated: Jun 12

Forecast Customer Lifetime Value (CLV) with Excel for Smarter Growth
Customer Lifetime Value (CLV) is one of the most powerful metrics a small business can track—but few do. It tells you how much a customer is worth over the entire time they do business with you, not just on the first sale.
This post walks you through a simple Excel-based approach to calculate and analyze CLV, so you can make better decisions about marketing spend, customer service investments, and pricing strategies.
Why CLV Matters for Small Businesses
CLV helps you understand:
How much you can afford to spend on customer acquisition (CAC)
Which customer segments are most valuable
How retention affects long-term revenue
If you know your average CLV is $1,200 and CAC is $300, you’re in a strong position. If those numbers flip, it's time to rework your funnel.
Excel Setup for CLV Calculation
Step 1: Gather These Metrics
Average Purchase Value (APV) = Total Revenue ÷ Number of Purchases
Purchase Frequency (PF) = Number of Purchases ÷ Number of Customers
Customer Lifespan (in years) = 1 ÷ Churn Rate
Step 2: Calculate CLV
Use this formula in Excel:
= APV * PF * Customer Lifespan
Or broken out:
= (Total Revenue/Total Purchases) * (Total Purchases/Total Customers) * (1/Churn Rate)
Step 3: Create Segments
Segment customers by:
Acquisition channel
Product type
Region
Compare CLVs between segments to prioritize growth areas.
Step 4: Visualize
Use Excel charts to:
Show CLV by segment
Compare CAC vs. CLV
Visualize retention impact
Download the Excel Template
Comments