Customer Lifetime Value (LTV) Spreadsheet for Small Business

Customer lifetime value (LTV) answers one of the most important questions in business: how much is a customer actually worth? Without knowing this, you can’t make rational decisions about how much to spend acquiring new customers or how much to invest in keeping existing ones.

The Basic LTV Formula

LTV = Average Purchase Value × Purchase Frequency × Customer Lifespan

Where:

• Average Purchase Value = Total revenue ÷ Number of orders (in a period)
• Purchase Frequency = Total orders ÷ Number of unique customers (in a period)
• Customer Lifespan = Average number of years a customer stays active (or months if you think in those terms)

Example calculation:

• Average purchase value: $120
• Purchase frequency: 2.5x per year
• Average customer lifespan: 4 years
• LTV = $120 × 2.5 × 4 = $1,200

The Profit-Based LTV (More Useful for Decisions)

Revenue-based LTV ignores your margins. Profit-based LTV is what you actually keep:

Profit-based LTV = LTV × Gross Margin %

Example: $1,200 LTV × 40% gross margin = $480 in gross profit per customer over their lifetime

This is the number that matters for acquisition budget decisions — you can’t spend more than your gross profit to acquire a customer.

Building the Spreadsheet

Data you need:

• Transaction history: customer ID, date, amount
• Customer acquisition date (when they first purchased)
• Active vs. churned status

Calculations (per customer):

Customer	First purchase	Latest purchase	Total spent	# Orders	Avg order	Monthly spend

From this data:

• Customer lifespan = months between first and latest purchase (or until churn)
• Monthly spend rate = Total spent ÷ Months active

Then aggregate across all customers to get average values.

Segmenting LTV by Customer Type

Not all customers have the same LTV. Segment to find patterns:

By acquisition channel:

• Referral customers: LTV = ?
• Google Ads customers: LTV = ?
• Email list customers: LTV = ?

If referral customers have 3x the LTV of ad customers, your referral program deserves far more investment.

By first product purchased:

• Customers who buy Product A first: LTV = ?
• Customers who buy Product B first: LTV = ?

If Product A customers have higher LTV, make Product A your front-of-funnel offer.

By cohort (year they first purchased): Are customers acquired this year more or less valuable than customers from 2 years ago? Cohort analysis shows whether your customer quality is improving or declining.

LTV:CAC Ratio

CAC (Customer Acquisition Cost) = Total sales and marketing spend ÷ New customers acquired

LTV:CAC ratio = LTV ÷ CAC

Benchmarks:

• Below 1:1: You’re losing money acquiring customers. Unsustainable.
• 1:1 to 3:1: Marginal. Barely worth it, or breaking even.
• 3:1 or higher: Healthy. You’re acquiring customers profitably.
• 5:1+: Either very efficient or underinvesting in growth.

If your profit-based LTV is $480 and you’re spending $200 to acquire a customer, that’s a 2.4:1 ratio — workable but worth improving. If you could raise LTV through better retention or increase order frequency, the same acquisition cost looks much better.

Using LTV to Make Decisions

Set acquisition budgets: Never spend more than your profit-based LTV to acquire a customer. If LTV is $480, your CAC limit is $480 — though 3:1+ means targeting $160 CAC or below.

Prioritize retention: A 5% increase in customer retention increases profit by 25-95% (Bain & Company research). Because of compounding purchase frequency, small retention improvements have large LTV impacts.

Identify high-value customers for personal attention: The top 20% of customers by LTV often represent 60-80% of your revenue. Know who they are and treat them accordingly.

Evaluate product strategy: Products that acquire high-LTV customers deserve more investment. Products that attract low-LTV or high-churn customers may not be worth the shelf space.

Pull three months of transaction data and build this spreadsheet this week. Even a rough LTV calculation based on averages is far more useful than no number at all. The real value comes from the segments — understanding which customers and channels are actually driving your business.