LTV in a Spreadsheet: The 5-Input Model I Used for 6 Years (With the ChatGPT Prompt That Builds Yours in 90 Seconds)

A DTC (Direct-to-Consumer, 直接面向消费者) brand I worked with in 2021 was spending $48,000 a month on Meta ads. The CEO was proud of the ROAS (Return On Ad Spend, 广告支出回报率) dashboard — 2.4x, she said. She was confused when I told her the business was bleeding cash. The reason: she had no LTV (Life Time Value, 用户终身价值) model. She was treating "first-purchase revenue × 2.4" as profit, when in reality those customers churned at 55% annually and the second purchase almost never came. We rebuilt the bid strategy around a 5-input spreadsheet model. Six months later, monthly ad spend was $72,000 and contribution margin (毛利贡献) was positive for the first time.

That model is the one I'm about to walk through. I have used it for DTC apparel, coffee subscriptions, B2B SaaS (Software as a Service, 订阅制软件), and a meal-kit company. It is the only LTV framework I trust because it has exactly 5 inputs — every one of which you can pull from a Shopify export or a Stripe dashboard — and the output answers the three questions a marketer actually needs answered:

1. Can I profitably acquire this customer? (LTV:CAC ratio)
2. How long until I make my CAC back? (Payback period)
3. What is the most I can bid to acquire a customer? (Max allowable CAC)

I built it the first time in 2019. I still build it the same way in 2026. Nothing fancy — but the math is correct, the inputs are honest, and you can defend every cell to a CFO (Chief Financial Officer, 首席财务官).

Why most LTV models fail

There are roughly three kinds of LTV models in the wild, and they are all bad for different reasons.

The "AOV × N" model. This is what you get from a Shopify report that says "LTV = Average Order Value × Number of Orders." It ignores retention, ignores churn, and overstates LTV by 2-5x for any business where customers do not reorder within 30 days. A 2020 case from a Shopify Plus apparel client: their Shopify "LTV" was $187. Their actual LTV (using retention data) was $74. That is a $113 difference per customer — at 4,000 customers per month, that is $452,000 per year of "LTV" they were using to justify ad spend that was not actually profitable.

The "ML model" model. A data scientist builds a BG/NBD (Beta-Geometric/Negative Binomial Distribution, 经典客户预测统计模型) + Gamma-Gamma model in Python. It produces beautiful LTV predictions. Nobody on the marketing team can explain how it works. When churn behavior changes (a product update, a new competitor, a season), nobody can update it. It dies in a Notion doc.

The vendor black box. Klaviyo, Shopify, Triple Whale, Northbeam — all of them have an "LTV" number. The math is opaque, the inputs are unclear, and the number often changes weekly as the vendor re-tunes. I had a client switch from Triple Whale to Northbeam and their reported LTV jumped 22% overnight. Same customers, same data, different vendor math. That tells you the number was always a guess.

The 5-input spreadsheet model is none of those. It is explicit, defensible, and editable. The tradeoff is that it assumes retention is roughly constant — which is fine for most businesses under $50M ARR (Annual Recurring Revenue, 年度经常性收入) but breaks for hyper-growth, virality-driven products.

The 5 inputs

You need exactly five numbers. For a B2C (Business to Consumer, 面向消费者) ecommerce business, these are the data sources:

#	Input	Source	What it captures
1	AOV (Average Order Value, 平均客单价)	Shopify > Analytics > Average order value, last 12 months	How much each transaction is worth
2	Annual Purchase Frequency	Total orders / Unique customers, last 12 months	How often customers come back
3	Gross Margin % (毛利率)	Income statement — (Revenue − COGS) / Revenue	What you keep per dollar of revenue
4	Annual Churn Rate % (年流失率)	(Customers lost in trailing 12 months) / (Customers at start of period)	How fast the customer base erodes
5	CAC (Customer Acquisition Cost, 获客成本)	(Marketing spend) / (New customers acquired)	What you pay per new customer

Two of these are easy: AOV and CAC are one-clicks in your dashboard. Two are medium: Annual purchase frequency requires a customer-count export (Shopify gives you "unique customers" if you toggle the right report). Gross margin is an accounting number, not a marketing one — get it from your finance team or compute it from COGS (Cost of Goods Sold, 销售成本) if you manufacture.

