Customer Lifetime Value Calculator in Google Sheets

Photo by Giorgio Tomassetti on Unsplash

Why customer lifetime value matters

Customer lifetime value (CLV) answers a deceptively simple question: how much is one customer actually worth? Knowing that figure helps you decide how much to spend acquiring new customers, how aggressively to invest in retention, and which customer segments are most profitable. Without a CLV estimate, marketing budgets and pricing decisions rest on intuition rather than numbers.

Google Sheets is a practical environment for CLV modelling because it keeps your assumptions visible and lets you update inputs as your business data changes. The GSheetPress Calculator tool lets you publish that model as a shareable web calculator without any coding.

Key terms explained

• Customer Lifetime Value (CLV): The total net profit expected from a single customer across the entire length of their relationship with your business.
• Average Order Value (AOV): Total revenue divided by the number of orders in a period. For example, £12,000 revenue from 400 orders gives an AOV of £30.
• Purchase Frequency (PF): How many times the average customer buys from you in a single year.
• Customer Lifespan (L): The average number of years a customer remains active before churning.
• Gross Margin (GM): Revenue minus the direct cost of goods or services, expressed as a percentage of revenue. A 40% gross margin means 40 pence of every pound remains after direct costs.
• Customer Acquisition Cost (CAC): Total marketing and sales spend divided by the number of new customers won. If you spent £5,000 and gained 100 customers, your CAC is £50.
• Servicing Cost: The ongoing per-customer cost of support, account management, or delivery that recurs each year.
• Contribution-Based CLV: A CLV model that starts with gross-margin revenue, then deducts annual servicing costs and the one-off acquisition cost, giving a figure closer to true profit.
• Churn Rate: The proportion of customers who stop buying in a given year. A 20% annual churn rate implies an average lifespan of 1 ÷ 0.20 = 5 years.
• Constant-Behaviour Assumption: The simplifying rule that a customer's order value, purchase frequency, and churn rate stay the same every year. Real customers rarely behave this way, which is the core limitation of both models in this guide.
• Runtime Inputs (column G): The column in the GSheetPress calculator template where web form submissions are written. All result formulas must reference G cells rather than E cells (which hold only the display defaults).

Two CLV models side by side

Before building anything, it helps to see what each formula looks like and what it leaves out.

The contribution-based model is more conservative and more honest. It still assumes the same AOV, frequency, and margin every year — an assumption you should revisit with real cohort data whenever possible. For a rigorous treatment of discount-rate adjustments and probabilistic CLV models, the Harvard Business Review's work on customer loyalty economics is a useful starting point.

Prerequisites

• A Google account with access to Google Sheets.
• GSheetPress installed: visit the GSheetPress sign-up page, create an account, and install the Google Workspace add-on from the link provided after sign-up. Open Google Sheets and confirm that Extensions → GS2Web SaaS appears in the menu bar.
• A Calculator entitlement on your GSheetPress plan. Check your plan at GSheetPress pricing to confirm the Calculator feature is included before proceeding.
• A dedicated sample workbook. Create a new, blank Google Sheet and name it CLV Calculator (Sample). Do not use a sheet that contains real customer or financial data.

Step 1 — Understand your inputs

The contribution-based model needs six numbers. Collect estimates for each before you open the spreadsheet. The table below shows what each input measures and a worked example value used throughout this guide.

Note on percentages: Enter gross margin as a whole number. Type 40 to mean 40%. The formula divides this by 100 internally. If you enter 0.40 instead, your result will be 100 times too small.

Step 2 — Verify the formulas manually

Before creating the web calculator, build the formula logic in a separate tab and confirm the numbers are correct.

1. In your CLV Calculator (Sample) workbook, click the + icon at the bottom of the screen to add a new sheet. Name it Formula_Check.
2. Enter the labels and values below. Column A holds labels; column B holds the numbers.

3. Click cell A8 and type Simple CLV (£). Click cell B8, type the equals sign, then complete the formula so B8 contains:

=B1*B2*B4

4. Click cell A9 and type Contribution-Based CLV (£). Click cell B9, type the equals sign, then complete the formula so B9 contains:

=(B1*B2*(B3/100)*B4)-(B5*B4)-B6

With these example values you should see:

• B8 (Simple CLV): 600 — calculated as 50 × 4 × 3.
• B9 (Contribution-Based CLV): 150 — calculated as (50 × 4 × 0.40 × 3) − (20 × 3) − 30 = 240 − 60 − 30 = 150.

Keep this tab open. You will compare its results against the web calculator output in the testing step.

Build the web calculator

