Customer Lifetime Value: How to Calculate CLV in a Spreadsheet

Customer lifetime value (CLV) is the total gross profit one customer brings you across your whole relationship with them. The fastest reliable formula is average order value × purchase frequency per year × customer lifespan in years × gross margin. Everything you need sits in a spreadsheet export of your last 12 months of orders — three columns and one pivot table, no data team required.

This guide runs the calculation twice, once for a repeat-purchase product business and once for a subscription, then covers the mistakes that make most published CLV numbers useless.

What CLV actually measures

CLV answers one commercial question: how much can you afford to spend to win a customer? If a customer is worth ₹4,320 in profit over their lifetime, spending ₹1,200 to acquire them is a good trade. Spending ₹5,000 is not.

That is the entire point. CLV is not a vanity metric or a dashboard tile — it is the ceiling on your customer acquisition cost (CAC), which is the average amount you spend on marketing and sales to win one new customer.

You will see CLV written as LTV (lifetime value) or CLTV. They mean the same thing. The only meaningful distinction is whether you are measuring revenue or profit, and profit is almost always the number you want.

The CLV formula, in plain English

Break it into four inputs you can each source in minutes:

• Average order value (AOV) — total net revenue ÷ total number of orders.
• Purchase frequency — total orders ÷ number of unique customers, over the same period.
• Customer lifespan — how many years a typical customer keeps buying. Calculate it as 1 ÷ your annual churn rate.
• Gross margin — the share of revenue left after the cost of the product or service itself.

Multiply all four. The first two together give you average customer value per year. Multiplying by lifespan turns one year into a lifetime. Multiplying by margin turns revenue into profit.

A note on churn: if 60% of last year’s customers bought again this year, your retention rate is 60% and your churn rate is 40%. Lifespan is 1 ÷ 0.40 = 2.5 years.

Worked example one: a D2C skincare brand

Take a direct-to-consumer (D2C) skincare brand in India with 12 months of clean order data.

Input	Value	Where it came from
Net revenue (12 months)	₹57,60,000	Order export, after returns and discounts
Total orders	3,200	Row count of the same export
Unique customers	2,000	Unique email or phone count
Annual churn rate	40%	Share who did not buy again the next year
Gross margin	60%	Price minus product, packaging and shipping cost

Now the arithmetic:

1. AOV = ₹57,60,000 ÷ 3,200 = ₹1,800
2. Purchase frequency = 3,200 ÷ 2,000 = 1.6 orders per customer per year
3. Average customer value = ₹1,800 × 1.6 = ₹2,880 per year
4. Lifespan = 1 ÷ 0.40 = 2.5 years
5. Lifetime revenue = ₹2,880 × 2.5 = ₹7,200
6. CLV (gross profit) = ₹7,200 × 0.60 = ₹4,320

If this brand acquires customers at ₹1,200, the CLV-to-CAC ratio is 3.6 to 1. That is healthy, and it suggests there is room to bid harder on acquisition.

Worked example two: a subscription business

Subscriptions are easier because purchase frequency is fixed by the billing cycle. You only need average revenue per account (ARPA), monthly churn and margin.

Say a US software tool charges $50 a month, loses 3% of accounts each month, and runs an 80% gross margin.

• Expected lifespan = 1 ÷ 0.03 = 33 months
• Lifetime revenue = $50 × 33 = $1,650
• CLV (gross profit) = $1,650 × 0.80 = $1,320

With a CAC of $400, the ratio is 3.3 to 1 and the CAC payback period is $400 ÷ ($50 × 0.80) = 10 months. Payback matters as much as the ratio: a 3-to-1 business that takes three years to recover its CAC will run out of cash long before the CLV arrives.

Three ways to calculate CLV, compared

Not every method suits every business. Pick by the data you actually have.

Method	What it tells you	Data needed	Effort	Best for
Historic CLV	What customers have been worth so far	Order history per customer	20 minutes	A sanity check on any predictive number
Predictive CLV (the formula above)	What a typical customer will be worth	AOV, frequency, churn, margin	30 minutes	Setting CAC ceilings and channel budgets
Cohort or probabilistic models	Segment-level value with a confidence range	Full transaction log, modelling tools	Days, usually a data team	Large catalogues and mature businesses

Start with historic CLV. It is simply total gross profit divided by number of customers, and it keeps your predictive model honest.

The spreadsheet method, step by step

Export 12 to 24 months of orders from Shopify, WooCommerce, Razorpay, your CRM or your billing system. Keep three columns: customer identifier, order date, net order value. Delete everything else.

