Customer Lifetime Value Calculator: Estimate Revenue and Contribution

Download the customer lifetime value calculator to calculate customer revenue and contribution over a period you choose. It has separate subscription and repeat-purchase paths, and shows recorded results separately from future estimates.

Start with the Summary tab. The fictional example begins with 100 customers, includes two completed months and projects three more. Its five-month result is $356.36 in revenue and $187.56 in contribution per originally acquired customer, before acquisition costs and fixed overhead.

All example figures in this guide are hypothetical Canadian dollars. They demonstrate the calculation and do not represent client results, industry averages or expected results for your business.

This is a finite-horizon cohort calculator. “Five-month value” means the calculation stops after five months; it does not mean the customer relationship ends then. You can calculate observed value alone or extend it with clearly labelled assumptions. The workbook accepts prepared monthly totals and does not connect to your CRM, payment processor or accounting system.

The released workbook was checked in LibreOffice. Microsoft Excel desktop and web were not tested. Review its Read_Me tab and check the results in your spreadsheet application before using them for a business decision.

Start here: use the example or enter your own history

You do not need a lifetime forecast to get a useful answer. If you know what a group of customers has bought and what it cost to serve them, calculate that recorded value first.

To understand the supplied example

1. Open Summary and compare the Observed, Projected future and Combined finite horizon rows.
2. Open Observed. The two completed months produce $17,100 in retained revenue and $9,000 in contribution across 100 customers.
3. Open Assumptions. Months three to five use subscription churn, sales value, refund and cost assumptions to estimate the additional value.
4. Return to Summary. The $90 already observed and $97.56 projected are both visible, so you can see how much of the answer depends on the future.

To calculate your own observed customer value

Save a copy, then use Settings to name the original group of customers and describe who belongs in it. Enter the original customer count in B7, the number of fully observed months in B8 and the total horizon in B9.

For an observed-only calculation, set the total horizon equal to the observed months. For example, two observed months and a two-month horizon produce no future projection. Start with this route if you do not yet have defensible retention or repeat-purchase assumptions.

In Observed, enter each month’s purchasing customers, orders or billing units, sales before refunds, refunds, direct costs, recovered direct costs and other variable serving costs. Subscription mode also needs the original accounts still active at each month-end. Keep the fixed period numbers and formula columns unchanged.

Complete the revenue, cost, timing and source-confirmation fields in Settings. Use your own review information; the supplied confirmations refer only to the fictional example. Once the required inputs are complete, Summary shows the cohort totals and value per original customer.

The minimum arithmetic is simple:

Observed revenue per customer = cohort revenue after refunds ÷ original customers.

Observed contribution per customer = (net revenue − direct costs + cost recoveries − other variable serving costs) ÷ original customers.

The workbook asks for more context than those two formulas because it needs to know whether the periods and amounts belong together. If you are missing costs, you may have enough evidence to discuss revenue, but not enough to call that revenue profit.

What “customer value” means in this calculator

Revenue value is the amount customers spend under the revenue policy you have selected. Contribution value is what remains after the direct and other variable serving costs included in the model. Value after acquisition also deducts the cost of obtaining the original customers.

For the example, the five-month revenue estimate is $356.36 per customer. Only $187.56 remains after the included serving costs. That second figure is more relevant to acquisition economics because the business cannot spend revenue that it needs to supply the orders.

Even contribution after acquisition has limits. The workbook keeps fixed overhead, financing and income tax outside the stated default cost scope. It does not calculate the company’s net profit. BDC’s explanation of gross and net profit margins helps distinguish narrower sales-cost measures from a result after broader business expenses.

Put the time period into the label whenever you share an answer. “Five-month contribution per original customer” tells a colleague what the number covers. “Our LTV is $187.56” leaves them to guess whether that figure includes ten years of sales, five months of history or a forecast based on last month’s churn.

You may also see the abbreviation CLV rather than LTV. Both are used for customer lifetime value. The important distinction is the calculation beneath the initials: revenue or contribution, observed or projected, before or after acquisition, over which horizon.

Why the original customer count stays fixed

The example begins with 100 customers. Some later stop buying. Their departure is part of the economics of acquiring that group, so the denominator remains 100.

