Customer lifetime value (LTV)

Why it matters

LTV tells you how much you can afford to spend to acquire a customer. Without it, there is no way to know whether a CAC is high or low.

Worked example

The table shows the columns this metric needs from six months of a made-up subscription brand. Add each column up over the six months, then put the totals into the formula:

₹14,20,800 ÷ 6,643 × (₹28,28,700 − ₹15,39,900) ÷ ₹28,28,700 ÷ (320 ÷ 6,095) = ₹1,856

Add up first, then divide. Averaging the six monthly figures gives a slightly different answer, because it counts a small month the same as a large one.

Month by month

LTV calculator

How to read it

This formula assumes churn stays constant, which is rarely true; customers who stay a year tend to stay longer. Check it against real cohort data once you have a year of history.

Common mistakes

• Use gross profit, not revenue. LTV in revenue terms overstates what you can spend on acquisition.
• Small changes in churn swing LTV a lot because churn is in the denominator. Treat the figure as a range.
• For non-subscription brands, use average order value × purchase frequency × expected years × gross margin instead.

Calculate customer lifetime value in Excel, SQL or Python

The quickest version, for the sample sheet laid out as in the CSV (headers in row 1, months in rows 2 to 7):

=SUM(AF2:AF7) / SUM(Z2:Z7) * (SUM(B2:B7) - SUM(C2:C7)) / SUM(B2:B7) / (SUM(Y2:Y7) / SUM(X2:X7))

• Customer lifetime value in Excel and Google Sheets — per-month formula and zero-safe version
• Customer lifetime value in SQL — query by month and for the period
• Customer lifetime value in Python (pandas) — pandas script with expected output

Related metrics

• LTV to CAC ratio (LTV:CAC)
• Customer acquisition cost (CAC)
• Average revenue per user (ARPU)
• Customer churn rate
• CAC payback period

See all business metrics, or the statistics methods that sit behind them.

Questions people ask

What is customer lifetime value?

Customer lifetime value (LTV or CLV) estimates the gross profit a customer brings over the whole time they stay. A common formula for subscriptions is average monthly revenue per customer × gross margin ÷ monthly churn rate.

How do you calculate customer lifetime value?

LTV = ARPU × Gross margin ÷ Monthly churn rate. For example, with six months of sample data: ₹14,20,800 ÷ 6,643 × (₹28,28,700 − ₹15,39,900) ÷ ₹28,28,700 ÷ (320 ÷ 6,095) = ₹1,856.

What is the LTV formula in Excel?

With the sample data laid out as in the downloadable CSV, =SUM(AF2:AF7) / SUM(Z2:Z7) * (SUM(B2:B7) - SUM(C2:C7)) / SUM(B2:B7) / (SUM(Y2:Y7) / SUM(X2:X7)) gives the figure for the whole period. For one month, use =AF2 / Z2 * (B2 - C2) / B2 / (Y2 / X2) and fill it down.

What is a good customer lifetime value?

There is no figure that is right for every business: it depends on your products, prices and channels. This formula assumes churn stays constant, which is rarely true; customers who stay a year tend to stay longer. Check it against real cohort data once you have a year of history.

Check how stable it is

• Standard deviation
• Moving average
• Z-score
• Linear trend forecast

Guides

• How to find out what changed in your sales data — 2 min read
• Real change or random noise? A plain guide to reading your numbers — 2 min read
• Excel data analysis: formulas, PivotTables and the ToolPak — 3 min read

About the data on this page

The figures come from a small made-up dataset, printed above in full so you can check every number by hand. Download it as monthly.csv. The results are computed from those rows when the page is built, and the Excel formulas, SQL and Python on these pages are run against the same rows before publishing, so the code and the answers agree.

To get these numbers from your own data without writing formulas, try AnalyzeIt: ask in plain words and it shows the working behind every figure.