A well-structured customer acquisition cost (CAC) and lifetime value (LTV) calculator in Excel helps you connect marketing spend with contribution profit, compare channels fairly and estimate how long it may take to recover acquisition costs. This guide shows how to build a repeatable workbook for actual results, forecast scenarios and monthly payback reviews.
Overview
CAC and LTV are most useful when they are analysed together. CAC measures the cost of acquiring a customer, while LTV estimates the value that customer is expected to generate over a defined relationship or forecast period. Comparing the two can support decisions about campaign budgets, pricing, retention activity and channel mix.
A simple CAC calculation is:
CAC = attributable acquisition costs ÷ new customers acquired
For example, if a campaign has £6,000 of attributable costs and produces 120 new customers, its CAC is £50. The calculation is only meaningful if the cost period and customer period match. Monthly marketing costs should normally be compared with customers acquired during the same month, while a longer campaign may require a clearly defined campaign window.
LTV can be expressed in two different ways:
- Revenue LTV: the expected sales revenue from a customer.
- Gross-profit LTV: expected sales multiplied by the gross margin, after direct costs but before wider operating expenses.
Gross-profit LTV is generally more useful for payback planning because it reflects the money available to recover acquisition costs. Revenue LTV can still be helpful for sales analysis, but it should not be treated as available profit.
Keep the model separate from a broader ROI calculator for business projects. ROI may include a wider set of investment costs and returns, whereas CAC and LTV focus specifically on customer acquisition and customer economics.
How to estimate CAC, LTV and payback in Excel
Build the workbook with separate sheets so that source data, assumptions and outputs can be checked independently. A practical structure is:
- Inputs: editable assumptions such as average order value, gross margin, repeat purchase rate and churn.
- Actuals: monthly spend, customers, orders and revenue by channel.
- Calculations: CAC, LTV, contribution profit and payback outputs.
- Scenarios: base, conservative and upside cases.
- Dashboard: a concise comparison of channels and monthly trends.
Use an Excel Table for the actuals data. If the table is named MarketingData, a blended CAC formula could be:
=SUM(MarketingData[Total Cost])/SUM(MarketingData[New Customers])
For channel-specific CAC, filter the table by channel. With a channel name in cell A2, a formula using SUMIFS could be:
=SUMIFS(MarketingData[Total Cost],MarketingData[Channel],A2)/SUMIFS(MarketingData[New Customers],MarketingData[Channel],A2)
For a straightforward order-based LTV estimate, use:
=Average Order Value*Expected Orders per Customer*Gross Margin
If repeat orders are not recorded directly, an assumption-based version might use a first order and repeat purchase rate:
=Average Order Value*(1+Expected Repeat Orders)*Gross Margin
For a time-based model, create one column per month and calculate active customers, orders, revenue and gross profit for each period. This is usually more transparent than relying on a single lifetime assumption, particularly where retention changes over time.
Marketing payback can be estimated as:
Payback period = CAC ÷ monthly gross profit per customer
If CAC is £50 and the model estimates £12.50 of monthly gross profit per customer, the payback period is four months. This is an estimate, not a promise: refunds, delayed purchases, discounts and changing retention can extend the actual period.
Inputs and assumptions
Use one input cell for each assumption and label its unit, period and source. Avoid burying constants inside formulas. Useful inputs include:
- Marketing spend: paid media, creative, promotional costs and other costs assigned to acquisition. Decide whether staff or software costs are included and apply the same rule across channels.
- New customers: count new customers rather than transactions. A customer who places several orders should normally count once for CAC.
- Average order value: define whether this is before or after discounts, refunds, VAT and delivery charges. Use a consistent revenue definition.
- Gross margin: include the direct costs relevant to the product or service. Do not confuse margin with markup; the product pricing and profitability workbook can help separate these concepts.
- Repeat purchase rate: specify the observation period, such as the share of customers making another purchase within a defined number of months.
- Churn or retention: state whether churn refers to customers, subscriptions or revenue. These are different measures and should not be mixed.
- Attribution rule: document whether costs are assigned by last click, first touch, an internal allocation or another method. Use the same rule when comparing periods.
Blended CAC combines all acquisition costs and all new customers. It is useful for assessing the total marketing engine, but it can hide differences between channels. Channel-specific CAC assigns costs and customers to individual sources, which supports budget decisions but depends on reliable tracking and an agreed attribution method.
Do not include personal data in a shared calculator unless there is a clear business need and appropriate access control. The model normally needs aggregated counts and financial totals, not names, email addresses or individual purchase histories. If customer-level data is imported, follow your organisation’s data governance requirements and remove unnecessary fields.
Worked examples
Assume a monthly campaign produces the following results:
| Measure | Value |
|---|---|
| Marketing and attributable acquisition cost | £6,000 |
| New customers | 120 |
| Average order value | £80 |
| Expected orders per customer | 2.5 |
| Gross margin | 40% |
CAC is £6,000 divided by 120, or £50 per new customer. Revenue LTV is £80 multiplied by 2.5, or £200. Gross-profit LTV is £200 multiplied by 40%, or £80. The gross-profit LTV to CAC comparison is therefore £80 to £50, before considering overheads, refunds and retention uncertainty.
Now add scenarios to test the assumptions. In a conservative case, reduce expected orders per customer or gross margin. In an upside case, increase repeat purchases only if there is a reasonable operational basis for doing so. A data table can show how payback changes as CAC, order value and retention vary. This is more useful than presenting one precise-looking forecast.
Compare the results with operational measures in a sales KPI dashboard template, and use a monthly variance analysis template to explain why actual CAC or LTV differs from the forecast.
When to recalculate
Refresh the model at least once per campaign cycle or month, depending on the volume and speed of your business. Import actual spend and customer results, then compare actual CAC with the forecast. Update the LTV assumptions when pricing, product mix, discounts, gross margin or repeat purchase behaviour changes.
Recalculate promptly when a channel’s tracking method changes, a campaign targets a different audience, or a new product has a different margin. Review payback after a meaningful change in conversion or retention rather than waiting for the next annual planning cycle.
Before sharing the output, run a short data-quality checklist:
- Are spend and customer counts from the same period?
- Are new customers distinguished from orders and returning customers?
- Are VAT, refunds, discounts and delivery costs treated consistently?
- Are channel attribution rules documented?
- Are the gross margin and retention assumptions dated?
- Does the workbook show actuals separately from forecasts?
- Are unusual results explained rather than silently removed?
Save a dated version after each monthly review so that assumptions can be traced. A CAC and LTV calculator is most valuable as a working decision tool: update it with actual results, test the next budget scenario and make the payback period visible before increasing spend.