After two months, the example has generated $9,000 of contribution. Dividing by 100 gives $90 per acquired customer. If only 80 accounts remain active and you divide by those 80 instead, the answer rises to $112.50. That measures contribution per remaining account and hides the effect of customers who left.

There can be a valid reason to analyse remaining accounts, such as planning support capacity. Keep that result separately labelled. It should not replace the original-customer denominator in an acquisition-value calculation.

The same principle applies to a retailer. A customer who makes one purchase and never returns still belongs to the group. Removing them after the fact would make repeat-purchase value look stronger without creating any additional revenue.

Walk through the recorded two-month result

The supplied cohort consists of 100 fictional customers with the same starting date. Here is the recorded part before any forecasting takes place.

Month one contribution is $9,500 minus $4,000 minus $500, or $5,000. Month two contributes another $4,000. Together they produce $9,000, which becomes $90 per original customer.

The revenue calculation uses the same population: $9,500 plus $7,600 equals $17,100, or $171 per customer. No assumed churn rate is needed to arrive at either recorded result.

This is useful even if you stop here. You can see what the group has produced so far, how much the serving costs consumed and whether later months are adding meaningful contribution. You can also compare another group at the same elapsed age, provided the customer and cost definitions match.

A month with no new orders still belongs in the history. It may contain a late refund or support cost. If those amounts make the month’s contribution negative, preserve the result. Replacing a negative amount with zero would overstate customer value.

By contrast, an empty cost cell means the amount is unknown until someone verifies it. A known zero says there was no cost in that category. Those two entries should not mean the same thing, especially when an apparently attractive customer value depends on costs that have not yet been collected.

Choose the path that matches how customers buy

The calculator offers Subscription and Repeat modes because cancellations and occasional purchases describe different customer behaviour.

Use Subscription for an ongoing paid relationship

Subscription mode starts with the original accounts remaining at the end of the observed period. Each future month reduces that group by an assumed account churn rate before calculating the next bill. Each surviving account generates one monthly billing unit in this model.

This suits a simplified recurring relationship where continued activity can be represented through retained accounts and a monthly value per account. It does not automatically reproduce every billing arrangement, annual contract, usage charge, upgrade or mid-month cancellation.

The timing convention matters. A customer cancelling before the next bill contributes differently from one who cancels after being charged. The supplied model applies churn before the next billing unit. If your revenue policy requires a different treatment, prepare compatible monthly amounts or have the model adapted and reviewed.

New customers stay outside the original cohort. Subscription reactivations also sit outside this projection design. If customers often leave and return, a one-way survival schedule can miss an important part of the relationship.

Use Repeat when a skipped purchase is ordinary

Repeat mode treats each future month’s purchase opportunity separately. It estimates how many of the original customers will buy, how many orders those buyers will place and the amount per order.

That is a better fit for occasional orders where not buying this month does not mean the relationship has permanently ended. A customer might buy in month three, skip month four and buy again in month five.

The calculation is:

Expected orders = original customers × monthly purchase probability × orders per purchasing customer.

Use the original customer count each month. Applying the probability only to the previous month’s buyers would permanently remove people who skipped a purchase, creating a different model.

When neither simple path is enough

Some businesses combine subscriptions, add-on purchases and reactivations. Others have contracts that renew annually or a few large accounts with highly individual buying patterns. The workbook can help expose the required assumptions, but it may not capture those relationships faithfully without adaptation.

Do not force a complicated business into the easier-looking option just to obtain a number. You can still calculate the observed contribution for a well-defined cohort, then prepare a more suitable future model. Keeping a reliable observed result is more useful than attaching a long forecast whose purchase behaviour does not match the business.

See how the subscription projection reaches $187.56

At the end of the example’s second observed month, 80 of the original 100 accounts remain active. The next three months assume 10% monthly account churn before billing.

That produces 72 expected billed accounts in month three: 80 multiplied by 90%. Month four retains 90% of those 72, giving 64.8. Month five retains 90% of 64.8, giving 58.32.

