Customer Lifetime Value in Excel or Google Sheets: Formulas From Your Sales List

Check your result in the free calculatorDownload the CLV spreadsheet formula card (PDF)

What you need before you start

One list: every sale, one per row, with three columns. A is the date, B the customer, C the amount. Most invoicing, booking and card systems export something close to this as a CSV. Two things matter more than anything else in the sheet:

• The customer column must be consistent. "J. Smith", "John Smith" and "john smith" are three customers to a formula. Use an ID or email address if your export has one; otherwise tidy the names first.
• Two full years of sales. One year gives you the average sale and purchases per customer. Measuring churn needs two back-to-back years, so you can see who came back.

The sheet works out the same four inputs as the customer lifetime value formula: average sale, purchases per year, years a customer stays and gross margin. The first three come from the list; the margin you type in.

Step by step: the summary formulas

Name the first tab Sales and paste the export there, headers in row 1. Make a second tab called Summary.

1. Set the two periods

In Summary!B1 type the first day of period 1, for example 1 October 2024. In B2 enter =EDATE(B1,12) and in B3 =EDATE(B1,24). Period 2 is the most recent full year; period 1 is the year before it. EDATE works the same in Excel and Google Sheets.

2. Revenue and number of sales in period 2

B5: =SUMIFS(Sales!C:C,Sales!A:A,">="&B2,Sales!A:A,"<"&B3)
B6: =COUNTIFS(Sales!A:A,">="&B2,Sales!A:A,"<"&B3)

SUMIFS adds the amounts whose date falls in the window; COUNTIFS counts those rows. Both are standard in Excel and Google Sheets with the same syntax.

3. Average sale and purchases per customer

B8 (average sale): =B5/B6. B7 (customers in period 2) comes from the per-customer table below: =COUNTIF(Customers!E:E,TRUE). Then B9 (purchases per customer per year): =B6/B7.

Step by step: the per-customer table and churn

Make a third tab called Customers. This table gives one row per customer, and it is what lets the sheet measure churn instead of asking you to guess it.

4. List each customer once

In A2, Excel 365: =UNIQUE(FILTER(Sales!B2:B5000,Sales!B2:B5000<>"")). Google Sheets: =UNIQUE(FILTER(Sales!B2:B,Sales!B2:B<>"")). The list fills down by itself. Microsoft documents UNIQUE for Excel 365 and Excel 2021 onwards; on older Excel, copy column B to the new tab and use Data, Remove Duplicates instead. If you only need the number of customers in Sheets, COUNTUNIQUE gives it in one cell.

5. Add the columns

• B2, lifetime revenue: =SUMIFS(Sales!C:C,Sales!B:B,A2)
• C2, purchases: =COUNTIFS(Sales!B:B,A2)
• D2, bought in period 1: =COUNTIFS(Sales!B:B,A2,Sales!A:A,">="&Summary!$B$1,Sales!A:A,"<"&Summary!$B$2)>0
• E2, bought in period 2: the same with $B$2 and $B$3.

Copy B2:E2 down to the last customer. D and E show TRUE or FALSE.

6. Retention, churn and lifespan

Back in Summary: B10 (retention) =COUNTIFS(Customers!D:D,TRUE,Customers!E:E,TRUE)/COUNTIF(Customers!D:D,TRUE), which is customers who bought in both periods divided by customers who bought in period 1. B11 (churn) =1-B10. B12 (lifespan in years) =1/B11. Customers who first bought in period 2 are left out on purpose; why that matters is covered in our guide to lifespan from churn.

7. Margin and the result

Type your gross margin in B13 as a percentage. B14: =B8*B9*B12*B13. That is your customer lifetime value in gross profit.

Errors you will probably hit, and the fix

• #DIV/0! in retention or lifespan. Either no customer bought in period 1 (check the date in B1), or every customer came back, so churn is 0 and 1 ÷ 0 has no answer. A churn of 0 means your data covers too short a time; use a longer period or a cautious lifespan you can defend.
• Revenue of zero. The dates in the Sales tab are text, not dates, so the comparisons find nothing. Exports often do this. Select column A and convert it to a date format, or re-import the CSV with the date column set as a date.
• Too many customers. The same person appears under two spellings or with a trailing space. In a spare column, =TRIM(LOWER(B2)) shows the cleaned name; sort by it to find the duplicates, then fix the source data.
• The UNIQUE list stops short or shows #SPILL!. In Excel, something is typed in the cells below A2 on the Customers tab; clear them so the list has room to fill.
• A churn rate that looks far too good. Period 1 is too recent, so most customers have not had time to miss a visit. Both periods must be complete, full years.

Once the sheet works, keep it. Next year, change only B1 and paste the new export over the Sales tab; every other number updates.

Check it, then use the table for more than one number

Spreadsheets fail quietly: one range that stops at row 500, one customer spelled two ways. Before you rely on the result, type the four inputs from B8, B9, B12 and B13 into the free CLV calculator. If its customer lifetime value matches B14, the arithmetic is right; if not, one of the ranges is wrong. Then sort the Customers tab by column B. It is common for a small group of customers to account for a large share of revenue, and knowing who they are is useful long after the average is forgotten.

The sheet goes stale the day you export it. Fynl's paid plans keep each customer's jobs, quotes, invoices and payments on one record, so the history behind these formulas is always current instead of rebuilt from a CSV every quarter.

The CLV calculator sits with the NPS, missed-call and no-show calculators on our free tools page.

Questions people also ask

Sources

• Microsoft Support — SUMIFS function
• Microsoft Support — UNIQUE function
• Google Docs Editors Help — COUNTIFS
• Google Docs Editors Help — COUNTUNIQUE