A Spreadsheet for Calculating Subscription Lifetime Value

Eric Stromberg
Nov 30, 2016 · 4 min read
The glamorous side of entrepreneurship — Lifetime Value spreadsheets

Subscription is a powerful business model because it creates an environment where the default customer behavior is retention, as opposed to one where the default behavior is churn.

However, the underlying concepts that drive subscription businesses can be complex in the early days of a startup. The pillar of any subscription business is Lifetime Value (LTV), or the total profit you can expect to receive from a new customer over the course of their lifetime. LTV, in turn, drives willingness to pay to acquire customers.

When we were scaling our subscription business, I found that there were surprisingly few publicly available resources that explained in a detailed way how to calculate LTV. So, I built a simple spreadsheet that you can use to calculate and project Lifetime Value.

You can follow this link to view the spreadsheet.

Inputs for LTV

To use the model effectively, it’s helpful to understand the inputs of LTV:

  1. The total amount of money you expect to bring in from a customer each month.
  2. : The percent of revenue that is profit.
  3. The percent of customers who sign up in month 1 who will still be around in each successive month. The curve represents the probability that a new customer is active in month [X] of their lifetime. To demonstrate the idea, I’ve included below an example of what projections for an upside case, base case, and downside case Retention Curve might look like for a pre-launch startup:
Example Retention Curve projections

Retention curves generally reach an asymptote over time (meaning the curve will flatten out). Put differently, the older a cohort is, the higher its marginal retention will be. This makes sense —older subscribers retain better than newer ones. Subscribers who see less value in the service drop off earlier in their life, leaving the service with more committed members over time.

With these three inputs in place, you can calculate LTV. Revenue and Margin give you expected profit for a customer that is retained, while the Cohort Retention Curve gives you the probability that the customer will be retained in each month. Taken together, you can project LTV, or the expected total profit from a new customer over their lifetime.

Spreadsheet Definitions

Below are definitions of each in the spreadsheet:

  1. The month in the customer’s lifetime. The sheet models out the first 60 months, which is sufficient to project LTV.
  2. The percent of the original cohort that is still around in each successive month. In other words, the probability that a customer who signs up in month 1 will be around in month X.
  3. Projected revenue in each month, taking into account the probability from Column B. This is an average across the entire cohort of Month 1 signups (including those who cancelled), so the number generally goes down over time.
  4. The marginal retention rate from month to month. For example, the percent of people that are retained between month 2 and month 3.
  5. An input that drives how quickly the % retained from previous month (column D) changes, and ultimately the slope of your Retention Curve.

Additionally, here are definitions for each of the :

  1. The monthly cost of your subscription to consumers.
  2. The percent of revenue that you keep as profit.
  3. The percent of customers who are retained after their first month.
  4. These two assumptions drive the slope of your Retention Curve. The best way to use this is to change them, in connection with the Month 1% Retained assumption, to drive a curve that looks realistic to you. You can eyeball what the curve looks like as you make these changes in the included Year 1 Retention Curve chart.
  5. The marginal rate at which your Retention Curve flattens (i.e. you will probably never have 100% marginal retention).

Using the Spreadsheet

  • First, make a local copy of the file. You can do this by clicking on the tab and clicking “Copy to,” and then copying to your own Google Drive:
  • Numbers in blue are inputs, and numbers in black are outputs. You can change the blue numbers to customize the projections for your business.
  • If you already have some historical retention numbers, that’s even better. You can input your historical retention curve in the months you have, and use that to drive the slope of your retention curve (Column D). Once you understand that slope, you can use it to project the future slope and get more accurate LTV projections.

Hopefully this is helpful in calculating the LTV of your subscription business. If you run into any issues, want to get a sense for what comparable retention curves look like for your product, or just want to talk subscription you can send me a note at ericgstromberg@gmail.com.

If you enjoyed this post, you can follow me on Twitter for updates.

Inflection Points

Searching for changes in direction.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store