Annual churn rate is the one most people get wrong. They confuse revenue churn (which can be high) with customer churn (which is what LTV needs). For a B2C business, count individual customers who ordered in the prior 12 months but did not order in the trailing 12 months. Divide by the customers who ordered in the prior 12 months. That is customer churn. If you have under 2,000 customers, your churn number will be noisy — use a 24-month window.

The 4 outputs (and the formulas)

Plug the 5 inputs into these six formulas. In a spreadsheet, put the inputs in row 2 (A2 through E2) and the formulas in row 3.

Customer Lifespan (years)    = 1 / Annual Churn Rate
Annual Contribution Margin    = AOV × Annual Frequency × Gross Margin %
LTV                           = Annual Contribution Margin × Customer Lifespan
LTV:CAC ratio                 = LTV / CAC
Payback Period (months)       = CAC / (Annual Contribution Margin / 12)
Max Allowable CAC (3:1 rule)  = LTV / 3

That is the entire model. Six formulas. Three inputs that come from your dashboard, one from accounting, one from a marketing export.

Let me run a real example. A coffee subscription client I worked with in 2023 had these inputs:

• AOV: $42
• Annual purchase frequency: 8 orders/year (heavy buyers)
• Gross margin: 70%
• Annual churn: 25%
• CAC: $45

The outputs:

• Customer lifespan: 1 / 0.25 = 4 years
• Annual contribution margin: $42 × 8 × 0.70 = $235.20/year
• LTV: $235.20 × 4 = $940.80
• LTV:CAC: $940.80 / $45 = 20.9 : 1
• Payback period: $45 / ($235.20 / 12) = 2.3 months
• Max allowable CAC: $940.80 / 3 = $313.60

That last number was the surprise. The CEO had assumed $45 was the upper bound of what they could spend. The model said they could spend almost 7x that. They tripled their Meta budget, then their Google budget, then started a podcast — all of it funded by the gap between $45 (actual CAC) and $313 (max allowable CAC).

A second example, this one with a worse outcome. A DTC apparel brand in 2022:

• AOV: $78
• Annual purchase frequency: 1.8 orders/year
• Gross margin: 55%
• Annual churn: 60%
• CAC: $85

The outputs:

• Customer lifespan: 1 / 0.60 = 1.67 years
• Annual contribution margin: $78 × 1.8 × 0.55 = $77.22/year
• LTV: $77.22 × 1.67 = $128.66
• LTV:CAC: $128.66 / $85 = 1.5 : 1
• Payback period: $85 / ($77.22 / 12) = 13.2 months
• Max allowable CAC: $128.66 / 3 = $42.89

That brand was spending $85 to acquire a customer whose LTV was $128.66. They were losing money on every transaction. The model said two things: (1) stop scaling ad spend immediately, and (2) the only way to fix this is to move one of the five inputs — raise AOV, raise frequency, raise margin, lower churn, or accept a much lower CAC. The CEO picked churn: they rebuilt the post-purchase flow, added a loyalty program, and 14 months later annual churn was 38% and the model was profitable.

The 3 decisions this model drives

The model is a tool, not an answer. The three decisions it unlocks:

1. Should I scale ad spend?

If LTV:CAC is below 3:1, you are buying customers at a loss on a lifetime basis. Stop scaling. Fix the unit economics first (lower CAC, raise AOV, raise frequency, lower churn). If LTV:CAC is above 5:1, you are leaving profit on the table — every customer you do not acquire is a competitor's gain. Scale aggressively.

2. How aggressively can I scale?

The max allowable CAC tells you the ceiling. If your actual CAC is $45 and your max is $313, you have huge headroom — you can either outbid competitors on the same keywords, or open new channels (offline, podcast, YouTube) that have higher CAC but still clear the 3:1 threshold.

3. Should I worry about payback period?

For venture-backed or growth-stage businesses, payback period matters more than LTV:CAC because of cash flow. For bootstrapped or low-cash businesses, a 12-month payback is the maximum. For venture-funded DTC, you can stretch to 18 months. The 5-input model gives you this number directly. The coffee subscription's 2.3-month payback meant cash was never a problem. The apparel brand's 13.2-month payback meant they were one bad month away from a working-capital crunch.

The ChatGPT prompt (build yours in 90 seconds)

I have a prompt I have been sharing with clients since GPT-4 came out. Paste it into ChatGPT (or Claude) with your 5 numbers and it will return a complete, ready-to-paste Google Sheets or Excel model with all formulas, conditional formatting, and a sanity check.

