How to Calculate Customer Lifetime Value (CLV) in Excel

Customer lifetime value (CLV) is the profit a business can expect from one customer over the whole time they buy from it. In Excel you can calculate customer lifetime value from a plain orders table: SUMIFS and COUNTIFS give each customer's revenue and orders, and a short formula turns them into CLV. This guide is for marketing, e-commerce and analyst roles, and builds everything on a small example checked in Python. The figures are examples, not benchmarks or advice for any business.

What customer lifetime value means#

CLV answers "what is a customer worth to us?". Teams compare it with the cost of winning a customer, decide how much to spend on marketing channels, and spot their most valuable segments. You'll meet two kinds:

• Historical CLV: what each customer has already spent. It needs only addition.
• Predictive CLV: what a typical customer will be worth over their whole relationship. It needs assumptions, such as how many years customers stay.

The simple predictive formula multiplies four parts:

CLV = average order value × orders per year × years as a customer × gross margin

Gross margin is the share of revenue left after the cost of the goods sold. Using it turns revenue into profit.

The example orders table#

The examples use seven orders from one year in A1:D8:

Each customer came through one channel. A real table would have thousands of rows, but the formulas are the same.

Step 1: Revenue and orders per customer#

List the customer IDs in F2:F4, then total and count each one's orders. In G2, revenue:

=SUMIFS($D$2:$D$8,$B$2:$B$8,F2)

In H2, number of orders:

=COUNTIFS($B$2:$B$8,F2)

Fill both down (Windows: Ctrl+D; Mac: ⌘D):

This is historical CLV in revenue terms: C-01 has spent 325. The $ signs lock the ranges, so only F2 changes as you fill down. The SUMIFS and COUNTIFS function guides explain the argument order. In Excel 2021 or Microsoft 365, =UNIQUE(B2:B8) in F2 lists the customer IDs for you.

Step 2: Calculate average order value and frequency#

Now work out the inputs to the CLV formula, with each one in its own cell:

In your sheet, divide the cells rather than typing the numbers: if total revenue is in K2 and orders in K3, average order value is =K2/K3. Without a separate customer list, =SUMPRODUCT(1/COUNTIF(B2:B8,B2:B8)) counts the distinct customers in any version of Excel. The article on how SUMPRODUCT works explains why that trick counts each ID once.

Step 3: Turn the inputs into CLV#

Add two assumptions: how many years a typical customer stays (3 in this example) and your gross margin (40%). Then multiply:

=K4*K5*K6*K7

where K4 is average order value, K5 orders per customer per year, K6 years as a customer and K7 gross margin. The result is 298.00: 248.33 of revenue per customer per year × 3 years × 40%. Excel uses the unrounded values, so the answer doesn't drift. In plain words, a typical customer in this example is worth about 298 of gross profit over three years.

Lifespan is the hardest input. If about a third of customers stop buying each year, a common estimate is 1 ÷ 0.33, roughly 3 years. Change the assumption cells and see how much CLV moves; that shows you which inputs matter most.

Compare CLV by channel with AVERAGEIFS#

AVERAGEIFS averages only the rows that meet your conditions, so it shows which channels bring the most valuable customers. Average order value by channel:

=AVERAGEIFS($D$2:$D$8,$C$2:$C$8,"Search")

For revenue per customer by channel, add a Channel column (I) to your per-customer table and use =AVERAGEIFS($G$2:$G$4,$I$2:$I$4,"Email"). Here Search customers order most often, while Email customers place the biggest orders. With only one customer per channel this proves nothing, but on real data it is how teams compare channels. The AVERAGEIFS function guide and AVERAGEIFS with multiple criteria cover more conditions, and averaging by group shows a pivot table alternative.

CLV for subscription businesses#

For a subscription, customers pay a similar amount every month, and the key unknown is how long they stay. Churn rate is the share of customers who cancel each period. A widely used formula is:

CLV = average monthly revenue per customer × gross margin ÷ monthly churn rate

With example figures of 30 a month, a 70% margin and 4% monthly churn, the formula =30*70%/4% returns 525. Dividing by churn is the same as multiplying by the average lifetime: 1 ÷ 4% = 25 months.

Common CLV mistakes#

These slips make CLV look bigger or smaller than it is:

1. Using revenue instead of profit. Multiply by gross margin before comparing CLV with acquisition costs.
2. Mixing time periods. Orders per year needs lifespan in years; monthly churn needs monthly revenue.
3. Counting orders instead of customers. Divide by the number of distinct customers, not the number of rows.
4. Short data windows. Customers who joined last month haven't had time to buy again, which pulls frequency down.
5. Treating one average as the truth. Segment by channel or cohort, because a few big customers can skew the average.

CLV is a common topic in Excel skills for e-commerce and Excel skills for marketing jobs, along with conversion rates and campaign totals.

Practise it#

Try the exercise on this page: one SUMIFS gives a customer's historical value. Then practise COUNTIFS and SUMIFS in a live spreadsheet to build the per-customer table, and practise AVERAGEIF, MAXIFS and MINIFS to compare segments. Each lesson checks your formula instantly.