With the formula verified, you can create the public-facing web calculator. The GSheetPress Calculator add-on generates a structured template with fixed header columns in row 1. You fill in the rows below to define each input field, then add result formulas to the rows below the generated button and separator.

Create a new calculator tab

1. Open your CLV Calculator (Sample) workbook.
2. Click Extensions in the top menu bar.
3. Hover over GS2Web SaaS (or the name of your installed add-on).
4. Click Calculator, then click New Calculator.
5. When prompted for a name, enter CLV Calculator.
6. When asked for the number of inputs, enter 6. This count does not include the Calculate button.
7. When asked whether to include graphs, select No.

The add-on creates a new sheet tab. Row 1 is pre-populated with eight header columns: Labels, Input Type, Required, Data, Default, ToolTips (some template versions spell this ToopTips — use whichever heading your sheet shows), Inputs, Rules. Do not rename, move, or delete any of these headers.

With 6 inputs, the row layout is fixed as follows:

• Rows 2–7: one input field per row.
• Row 8: Calculate button, generated automatically — do not delete or overwrite.
• Row 9: merged Result Fields separator, generated automatically.
• Row 10: result section heading row.
• Rows 11 onward: individual result rows where you add labels and formulas.

How columns E and G differ

Column E (Default) stores the value shown to a visitor when the form first loads. Column G (Inputs) is overwritten by the values a visitor submits on the web form. All result formulas must reference column G. Never put result formulas inside column G itself, because submissions will overwrite them. To preview results using your defaults before publishing, copy E2:E7 and paste into G2:G7 using Edit → Paste special → Values only.

Input row 2 — Average Order Value

• A2 — Label: Average Order Value (£)
• B2 — Input Type: Number
• C2 — Required: Tick the checkbox to make this field mandatory.
• D2 — Data: Leave blank.
• E2 — Default: 50
• F2 — ToolTips: Enter the average amount spent per order. Example: if 400 orders generated £20,000 revenue, enter 50.
• G2 — Inputs: Leave blank. This cell is overwritten by web form submissions.
• H2 — Rules: Leave blank.

Input row 3 — Purchase Frequency

• A3 — Label: Purchase Frequency (per year)
• B3 — Input Type: Number
• C3 — Required: Tick the checkbox.
• D3 — Data: Leave blank.
• E3 — Default: 4
• F3 — ToolTips: How many times per year does the average customer place an order?
• G3 — Inputs: Leave blank.
• H3 — Rules: Leave blank.

Input row 4 — Gross Margin

• A4 — Label: Gross Margin (%)
• B4 — Input Type: Number
• C4 — Required: Tick the checkbox.
• D4 — Data: Leave blank.
• E4 — Default: 40
• F4 — ToolTips: Enter as a whole number. Type 40 to represent 40%. Do not enter 0.40.
• G4 — Inputs: Leave blank.
• H4 — Rules: Leave blank.

Input row 5 — Customer Lifespan

• A5 — Label: Customer Lifespan (years)
• B5 — Input Type: Number
• C5 — Required: Tick the checkbox.
• D5 — Data: Leave blank.
• E5 — Default: 3
• F5 — ToolTips: Average number of years a customer stays active. To estimate this, divide 1 by your annual churn rate. A 20% churn rate gives a lifespan of 5 years.
• G5 — Inputs: Leave blank.
• H5 — Rules: Leave blank.

Input row 6 — Annual Servicing Cost

• A6 — Label: Annual Servicing Cost per Customer (£)
• B6 — Input Type: Number
• C6 — Required: Tick the checkbox.
• D6 — Data: Leave blank.
• E6 — Default: 20
• F6 — ToolTips: Total annual support, account management, or delivery costs divided by the number of active customers.
• G6 — Inputs: Leave blank.
• H6 — Rules: Leave blank.

Input row 7 — Customer Acquisition Cost

• A7 — Label: Customer Acquisition Cost (£)
• B7 — Input Type: Number
• C7 — Required: Tick the checkbox.
• D7 — Data: Leave blank.
• E7 — Default: 30
• F7 — ToolTips: Total marketing and sales spend to win one new customer. Divide your campaign spend by the number of new customers acquired in the same period.
• G7 — Inputs: Leave blank.
• H7 — Rules: Leave blank.

Row 8 — Calculate button (generated, do not edit)

Row 8 is created automatically by the add-on. Column A holds the button label the add-on inserted. Column B reads Button and column D reads main. Column E may show Reset, which controls the reset button behaviour and is not the Calculate label. Do not overwrite any cell in row 8.

Add result formulas — rows 11 onward