The fractional accounts are expected quantities used in a projection. They do not imply that anyone can bill part of a customer. Rounding each month’s expected accounts to a whole number would introduce another assumption, so the model retains precision until displaying results.

Each future billing unit in the example has:

• $100 in sales before refunds.
• A 5% refund assumption, leaving $95 revenue.
• $40 of direct cost, with no recovery on refunded sales.
• $5 of other variable serving cost.
• $50 contribution before acquisition and fixed overhead.

Multiply the expected billing units by that $50 contribution:

The $9,756 future contribution divided by the original 100 customers gives $97.56 per customer. Adding the $90 already observed produces the five-month $187.56 result.

Future net revenue uses $95 per billing unit. Across 195.12 expected units, that gives $18,536.40, or $185.364 per original customer. The displayed $185.36 is rounded; calculations retain the underlying precision.

Where to enter the assumptions

Set Projection mode to Subscription in Settings B10. In Observed, enter the active original accounts remaining at each completed month-end. The latest observed value supplies the projection’s starting population.

In Assumptions, enter the monthly account churn in column B for each future period. The example uses 10% in each of months three to five. Column E holds the sales value per billing unit. Columns F through L describe refunds, margin, cost recovery, serving costs and later adjustments. Leave the Repeat-specific inputs in columns C and D blank.

The model permits different assumptions by month. A renewal-heavy period might require a different churn assumption from an ordinary month. Use that flexibility to describe the business, rather than spreading a known change across every month for convenience.

Account churn and revenue retention need different inputs

Losing 10% of accounts does not necessarily mean losing 10% of revenue. The departing accounts might be larger or smaller than average, while remaining accounts might expand or downgrade.

Stripe’s subscription analytics documentation distinguishes subscriber retention from revenue retention and describes how expansion can take revenue retention above 100%. It also gives a particular dashboard definition for subscriber churn. Check the denominator before importing any dashboard rate into a closed-cohort model.

This calculator’s churn assumption concerns the remaining original accounts. If account values differ materially, consider separate cohorts or an explicitly modelled value per retained account. A company-wide revenue retention percentage should not be pasted into the account-churn field.

See the repeat-purchase calculation

For a separate scenario, keep the same original group of 100 customers and the same observed contribution of $90 per customer. Assume purchase probabilities of 60%, 50% and 40% in the next three months. Each purchasing customer is expected to place 1.5 orders.

Expected orders are 100 × 60% × 1.5 = 90 in month three, 75 in month four and 60 in month five. Across the three months, the model expects 225 orders.

Using the same $100 sales value, refund and serving-cost assumptions as the subscription illustration, each order contributes $50. Future contribution is therefore $11,250, or $112.50 per original customer. Add the $90 already observed and the five-month contribution becomes $202.50 per customer.

This higher result does not demonstrate that repeat-purchase businesses are better than subscription businesses. The two scenarios assume different future activity. The point is to use the purchase pattern that fits the group being analysed.

In Settings, choose Repeat. In Assumptions, column C takes purchase probability and column D takes orders per purchasing customer. Leave the Subscription churn field in column B blank. In Observed, leave subscription-specific active-account inputs blank for this mode.

Purchase probability ranges from zero to 100%. Orders per purchaser is at least one because it describes customers who buy. An average of 0.6 orders per original customer is a different quantity; entering it as both a purchase probability and an order-frequency adjustment would count inactivity twice.

If no one is expected to purchase in a future month, set its purchase probability to zero. A later month can still have a positive probability. Existing support costs or older-sale refunds may also remain in the zero-purchase month, so check those separately.

Estimate repeat behaviour from comparable customers

Look at earlier groups that bought similar products under similar conditions and had enough time to reach the relevant month. Count how many original customers purchased and how many orders those purchasers placed.

A holiday promotion, a replenishable product and a one-time project can have very different repeat patterns. Mixing them into one average may produce an assumption that describes none of them well.

Keep the actual customer count beside your estimate. A rate derived from a handful of buyers is much more sensitive to one customer than a rate from a larger comparable group. The spreadsheet will calculate both precisely, but the evidence behind them deserves different levels of confidence.

Get the customer group and the time period right

