Customer Lifetime Value: How to Calculate CLV in a Spreadsheet

Customer lifetime value (CLV) is the total 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 × margin. Everything you need sits in a spreadsheet export of your last 12 to 24 months of orders — three columns and one pivot table, no data team required.

This guide runs the calculation twice with every number shown — once for a repeat-purchase product business in India and once for a subscription — then shows the cost lines that quietly destroy most published CLV figures, and how to split the number by channel so it actually changes a budget.

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 ₹3,550 in profit over their lifetime, spending ₹1,200 to acquire them is a defensible trade. Spending ₹3,000 is not.

That is the entire point. CLV is not 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 distinction that matters is whether the figure is 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) — net revenue ÷ number of orders.
• Purchase frequency — total orders ÷ unique customers, over the same period.
• Customer lifespan — how many years a typical customer keeps buying, estimated as 1 ÷ annual churn rate.
• Margin — the share of revenue you keep after the costs of serving that order.

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

On churn: if 60% of last year’s customers bought again this year, retention is 60%, churn is 40%, and lifespan is 1 ÷ 0.40 = 2.5 years. Use the same definition of “customer” in both periods or the number drifts.

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 delivered revenue (12 months)	₹57,60,000	Order export, after returns, discounts and undelivered orders
Delivered 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 following year
Gross margin	60%	Price minus landed product cost

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 on gross margin = ₹7,200 × 0.60 = ₹4,320

Most articles stop here. That is where the number starts lying to you.

Gross margin is not the margin you can spend

Gross margin counts only the product. Fulfilling an order in India costs real money on top of that, and none of it is available to fund ads. What you want is contribution margin: revenue minus every variable cost of serving the order.

Here is the same ₹1,800 order built up properly. The ranges are typical bands for Indian D2C in late 2026 and should be treated as estimates — replace each one with your own invoices.

Cost line	Typical range	Used here
Landed product cost	30–50% of AOV	₹720
Primary and shipper packaging	₹30–₹60 per order	₹40
Forward shipping (prepaid and COD blend)	₹70–₹110 per order	₹80
Payment gateway (roughly 2% plus 18% GST)	about 2.3% of order value	₹42
Returns and reverse logistics allowance	₹20–₹60 per order	₹30
Contribution per order	—	₹888 (49%)

Rerun the last step with 49% instead of 60%: CLV = ₹7,200 × 0.49 = ₹3,550.

That single correction moves the brand’s verdict. At a CAC of ₹1,200, the gross-margin version reads 3.6 to 1 — “bid harder.” The contribution version reads 3.0 to 1 — “you are exactly at the line.” Same business, two different decisions.

Cash on delivery (COD) deserves its own warning. Orders refused at the door still cost you forward and reverse shipping and packaging, and they earn nothing. Strip them out of revenue and out of the order count before you divide, or your AOV and your frequency are both wrong.

Worked example two: a subscription business

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

Say a software tool charges $50 a month (or roughly ₹4,000 a seat for an India-priced equivalent), loses 3% of accounts each month, and runs an 80% gross margin after hosting and support.

• Implied lifespan = 1 ÷ 0.03 = 33 months
• Lifetime revenue = $50 × 33 = $1,650
• CLV = $1,650 × 0.80 = $1,320

Two adjustments make this honest. First, cap the lifespan. A 1 ÷ churn calculation on 2% monthly churn implies a 50-month customer, which is longer than most products have existed. Capping at 36 months here gives $50 × 36 × 0.80 = $1,440.

Second, discount it. Profit arriving in year three is worth less than profit today. Discounting that $1,440 at 10% a year lands near $1,200. For Indian businesses, a 12% rate is a fairer reflection of the cost of capital. Both are conventions, not measurements — label them in the sheet.

Then check payback. At a CAC of $400, payback = $400 ÷ ($50 × 0.80) = 10 months. A business at 3 to 1 that takes three years to recover its CAC runs out of cash long before the CLV arrives.

Split it by channel, or skip it entirely