Result labels go in column A and result formulas go in column B. Every formula references G2 through G7 because those cells receive the values submitted by a visitor. Do not reference column E in any result formula.

Result row 11 — Simple CLV

1. Click cell A11 and type: Simple CLV (£)
2. Click cell B11. Click in the formula bar, type the equals sign to begin the formula, and enter the full formula below, then press Enter:

=IF(OR(G2=0,G3=0,G5=0),"Inputs required",ROUND(G2*G3*G5,2))

This formula checks that the three inputs needed for simple CLV are not zero. If any are zero it returns the text Inputs required rather than a misleading zero result. With the example defaults (G2=50, G3=4, G5=3) the result is 600.

Result row 12 — Annual Gross Margin Revenue per Customer

1. Click cell A12 and type: Annual Gross Margin Revenue (£)
2. Click cell B12 and enter:

=IF(OR(G2=0,G3=0,G4=0),"Inputs required",ROUND(G2*G3*(G4/100),2))

This is the gross margin contribution per customer per year. With G2=50, G3=4, G4=40 the result is 80 — that is 50 × 4 × 0.40.

Result row 13 — Total Gross Margin Revenue over Lifespan

1. Click cell A13 and type: Total Gross Margin over Lifespan (£)
2. Click cell B13 and enter:

=IF(OR(G2=0,G3=0,G4=0,G5=0),"Inputs required",ROUND(G2*G3*(G4/100)*G5,2))

With the defaults this returns 240 — that is 80 × 3.

Result row 14 — Total Servicing Cost over Lifespan

1. Click cell A14 and type: Total Servicing Cost over Lifespan (£)
2. Click cell B14 and enter:

=IF(OR(G5=0,G6=0),"Inputs required",ROUND(G6*G5,2))

With G6=20 and G5=3 this returns 60.

Result row 15 — Contribution-Based CLV

1. Click cell A15 and type: Contribution-Based CLV (£)
2. Click cell B15 and enter:

=IF(OR(G2=0,G3=0,G4=0,G5=0),"Inputs required",ROUND((G2*G3*(G4/100)*G5)-(G6*G5)-G7,2))

With all defaults applied: (240) − (60) − 30 = 150. This is the headline result of the contribution-based model.

Result row 16 — CLV to CAC Ratio

1. Click cell A16 and type: CLV to CAC Ratio
2. Click cell B16 and enter:

=IF(OR(G7=0,G2=0,G3=0,G4=0,G5=0),"CAC required",ROUND(((G2*G3*(G4/100)*G5)-(G6*G5)-G7)/G7,2))

A ratio above 1 means the customer generates more net value than it cost to acquire them. With a contribution-based CLV of 150 and a CAC of 30, the ratio is 5, meaning you recover the acquisition cost five times over the customer's lifetime. A ratio below 1 indicates the relationship is currently unprofitable.

Verify results with the default values

Before testing on the web, paste the defaults into column G so you can check every formula in the sheet itself.

1. Select cells E2:E7.
2. Press Ctrl+C (or Cmd+C on Mac) to copy.
3. Click cell G2.
4. Choose Edit → Paste special → Values only.

The expected results with the example defaults are:

If any cell shows a different number, re-read the formula in that cell and check that you referenced G2 through G7, not E2 through E7, and that you did not accidentally omit the division by 100 on the gross margin input.

Alternative scenario check

To confirm the formula handles a different set of inputs correctly, mentally substitute: AOV=80, Frequency=2, Margin=30%, Lifespan=5, Servicing=25, CAC=60.

• Annual gross margin revenue: 80 × 2 × 0.30 = 48
• Total gross margin over lifespan: 48 × 5 = 240
• Total servicing cost: 25 × 5 = 125
• Contribution-Based CLV: 240 − 125 − 60 = 55
• CLV to CAC ratio: 55 ÷ 60 = 0.92 — less than 1, meaning this customer segment is currently loss-making on a contribution basis.

Paste these values into G2:G7 using Paste special → Values only and confirm your formulas return 55 and 0.92 respectively before proceeding.

Limitations to communicate to users

Include these caveats wherever you share the calculator — for example in a note below the published form or in the tooltip of a result field:

• The model assumes AOV, purchase frequency, and gross margin are constant across every year of the relationship. Real customer behaviour changes over time.
• No discount rate is applied, so the model does not account for the time value of money. A pound received in year three is treated as equivalent to a pound received today.
• Overhead costs, tax, and capital costs are excluded. The contribution-based CLV is not the same as net profit per customer.
• Servicing cost is treated as fixed per customer per year. In practice, long-term customers often require less support, or more, depending on your business model.
• The churn rate is assumed constant. If early-period churn is higher than later-period churn (common in subscription businesses), the simple lifespan estimate will be inaccurate.

