Customer Lifetime Value Formula: How to Calculate CLV
Summary
Customer lifetime value (CLV) = Average order value × Purchases per year × Gross margin × Customer lifespan in years. For subscriptions, use ARPA × Gross margin ÷ Churn rate. A shop with $64 orders, 2.6 orders a year, a 58% margin and a three-year lifespan has a CLV of $289.54 in gross profit.
On this page
Customer lifetime value (CLV / LTV) formula
CLV = Average order value × Purchase frequency per year × Gross margin % × Average customer lifespan (years)- Average order value: Revenue ÷ number of orders over the measurement period.
- Purchase frequency: Orders per customer per year: orders ÷ unique customers in a 12-month window.
- Gross margin: Share of revenue left after cost of goods, as a decimal. Using margin makes CLV comparable with acquisition cost.
- Customer lifespan: Average number of years a customer keeps buying, usually estimated from cohort data or as 1 ÷ annual churn.
How to calculate customer lifetime value (CLV / LTV) in Excel and Google Sheets
Excel, using the cells in the worked example below:
=B2*B3*B4*B5Worked example
An online specialty tea shop wants a CLV estimate to decide how much it can pay to acquire a first-time buyer through paid social.
| Input | Value |
|---|---|
| Average order value | $64.00 |
| Orders per customer per year | 2.6 |
| Gross margin | 58% |
| Average customer lifespan (years) | 3.0 |
- Annual revenue per customer = 64.00 × 2.6 = $166.40.
- Annual gross profit per customer = 166.40 × 0.58 = $96.51.
- CLV = 96.51 × 3.0 = $289.54 of gross profit over a customer's lifetime.
- On revenue alone the figure would be $499.20, which overstates what you can afford to spend on acquisition.
Result: $289.54 customer lifetime value (gross profit basis)
How to interpret customer lifetime value (CLV / LTV)
CLV is most useful as a ceiling on acquisition cost and as a way to compare segments, for example customers acquired through search versus paid social, or those whose first order included a subscription. Treat any single CLV number as an estimate: lifespan is the hardest input, and young businesses often don't yet have enough history to observe it. Prefer cohort-based CLV (actual cumulative gross profit per customer by months since first order) once you have 12–24 months of data, and refresh it quarterly.
Common mistakes
- Using revenue instead of gross margin. Revenue CLV makes every acquisition channel look affordable.
- Assuming lifespan instead of measuring it. Estimate from cohort retention curves, or as 1 ÷ annual churn, and say which.
- Mixing one-time and repeat buyers in an average that hides two very different populations. Segment first.
- Not discounting long lifespans. For lifespans beyond a few years, future gross profit is worth less today; apply a discount rate or cap the horizon.
Calculate customer lifetime value (CLV / LTV) with Better Analyst
Connect your data or upload a spreadsheet, then ask in plain English. Better Analyst writes and runs the calculation and shows the steps, so you can check the number.
- “Calculate 12-month and 24-month CLV by acquisition channel from our orders table.”
- “Build a cohort chart of cumulative gross profit per customer by first-order month.”
- “Which first-purchase products lead to the highest customer lifetime value?”
Usually calculated from: Google BigQuery, MySQL, PostgreSQL, Snowflake, Google Sheets, Google Analytics.