A cohort is simply the original group you follow. It might be customers making their first paid purchase in a specified period or business accounts beginning a paid relationship under one contract type.

Define whether a customer is a person, household, billing account or organisation. A software company with several seats in one company should not count organisations on one side of the calculation and individual seats on the other without an explicit conversion.

Use a stable internal identifier when preparing the source data. Guest orders, changed email addresses or multiple billing contacts can otherwise split one customer into several or combine different customers incorrectly. The workbook needs prepared totals, so identity resolution must happen before those totals are entered.

Keep customers who stopped buying and customers who later requested refunds in the original group. Exclusions should follow a rule established for the business question, such as excluding internal test orders, rather than whether the customer’s result is attractive.

Give every customer the stated observation time

A customer acquired on the first day of a month has had more time to make another purchase by month-end than one acquired on the last day. Treating both as having a full month of history can understate the later customer’s opportunity to contribute.

The workbook uses completed customer-relative months. Prepare each customer’s first month from their starting event to the next monthly boundary, then their second month, and so on. Use a consistent rule for dates at month-end. Aggregate only after the customer periods have been aligned.

The fictional example avoids this preparation problem by giving every customer the same start date. Your real data may require more work. When customers start on different dates, the observation cut-off must allow everyone in the group to complete each month labelled observed.

Amplitude’s retention documentation illustrates how incomplete intervals can change the eligible population in retention reporting. Product-event retention and paid-customer value remain different measures, but the timing lesson is useful: a customer cannot contribute to a period they have not yet reached.

For comparisons, place cohorts at the same elapsed age. Six months of revenue from an older group should not be described as stronger customer value than two months from a newer group without accounting for the unequal observation period.

Refunds, margins and serving costs change the answer

The most consequential field in a customer-value model is often one whose label looks familiar. “Margin” can mean different things depending on which costs have already been deducted.

In the projection, the gross-margin input is a pre-refund margin. The example’s 60% setting means a $100 sale has $40 of direct cost before any refund treatment. The model then handles refunds and direct-cost recovery separately.

With a 5% refund assumption and no cost recovery, the example retains $95 of revenue but still incurs $40 of direct cost. Gross profit is $55. After $5 in other variable serving cost, contribution is $50.

Multiplying the $95 retained revenue by the 60% pre-refund margin would give $57. That would implicitly reduce the direct cost even though the example assumes it cannot be recovered. The separate refund treatment is why the workbook’s contribution differs from a simple net-revenue-times-margin shortcut.

A refund can reverse revenue without reversing all costs

A returned product might be recovered for resale, written off or never returned. Payment processing and outbound delivery may still cost money. A service refund may leave labour already spent on the job.

Use the cost-recovery input only for the recoverable share of direct costs associated with current refunded sales. The calculation assumes refunded sales have the same direct-cost ratio as the month’s sales. If returns are concentrated in a materially different product group, separate that group or use a more specific cost calculation.

The model also has fields for refunds and recoveries relating to older sales. Those allow a later period to contain a refund even if it has no new orders. Keep the adjustment attached to its original cohort in your source records and count it once.

A negative month can therefore be valid. For example, no new sales and a $300 refund, with no recovery or other costs, gives −$300 contribution. A model that automatically replaces this with zero would lose part of the customer relationship’s cost.

Keep direct and other variable costs separate

Put an expense in one category only. If fulfilment is already part of direct cost, subtracting the same fulfilment fee as an other variable serving cost understates contribution. If it is absent from both, the result is overstated.

Other serving costs can include a cost per order or billing unit and an additional cohort-level amount. Some continue even when there are no purchases, such as support required for earlier sales. The workbook charges the per-order serving cost on refunded orders as well; review that convention against your actual cost behaviour.

It is usually easier to start with the financial amounts you can reconcile than with a broad company margin percentage. A company average may contain a different product mix or cost classification from the particular cohort being modelled.

Reconcile source numbers before extending the forecast

The calculator needs one consistent revenue basis. Sales, invoices, recognised revenue and cash received can describe different events. An annual subscription paid upfront should not automatically be treated as one month of recurring earned revenue, and an unpaid invoice should not silently become cash collected.