Frequently asked questions

What is a good CLV to CAC ratio?

A ratio of 3:1 is widely cited as a healthy benchmark — meaning the lifetime contribution from a customer is three times the cost of acquiring them. A ratio below 1 means you are spending more to acquire customers than they return in contribution over their lifetime, which is unsustainable without other revenue sources. A very high ratio (above 10) can indicate that you are under-investing in acquisition and leaving growth on the table. These are industry rules of thumb, not guarantees, and your own unit economics and growth stage matter far more than any generic benchmark.

Why does my contribution-based CLV come out negative?

A negative result means the combined servicing costs and acquisition cost exceed the gross margin revenue generated over the customer's lifespan. Common causes include: a low gross margin percentage (try checking whether you entered a decimal like 0.40 instead of the whole number 40); a very short lifespan (high churn rate); or a high acquisition cost relative to order value. Review each input, correct the gross margin entry format first, and recalculate.

Can I add a discount rate to account for the time value of money?

Yes, but it requires a more complex formula. A simplified approach multiplies the annual gross margin contribution by the sum of a discount factor for each year, rather than simply multiplying by the total lifespan. For example, with a 10% annual discount rate, year one is worth 1.00, year two is worth 1 ÷ 1.10 = 0.91, and year three is worth 1 ÷ (1.10 squared) = 0.83. You would add a seventh input for the discount rate and replace the lifespan multiplier with a SUMPRODUCT or manual year-by-year calculation. The model in this guide deliberately omits this step to keep it accessible, but the Google Sheets NPV function documentation explains the net present value approach you would need.

Troubleshooting

Publish and test your web calculator

Share the workbook

GSheetPress requires the spreadsheet to be shared before a published calculator can read its structure. This step is mandatory.

1. In your CLV Calculator (Sample) workbook, click Share in the top-right corner.
2. Under General access, open the dropdown and select Anyone with the link.
3. Set the role to Editor.
4. Click Done.

Important: Setting access to Editor means anyone who obtains the spreadsheet link can modify the sheet. This is why you must use a dedicated sample workbook containing only example data, never a sheet with real customer records or financial information.

Publish the calculator

1. Click the tab for your calculator sheet (the tab the add-on created, not the Formula_Check tab) to make sure it is the active, selected tab.
2. Click Extensions → GS2Web SaaS → Calculator → Publish Calculator.
3. Wait for the confirmation message to appear in the add-on panel. Do not close the panel or switch tabs while publishing is in progress.
4. After confirmation, click Calculator Link in the add-on panel. This opens a separate sheet (sometimes called the Links sheet) that lists your published calculators.
5. Find the row whose Sheet Name column matches CLV Calculator.
6. In column C of that row, find the public Link. Open this link in a new browser tab. Do not use the Edit Link in column B — that opens the backend editor, not the live public calculator.
7. If the link does not appear or appears stale, click Get Links in the add-on panel to refresh the Links sheet, then look again.

Test the published calculator

1. On the public calculator page, confirm the six input fields are present and that each shows its default value.
2. Enter the worked example inputs: Average Order Value = 50, Purchase Frequency = 4, Gross Margin = 40, Customer Lifespan = 3, Annual Servicing Cost = 20, Customer Acquisition Cost = 30.
3. Click the Calculate button.
4. Confirm the results match the expected values from the table in the verification section: Simple CLV = 600, Annual Gross Margin Revenue = 80, Total Gross Margin over Lifespan = 240, Total Servicing Cost over Lifespan = 60, Contribution-Based CLV = 150, CLV to CAC Ratio = 5.
5. Next, test the alternative scenario: AOV = 80, Frequency = 2, Margin = 30, Lifespan = 5, Servicing = 25, CAC = 60. Confirm Contribution-Based CLV = 55 and CLV to CAC Ratio = 0.92.
6. Test an edge case: enter 0 for Purchase Frequency. The Simple CLV and Contribution-Based CLV results should display Inputs required rather than 0, confirming the guard conditions work correctly.

Embed or share the calculator

Once testing is complete, copy the public link from column C of the Links sheet and share it directly. If you want to embed the calculator in a webpage, copy the iframe code from column D of the same row and paste it into an HTML block on your site. For guidance on building and sharing additional tools, see the GSheetPress Table feature and the GSheetPress support pages.

Republishing the same calculator tab in future — for example after updating a tooltip or adding a result row — retains the same public link. Visitors do not need a new URL.