You are a marketing analytics expert. Build me a complete LTV spreadsheet
model with these 5 inputs as the only cells the user touches. Output: a
list of cell references, the formulas, conditional formatting rules, and
a worked example using my values.

The 5 inputs (I will fill these in):
- B2: AOV (Average Order Value, in dollars)
- B3: Annual purchase frequency (orders per customer per year)
- B4: Gross margin % (as decimal, e.g. 0.70 for 70%)
- B5: Annual churn rate (as decimal, e.g. 0.25 for 25%)
- B6: Customer Acquisition Cost (in dollars)

The 6 outputs and their formulas:
- B9:  Customer lifespan (years) = 1 / B5
- B10: Annual contribution margin = B2 * B3 * B4
- B11: LTV = B10 * B9
- B12: LTV:CAC = B11 / B6
- B13: Payback period (months) = B6 / (B10 / 12)
- B14: Max allowable CAC (3:1 rule) = B11 / 3

Output format:
1. A table with all cell references and the formula in each cell.
2. Conditional formatting: B12 green if >5, yellow if 1-5, red if <1.
3. A worked example using these values: AOV=$42, Frequency=8, GM=70%,
   Churn=25%, CAC=$45.
4. If any input looks unrealistic for the type of business I describe
   (DTC, SaaS, subscription, services), flag it and suggest a healthier
   range.
5. Add a row at the bottom labeled "Health check" that produces a single
   sentence verdict: "Healthy — scale aggressively" / "Warning — fix unit
   economics" / "Critical — stop scaling ad spend."

Do not include any commentary, marketing copy, or "hope this helps!"
I just want the model.

That prompt, with the 5 numbers filled in, will give you a model in under 90 seconds. If you are using a code interpreter model (ChatGPT Advanced Data Analysis, Claude with tools), ask it to verify the math — sometimes the model fumbles the payback formula and gives you payback in years instead of months.

I have also used this exact prompt with Gemini 1.5 Pro and Claude 3.5 Sonnet. The output is similar in quality; the main difference is that GPT-4o tends to add unsolicited advice ("you should also consider...") which I have explicitly told it not to do in the prompt. If you get that, ignore it — the model is the model.

What the model doesn't capture (be honest about it)

The 5-input model assumes retention is constant over time. In reality, retention curves are usually convex — customers churn fast in month 1-2, then the survivors stick around for years. The simple model overweights the "long tail" by assuming the same churn rate forever. For most businesses, this is fine — the overestimate is under 20% and the model is still directionally correct. For high-LTV subscription businesses with sophisticated retention (think: Dollar Shave Club at scale, or any SaaS with strong product-led growth), you need a cohort-based model (which is what the ML people build).

It also ignores expansion revenue — the fact that some customers buy more over time, or upgrade to a higher tier, or refer others. The simple model treats every customer as a static-value entity. For SaaS or B2B, this matters a lot. For DTC ecommerce, it matters less.

And it assumes gross margin is constant, which is rarely true at the SKU level. Some products are 30% margin, some are 80%. The model is a useful aggregate; it is not a per-customer prediction.

One input I do not include that the more sophisticated models do: discount rate (the time value of money). For a venture-backed business with a 5-year horizon, a customer who pays $100 in year 4 is worth less than a customer who pays $100 in year 1. The simple model ignores this. For a 2-3 year payback window, it is a rounding error. For longer horizons or expensive capital, it matters.

If your business is past $30M ARR, past 100,000 customers, or in a category with significant expansion revenue (SaaS tiers, consumables with cross-sell, B2B contracts), graduate to a cohort-based or BG/NBD model. Until then, the 5-input spreadsheet is the right level of complexity.

The version I'd build if I started over

The 5-input model has served me for six years. The single change I would make if I were building it fresh in 2026: I would add a sixth input — a segmentation toggle that lets you compute LTV separately for "new customer," "repeat customer," and "reactivated customer" cohorts. The reason: a new customer acquired via paid ads and a customer reactivated by an email winback have radically different LTVs, and averaging them produces a number that is wrong for both decisions.

A new customer might have a 25% annual churn rate and a $42 LTV. A reactivated customer (someone who churned, then came back through a winback flow) might have a 60% annual churn rate — but they were free to acquire, so even a $15 LTV is profitable. Lumping them together understates the value of paid acquisition and overstates the value of email. With three rows in the spreadsheet, you can bid differently on each.

If you build a six-input version with that toggle, send it to me. I will buy you a coffee.