Have the person responsible for the accounts confirm the basis you are using and explain it in the workbook. This is a management model, so its output should not be described as an accounting-standard determination or a replacement for the financial statements.

Shopify’s explanation of sales discrepancies shows why totals can differ across reports because of timing, refunds, returns and reporting logic. The lesson is to check what an export includes before assuming two totals should match.

For each observed period, reconcile the order or billing-unit count and the financial amounts to the relevant source records. Investigate differences rather than entering a balancing amount simply to clear the workbook’s check. A duplicate transaction can produce a plausible-looking customer value while still being wrong.

Convert source amounts to Canadian dollars before entry using a consistent documented policy. The workbook’s currency is fixed to CAD. It excludes sales taxes and does not determine whether your business can recover tax paid on costs.

Keep detailed customer and transaction references in your source system. The calculator accepts cohort totals and does not require names, emails or payment details. That makes the model easier to share for review without including unnecessary customer information.

When the history is incomplete

Start with the complete periods and identify the missing information. If only one full month is available, an observed one-month contribution is a more defensible starting point than presenting a long forecast as established lifetime value.

You can create a future scenario to explore what would need to happen, but keep it separate from what is known. Short history cannot establish that a current purchase rate will persist, particularly through a renewal, seasonal change or major change in customer mix.

An unknown cost should remain an uncertainty. If you use an estimate, label its basis and review it when the actual amount arrives. Replacing an unknown with zero can make both observed contribution and any forecast built on it look stronger than the evidence supports.

Change one assumption and see what matters

The subscription example’s combined contribution is $187.56 per customer using 10% monthly churn, a 60% pre-refund margin and three projected months. These settings are fictional; they are not recommended targets.

Keep the observed $90 unchanged and change future monthly churn to 20%. The remaining 80 accounts become 64, 51.2 and 40.96 expected billing units. At $50 contribution each, future contribution is $7,808, or $78.08 per original customer. Combined five-month contribution falls to $168.08.

Now return churn to 10% and reduce the pre-refund margin to 50%. Direct cost becomes $50 per $100 sale. After a $5 refund allowance and $5 other serving cost, contribution per unit is $40. The 195.12 expected billing units produce $7,804.80, giving $168.05 combined contribution per customer when rounded.

Those different changes lead to similar answers, but they imply different questions. The churn case concerns how many accounts continue. The margin case concerns what remains from each sale. A headline LTV alone will not tell you which mechanism needs attention.

Start by changing one future input at a time. Then test combinations that have a business rationale, such as lower order frequency with higher serving costs. Do not assume that an optimistic and pessimistic scenario averaged together produce a statistically estimated expected value.

The workbook’s Sensitivity tab contains illustrative comparisons. Those illustrations help explain the model; their settings do not establish what is likely for your customers. Your most useful alternative is often the one tied to a specific unresolved question in your own records.

Watch how much of the answer comes from the future

In the supplied example, $90 of contribution has been observed and $97.56 is projected. More than half the combined contribution therefore depends on events that have not yet occurred in the scenario.

Extending the horizon generally creates room for more projected value, but also more assumptions about retention, prices and costs. A large lifetime estimate can result from a long horizon rather than strong evidence about customer behaviour.

The workbook supports up to 120 monthly periods. That is a design limit, not an endorsement of forecasting ten years. Choose a horizon that serves the decision and that you can explain. Show how much is observed and projected every time the result is shared.

Compare customer groups without losing differences

Averages help summarize a cohort, but they can conceal concentration. A few large accounts may account for much of the value. A promotion may bring many first-time customers who buy once, while a smaller channel brings people with more repeat orders.

Segment when the distinction answers a real question, such as contract type, first product purchased or acquisition period. Avoid creating so many tiny groups that one exceptional customer determines each result. Keep the original customer count and elapsed observation period visible beside the value.

If you combine compatible cohorts, weight by their original customer counts. A group of 100 customers contributing $90 each produces $9,000. A group of 20 contributing $150 each produces $3,000. Together they produce $12,000 across 120 customers, or $100 per customer.

The simple average of $90 and $150 would be $120. That incorrectly gives the smaller group equal weight. Add the amounts and customer counts first, then calculate the combined result.