A blended CLV is the average of customers who behave nothing alike. Here is the skincare brand’s 2,000 customers broken out — illustrative figures in the same shape as the real export, all using the 49% contribution margin.

Channel	Customers	AOV	Orders/yr	Year-2 repeat	CLV	CAC	Ratio
Paid social	1,100	₹1,650	1.35	48%	₹2,110	₹1,450	1.5 : 1
Paid search	450	₹1,900	1.8	62%	₹4,430	₹980	4.5 : 1
Organic and referral	450	₹1,950	2.0	68%	₹6,020	₹240	25 : 1

The blended ₹3,550 looked fine. The split says more than half the customer base is acquired at a ratio that barely clears break-even once overheads are counted, and the profitable channels are starved. That is a budget meeting, not a metric. If paid social is carrying your volume, the trade-offs in Meta Ads vs Google Ads for Indian D2C brands are the next thing to read, and the 70-20-10 budget rule gives you a frame for reallocating.

One caution: CLV by channel is only as good as your attribution. Two tools using different lookback periods will assign the same customer to different channels, so fix your definitions first — attribution windows explained covers why a 1-day and a 7-day window produce different winners.

Four ways to calculate CLV, compared

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 curves	How value accumulates month by month after acquisition	Orders tagged with first-order month	Half a day	Payback decisions and spotting churn shifts early
Probabilistic models (BG/NBD and similar)	Per-customer value with a confidence range	Full transaction log, modelling tools	Days, usually a data team	Large catalogues and mature businesses

Start with historic CLV — total contribution profit divided by number of customers. It keeps the predictive model honest. Cohort curves are the best value for effort: they show you whether month-6 value is rising or falling, which a single blended number never will.

The spreadsheet method, step by step

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

1. Remove cancelled and undelivered orders, then subtract returns, refunds and discounts so revenue is net.
2. De-duplicate customers. The same person with two email addresses becomes two customers and halves your measured frequency.
3. Build a pivot table: rows = customer identifier, values = sum of order value and count of orders.
4. Read AOV and purchase frequency off the totals row.
5. For churn, filter to customers whose first order fell in the earlier 12 months, then count how many ordered again in the later 12.
6. Build the per-order cost stack from the table above and compute contribution margin.
7. Multiply the four inputs in one labelled cell, with every assumption in a visible cell next to it.
8. Repeat with channel as a pivot filter.

Identity resolution is where most sheets quietly break. If you are stitching web, app and offline orders together, a first-party data strategy for small teams is the groundwork that makes CLV trustworthy rather than approximate.

What if customers only buy once?

Some categories — mattresses, wedding services, large appliances — have genuine one-purchase behaviour. Do not force a frequency multiplier onto them. Instead, CLV = contribution on the first order + expected value of referrals + expected value of accessories, consumables or service contracts. If 15% of customers refer one buyer, that is 0.15 × the next customer’s contribution, added on. Be conservative; referral assumptions are the easiest place to fool yourself.

Eight mistakes that make CLV numbers wrong

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

2. Stopping at gross margin. Shipping, packaging, gateway fees and returns come out before a rupee is available for ads. As shown above, the gap between gross and contribution margin was 11 percentage points and ₹770 of CLV.

3. Confusing AOV with average customer value. AOV is per transaction; customer value is per person per year. Skipping the frequency multiplier badly understates repeat-purchase brands.

4. Treating 1 ÷ churn as a measurement. Churn is front-loaded — the heaviest losses happen in the first 90 days, and survivors churn more slowly. A single blended rate therefore understates the value of the customers who stay and overstates the average. Cohort curves are the fix.

5. Extrapolating from a young business. A 14-month-old brand cannot observe a three-year lifespan. Use churn-based lifespan, cap it, and say out loud that it is a floor.

6. Reporting one blended CLV. It hides the channel that is quietly unprofitable. Segment by channel first, then by first product bought.

7. No discount rate. If your modelled lifespan exceeds roughly three years, discount future years at 10–12% annually. It lowers CLV, and that is the point.

8. Letting CLV authorise spend you cannot fund. CLV is a profit forecast, not a bank balance. A healthy ratio with a 14-month payback still needs working capital for 14 months.

