LTV calculator: formulas and a spreadsheet example
An LTV calculator turns assumptions about customer spending and retention into an estimate. This guide shows the formulas and spreadsheet inputs, including how to keep measured customer value separate from the future value you still expect.
Choose what the calculator should estimate
Write the unit, value definition and time horizon above the calculation. For example: gross profit per new paying account over 12 months. That is a 12-month customer value estimate, not necessarily the full lifetime value.
Use revenue when you need to describe customer spending. For acquisition economics, deduct the relevant delivery costs and name the margin measure. Gross profit is still before acquisition and other operating expenses, so it is not net profit.
A spreadsheet is enough for these calculations. The important feature is the ability to trace an output to its inputs and see which of them are observed rather than assumed.
A formula for repeat purchases
A simple revenue estimate is average order value x purchases per customer per period x number of periods. Use matching time units: purchases per year must be multiplied by years, not months.
In an illustrative scenario, an average order of $50, four orders a year and a three-year relationship gives $600 in revenue. At a constant 40% gross margin, modeled gross-profit value is $240. The three-year relationship and future purchase frequency are assumptions until the data supports them.
In a spreadsheet, put these four inputs in B2 through B5. The revenue formula is =B2*B3*B4. The margin-based formula is =B2*B3*B4*B5, with B5 entered as 40% or 0.4.
Compare the result with acquisition cost
If the scenario produces $240 in customer gross profit and fully loaded CAC is $80, the modeled ratio is 3:1. This does not establish a safe budget: the cash may arrive over several years, and the forecast may be wrong.
Add the cumulative margin by period to see when the acquisition expense is recovered. Then test a lower purchase frequency or shorter relationship. Product decisions about repeat use should be evaluated through their effect on those inputs, not through a target ratio alone.
| Cell | Input | Example |
|---|---|---|
| B2 | Average order value | 50 |
| B3 | Orders per customer per year | 4 |
| B4 | Assumed years of purchasing | 3 |
| B5 | Assumed gross margin | 40% |
| B6 | Revenue value: =B2*B3*B4 | 600 |
| B7 | Gross-profit value: =B2*B3*B4*B5 | 240 |
Calculate observed cohort value without double-counting
For an observed cohort, sum the net revenue earned by that group during a defined window and divide it by the original number of acquired customers. This already includes their purchases during that window. Do not multiply it by purchase frequency again.
For example, 100 acquired customers generate $12,000 of net revenue in their first six months. Observed six-month revenue per acquired customer is $120. If their associated delivery costs total $4,800 under your chosen cost policy, observed margin per customer is ($12,000 - $4,800) / 100 = $72.
Keep customers who stopped buying in the original denominator. Compare this cohort with another at the same age, then show any value forecast beyond month six separately.
Use the subscription shortcut carefully
A simple subscription revenue LTV model divides average recurring revenue per account by customer churn rate. A margin-based version also multiplies by gross margin. Revenue and churn must use the same period, and revenue must be per account, not total company MRR.
With monthly revenue of $50 per account, 80% gross margin and assumed monthly customer churn of 5%, the shortcut gives $50 x 0.80 / 0.05 = $800. It assumes stable revenue, margin and churn, so it can mislead when early cancellations, upgrades or customer mix change.
If observed churn is zero, the formula has no finite answer. That does not prove infinite customer value. Use a finite forecast horizon and explicit retention assumptions, especially for young cohorts or annual plans that have not reached renewal.
Test which assumptions drive the decision
Create separate scenarios for the uncertain inputs and show what changes the conclusion. Do not combine the best observed price, margin and retention from unrelated segments into one optimistic customer.
A retention improvement may require support or incentives that affect cost. A price increase may alter both conversion and renewal. Model these linked changes together, then compare them with what actually happens after the product change.
- Keep the acquired customer definition constant across scenarios.
- Distinguish refunds, discounts and delivery costs according to a stated policy.
- Use repeat-purchase or renewal history that covers the product's actual usage cycle.
- Flag major product, price or channel changes that make old cohorts less comparable.
Update the model when the customer behavior changes
Revisit the calculation when a cohort reaches a meaningful renewal or repeat-purchase window, or when pricing and delivery costs change. There is no number of calendar months that makes every product's lifetime estimate reliable.
Use the difference between the forecast and the observed result to revise the assumptions. If recent customers leave earlier than expected, investigate their experience and acquisition mix before keeping the old LTV in the growth plan.
FAQ
Is there an interactive calculator on this page?
This is a formula guide with a spreadsheet example. Copy the inputs and formulas into your own sheet so you can change the assumptions and inspect the result.
Are LTV and CLV different metrics?
Both abbreviations refer to customer lifetime value. The important difference is whether a particular calculation uses revenue or margin, and whether it measures observed value or forecasts the future.
Can I calculate LTV with three months of data?
You can measure three-month value for a cohort that has reached that age. Estimating a longer relationship requires assumptions, especially if the product's renewal or repeat-purchase cycle is longer than your observation window.
Why does my subscription formula return an error at zero churn?
The shortcut divides by churn, so a zero denominator cannot produce a finite estimate. Use a stated horizon and a retention scenario instead of treating the result as infinite LTV.
Should I divide company MRR by churn?
Not to calculate value per customer. The simple subscription formula uses recurring revenue per account and customer churn for the same period. Total company MRR would produce a differently scaled and misleading result.
Sources
Discuss your product's next step
I help define product priorities, plan launches and investigate where users drop out.
