A Spreadsheet for Calculating Subscription Lifetime Value

The glamorous side of entrepreneurship — Lifetime Value spreadsheets

You can follow my latest writing at ScreenshotEssays.com

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. Monthly Revenue: The total amount of money you expect to bring in from a customer each month.
2. Margin: The percent of revenue that is profit.
3. The Cohort Retention Curve: 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 column header in the spreadsheet:

1. Month (column A): The month in the customer’s lifetime. The sheet models out the first 60 months, which is sufficient to project LTV.
2. Cohort Retention (column B): 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. Expected Revenue (column C): 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. Retained from previous month (column D): The marginal retention rate from month to month. For example, the percent of people that are retained between month 2 and month 3.
5. Rate of change (column E): 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 assumptions:

1. Monthly Revenue: The monthly cost of your subscription to consumers.
2. Margin: The percent of revenue that you keep as profit.
3. Month 1 % Retained: The percent of customers who are retained after their first month.
4. Retention Curve Rate of Change Baseline and Retention Curve Rate of Change Additions: 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. Long-term retention: 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.