1. Subtract returns, refunds and discounts so the revenue column is net, not gross.
2. Build a pivot table: rows = customer identifier, values = sum of order value and count of orders.
3. Read AOV and purchase frequency straight off the totals row.
4. For churn, filter to customers whose first order was in the earlier year, then check how many ordered again in the later year.
5. Get gross margin from finance, or calculate it yourself as (price − landed cost) ÷ price.
6. Multiply the four inputs in a single cell and label it clearly.

Then repeat the whole thing split by acquisition channel. That single extra step is where the decisions come from.

What a good CLV-to-CAC ratio looks like

CLV on its own means nothing. Divide it by CAC and you get a ratio marketers commonly read like this:

• Below 1 to 1 — you lose money on every customer. Fix pricing, margin or retention before scaling spend.
• 1 to 3 — workable but thin. Small increases in ad costs will hurt.
• Around 3 to 1 — the widely used rule of thumb for a healthy business.
• Above 5 to 1 — often a sign of underinvestment. You are probably leaving growth on the table.

Treat 3 to 1 as a convention, not a law. It came out of subscription software and travels imperfectly to categories with different margins and repeat rates.

Six mistakes that make CLV numbers wrong

1. Using revenue instead of gross margin. The single most common error, and it inflates CLV by two or three times in low-margin categories. Revenue you never keep cannot fund acquisition.

2. Confusing average order value with average customer value. AOV is per transaction. Customer value is per person per year. Skipping the frequency multiplier understates CLV badly for repeat-purchase brands.

3. Ignoring survivorship bias in lifespan. A brand that is 14 months old cannot observe a three-year lifespan. Young businesses should use churn-based lifespan and cap it conservatively rather than extrapolating from their happiest early cohort.

4. Reporting one blended CLV. Customers from paid social, organic search and referrals rarely behave alike. A blended average hides the channel that is quietly unprofitable. Segment by channel, and by first product bought.

5. Forgetting returns and cash-on-delivery losses. For Indian e-commerce this is not a rounding error. Cash on delivery (COD) orders that are refused at the door still cost you forward and reverse shipping. Use net delivered revenue, not order-placed revenue, or your CLV will be fiction.

6. Never applying a discount rate. Profit arriving in year four is worth less than profit arriving today. If your modelled lifespan exceeds roughly three years, discount future years by 10% annually. It will lower CLV, and that is the point.

What this means for you

• Do the 20-minute version this week. Three columns, one pivot table, four multiplications. An imperfect CLV beats no CLV.
• Use gross profit, not revenue. Write the margin assumption into the sheet so nobody quietly forgets it.
• Set a CAC ceiling from it. Divide CLV by three and hand that number to whoever buys your media. It converts a metric into a rule.
• Split by channel before you split by anything else. This is where budget reallocation decisions come from.
• Track CAC payback alongside CLV. Cash timing kills more businesses than weak ratios do.
• Recalculate quarterly. Margin, churn and ad costs all move. A CLV from last year is a historical artefact.
• Remember the lever. Retention raises lifespan, which raises CLV faster than almost anything you can do to AOV. A repeat-purchase email flow often beats another ad set.

Frequently asked questions

What is the simplest customer lifetime value formula?

Average order value × purchase frequency per year × customer lifespan in years × gross margin. For example: ₹1,800 × 1.6 orders × 2.5 years × 60% margin = ₹4,320 of lifetime gross profit per customer.

What is a good customer lifetime value?

There is no universal good number, because CLV depends entirely on your category and price point. What matters is the ratio of CLV to customer acquisition cost. A ratio of roughly 3 to 1 is the widely used benchmark for a healthy business; below 1 to 1 means you lose money on every customer you acquire.

What is the difference between CLV and LTV?

Nothing meaningful — CLV, CLTV and LTV all refer to customer lifetime value and are used interchangeably. The distinction worth policing is whether the figure is lifetime revenue or lifetime gross profit. Always ask which one a number represents before you make a budget decision with it.

How do I calculate CLV if my business is less than a year old?

Use the historic method. Divide total gross profit to date by the number of customers acquired, and treat it as a floor rather than a forecast. Once you have two comparable periods you can measure churn properly and switch to the predictive formula.

How often should I recalculate CLV?

Quarterly for most businesses, and immediately after any significant change to pricing, product mix, shipping costs or acquisition channels. CLV drifts as margins and churn move, so an annual refresh is usually too slow to catch problems.