The cheapest way to raise CLV

Look at the formula again. AOV and frequency move in small increments; lifespan is a multiplier. Lifting year-two repeat from 48% to 58% changes lifespan from 1.92 to 2.38 years — a 24% CLV increase with no change to price, product or ad spend.

That is why a working repeat-purchase email and WhatsApp flow usually beats another ad set. If you are choosing the tool that sends those flows, CleverTap vs MoEngage vs WebEngage compares the options Indian teams actually shortlist.

What this means for you

• Do the 20-minute version this week. Four columns, one pivot table, four multiplications. An imperfect CLV beats no CLV.
• Use contribution margin, not gross margin. Build the per-order cost stack once and keep it visible in the sheet.
• Convert it into a CAC ceiling. Divide CLV by three and hand that number to whoever buys your media. A metric becomes a rule.
• Split by channel before anything else. That is where reallocation decisions come from.
• Track CAC payback alongside the ratio. Cash timing kills more businesses than thin ratios do.
• Cap lifespan and discount long tails. If your model implies a four-year customer, prove it with a cohort before you spend against it.
• Recalculate quarterly, and immediately after any change to pricing, shipping rates or product mix.
• Spend the next hour on retention, not on the spreadsheet. Lifespan is the biggest lever in the formula.

Frequently asked questions

What is the simplest customer lifetime value formula?

Average order value × purchase frequency per year × customer lifespan in years × margin. A worked example: ₹1,800 average order value × 1.6 orders a year × 2.5 years × 49% contribution margin = ₹3,550 of lifetime profit per customer. Customer lifespan is estimated as 1 ÷ your annual churn rate, so 40% churn implies 2.5 years. Use contribution margin — revenue minus product, packaging, shipping, payment fees and returns — rather than gross margin, because only contribution is actually available to fund marketing.

Should CLV use gross margin or contribution margin?

Contribution margin. Gross margin subtracts only the cost of the product, so it ignores packaging, forward and reverse shipping, payment gateway fees and return losses — all of which are real cash leaving the business on every order. For Indian direct-to-consumer brands the gap is commonly 8 to 15 percentage points of revenue, which can cut CLV by a fifth or more. Since CLV exists to set a ceiling on customer acquisition cost, using a margin figure you cannot actually spend produces a ceiling that is too high.

What is a good CLV to CAC ratio?

Roughly 3 to 1 is the widely used benchmark for a healthy business. Below 1 to 1 you lose money on every customer acquired. Between 1 and 3 is workable but thin — small rises in ad costs will hurt. Above 5 to 1 often signals underinvestment in growth rather than excellence. Treat 3 to 1 as a convention that originated in subscription software; it travels imperfectly to categories with different margins and repeat rates. Always read it alongside CAC payback period, which tells you when the cash actually returns.

How do I calculate CLV for a subscription business?

Divide average revenue per account by your monthly churn rate to get lifetime revenue, then multiply by margin. At $50 per month, 3% monthly churn and 80% margin: 1 ÷ 0.03 = 33 months, × $50 = $1,650 lifetime revenue, × 0.80 = $1,320 CLV. Two corrections matter. Cap the implied lifespan at 36 to 60 months, because low churn rates imply customer lifetimes longer than most products have existed. Then discount future years at about 10% annually, since profit arriving in year three is worth less than profit today.

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

Use the historic method: divide total contribution profit earned to date by the number of customers acquired, and treat the result as a floor rather than a forecast. You cannot measure annual churn without two comparable periods, so any lifespan you assume is a guess. Instead, track a cohort curve — cumulative profit per customer at months 1, 3 and 6 after their first purchase. Once you have two full 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 material change to pricing, product mix, shipping rates, payment fees or acquisition channels. CLV drifts constantly because margin, churn and ad costs all move, so an annual refresh is usually too slow to catch a channel turning unprofitable. Recalculate the channel-level splits at the same cadence as the blended figure — the blended number can stay flat while one channel deteriorates sharply and another improves, which is exactly the situation you need to see.