Before doing that, check that both groups cover the same elapsed horizon and cost definitions. Correct weighted arithmetic cannot make a six-month value comparable to a two-month value. If one group has more forecast months, retain that observed/projected split too.

A difference between groups can suggest where to investigate. It does not by itself prove that the acquisition channel caused the difference. Product mix, discounts, seasonality and who chose to buy can all influence value.

Use contribution with acquisition cost and cash timing

The example’s $187.56 five-month contribution is before acquisition. If acquiring the original 100 customers cost $10,000 in total, acquisition cost is $100 per customer. The combined contribution after acquisition would be $87.56 per customer.

Enter the total cohort acquisition cost, rather than the per-customer amount, in Settings B14. The workbook divides it by the original customer count and deducts it once in the separate result. Entering $100 in that total-cost field would understate acquisition cost for this example by a factor of 100.

A blank acquisition field means it has not been assessed. An entered zero means you have deliberately specified zero for the cost scope. Include the appropriate media, sales and supporting acquisition costs for the decision, and avoid counting an expense already deducted elsewhere.

The $87.56 scenario still includes future assumptions and excludes the other company costs identified in the model. It is not permission to spend $100 to acquire the next customer. That customer may come from a different channel, buy different products or require different serving costs.

Timing also matters. The cohort has contributed $90 per customer after two observed months, less than the illustrative $100 acquisition cost. The full five-month scenario becomes positive after acquisition, but the business must finance the interval before that later contribution arrives.

Use a cash-flow view where payment timing matters. Neither revenue nor contribution automatically tells you when customer cash is collected or supplier invoices are paid. Capacity matters as well: additional customers can require staff, inventory or systems beyond the cost pattern in an earlier cohort.

For campaign-level revenue and return definitions, use the separate ROAS vs ROI guide. This calculator supplies the customer-value component; a spending decision requires matching acquisition costs, timing and a realistic view of the next customers.

Update the estimate as the customer history grows

Save the forecast before replacing it with new observed data. Otherwise you lose the comparison between what you expected and what happened.

When month three is fully observed, replace its projection with the recorded revenue and costs, increase the observed-month setting and review the remaining future assumptions. Keep the original customer count fixed. Do not leave both the forecast and recorded version of the same month contributing to the total.

Investigate a difference in practical terms. Were fewer original accounts retained? Did repeat buyers place fewer orders? Did discounting lower sales value? Were refunds larger or later? Did serving those customers take more work? Each explanation points to a different change in the model or a different business question.

Distinguish corrections from performance changes. Removing a duplicate order or recording a previously missing refund can legitimately change historical value. That should be described as a correction, rather than a newly achieved gain or loss in customer behaviour.

Optional: account for when future contribution arrives

The workbook can discount future contribution to the observation cut-off using an effective annual discount rate. This is an optional finance assumption. At a zero rate, future nominal contribution remains unchanged.

For each future month, the model divides its contribution by one plus the annual rate raised to the number of future months divided by twelve. It assumes end-of-period future values. The result remains separate from the observed nominal history.

Use a rate and valuation date appropriate to your decision, with finance input where needed. A common customer-age month is not automatically a common calendar valuation date when customers started at different times. The supplied same-start example avoids that issue; staggered cohorts require aligned timing before interpreting a common present value.

Discounting changes how timing is valued. It does not make uncertain churn or repeat-purchase assumptions more reliable. For a short, early-stage analysis, the undiscounted observed/projected split may be the more important thing to understand first.

Finish with an answer you can explain

A useful statement for the supplied example is: “For 100 original customers, two recorded months produced $90 contribution per customer. The next three months add a projected $97.56 under the stated subscription assumptions. Combined five-month contribution is $187.56 before acquisition and fixed overhead.”

For your own business, begin with the same plain questions: which customers, how much completed history, what revenue and serving costs, and how much of the answer depends on future behaviour? Enter the recorded months first. Extend the horizon only when you can explain the assumptions beside it.

The most useful customer value is a number you can update as evidence arrives. A clear observed result, a visible forecast and a defined cost scope give you that starting point.