How to calculate customer lifetime value in Excel

The classic formula: order value x frequency x lifespan

The textbook version multiplies three things: average order value (AOV), how many orders a customer places per year, and how many years they stay. With 14 orders worth $940 from 6 customers over 12 months, AOV is $67.14 and each customer orders 2.33 times a year.

AOV x frequency simplifies to revenue / customers, so you don't need both steps: $940 / 6 = $156.67 per customer per year. The 12/L4 part scales your data to a year, so it still works if your export covers 6 or 18 months.

The lifespan in L3 is an estimate, and it's the number that moves CLV the most. Put it in an input cell, write down where it came from (how long your repeat customers have actually stayed, or 1 / annual churn), and never bury it inside the formula.

Use margin, not revenue

A customer who spends $470 doesn't leave you $470. Multiply by your gross margin (revenue minus the direct cost of what you sold, as a share of revenue) to get the value you can actually spend on winning customers. Put your own margin in L2; 55% here is only an example.

This margin-based number is the one to compare with acquisition cost. Comparing revenue CLV with CAC makes every channel look better than it is.

Check the median: one big customer skews the mean

Build a small table with one row per customer: =SUMIFS($D$2:$D$15,$B$2:$B$15,F2) for revenue and =COUNTIFS($B$2:$B$15,F2) for orders, copied down. Now look at it. Dev placed one $420 corporate gift order, more than Ava's four orders together ($146).

The average revenue per customer is $156.67, but the median is $106.50. Take Dev out and the average drops to $104. So the "typical" customer is worth much less than the average says. Report the CLV on the median next to the mean, and plan budgets on the lower one until you have more customers.

In Excel 365 you can skip the helper table: =MEDIAN(SUMIFS(D2:D15,B2:B15,UNIQUE(B2:B15))) totals each customer and takes the middle in one cell.

CLV by acquisition channel with AVERAGEIFS

Column G records where each customer came from. AVERAGEIFS averages the revenue column for one channel: Instagram customers average $122.50, search customers $87.50 and referrals $260.

Before you pour money into referrals, look at who's in that group. Two customers, and one of them is Dev. =AVERAGEIFS(H2:H7,G2:G7,"Referral",F2:F7,"<>Dev") gives $100. With this little data, channel CLV is a hint, not a verdict. Add a count next to each channel (=COUNTIFS(G2:G7,"Referral")) so nobody reads too much into two rows.

Also watch the start dates. Fay first ordered in June and Eli in April, so they've had less time to buy than Ava. With more data, compare customers who started in the same quarter (a cohort) instead of everyone at once.

Subscription shortcut: ARPU x margin / churn

For a subscription, CLV = monthly revenue per customer (ARPU) x margin / monthly churn rate. The churn part works as the lifespan: if 4% of customers cancel each month, the average customer stays 1 / 4% = 25 months.

With a $29 plan, a 55% margin and 4% monthly churn, that's $398.75. Without the margin it's $725, which is revenue, not value. This shortcut assumes churn stays steady, so recheck it when churn changes. The churn guide shows how to measure the rate from your customer list.

The CLV:CAC ratio

Divide CLV by what it costs to win a customer (CAC). With a margin CLV of $258.50 and a $60 acquisition cost, each customer returns about 4.3 times what you spent getting them.

People often quote 3:1 as a healthy target, but it's a rule of thumb, not a law. A ratio under 1 means you lose money on every new customer. A very high ratio can mean you're spending too little on growth. Both numbers go in cells you can change, so you can test "what if CAC rises to $90?" in seconds.