Free Excel templates

A free LTV and CAC unit economics model in Excel: two companies, the same growth

Revenue split into retained and bought, the value of one customer by horizon, the acquisition cost at which growth starts destroying value, and the test of cutting marketing to zero: six sheets, no macros.

The file compares two fictional companies that both double revenue every year with the same 80% gross margin and the same cost to acquire a unit of revenue. One keeps and expands its customers (net revenue retention of 115%), the other loses them (70%). Over an eight-year horizon one customer is worth 10.98 units at the first and 2.51 at the second, so the acquisition cost at which growth stops creating value is four times higher. Cut marketing to zero and, four years later, the first company has grown 75% while the second has lost 76% of its revenue.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

LTV_CAC_Unit_Economics.xlsx (17 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is one worked case from The Venture Capital Associate. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 10 paperbacks by Julian R. Sterling on real estate and housing, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Six sheets. Inputs are on the first model sheet; everything else is a formula. Units of revenue, no currency.

0. Read Me
What the file finds and why a customer's value has no answer without a horizon.
1. The two companies
Net revenue retention, growth, gross margin, acquisition cost and horizon, and revenue year by year split into retained and bought.
2. What a customer is worth
Value of one customer at horizons from 3 to 20 years, and the limit as the horizon grows.
3. The cost that reverses
The acquisition cost at which growth stops adding value, the margin for error and the cumulative accounts at a common cost.
4. The cut test and 5. Checks
Acquisition set to zero for four years, and the checks against the book.

What the worked case returns

MeasureValue
Net revenue retention115% against 70%
Gross margin, both80%
Customer value at 8 years10.98 against 2.51
Customer value at 20 years81.96 against 2.66
Limit as the horizon growsdiverges against 2.67
Break-even acquisition cost, ratio4.37x
Revenue after a four-year marketing cut1.75x against 0.24x

With net revenue retention above 100% the sum of a customer's future margin has no limit, so any LTV figure is really a choice of horizon. Below 100% it converges. That is why an LTV to CAC ratio quoted without its horizon cannot be compared across companies.

How to use it on your own company

  1. Enter your net revenue retention, gross margin and acquisition cost per unit of new revenue on sheet 1.
  2. Choose the horizon you are prepared to defend and read the customer's value on sheet 2.
  3. Compare your actual acquisition cost with the break-even cost on sheet 3.
  4. Run the cut test on sheet 4 before believing a growth rate.

Questions people ask about it

Is this template really free?

Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Does it use macros or circular references?

No macros and no circular reference: geometric sums over the chosen horizon.

How is customer lifetime value calculated here?

As the gross margin a unit of revenue earns over the horizon when it grows or shrinks each year at the net revenue retention rate: a geometric sum. Above 100% retention it grows without limit, which is why the horizon must be named.

Can I use it for my own company?

Yes, by replacing the inputs. The two companies are fictional.

The rest of the files

This template is one of the companion workbooks for The Venture Capital Associate: the other works through reserve arithmetic, and the page also carries forty self-marking questions. Free, like this one.

Open the companion files →

Other free templates

This template comes from The Venture Capital Associate. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.