top of page

Forecasting Customer Lifetime Value (CLV) in Excel: A Step-by-Step Guide for Small Businesses

Updated: Jun 12

ree

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?

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


bottom of page