You just can't calculate LTV in a spreadsheet

Why you need to use simulations to model lifetime value
You just can't calculate LTV in a spreadsheet

Lifetime value (or LTV) is a measure of how much profit you make from a customer over their lifetime.

LTV is arguably the most important financial metric for a wide range of businesses. If your business uses any sort of subscription model, or relies on repeat customer purchases, then it's critical for you to know what your LTV is.

Knowing your customer LTV lets you know how hard you can push on marketing, how much you can afford to spend on retention strategies, as well as enabling you to build accurate financial models for your business.

For all its usefulness, LTV is a surprisingly hard metric to calculate accurately. To see why, lets look at how people typically calculate LTV, and why those methods fail.

How most people calculate LTV

The typical way to calculate LTV is to use a formula like:

It's a quick and easy formula to remember, but it relies on one crucial assumption; that the chance of a user churning is independent of how far into their lifetime they are.

Put another way, for the formula above to work, the chance of a user churning in month 1 has to be the same as the chance of them churning in month 50, and so on.

While this assumption might not seem totally unreasonable at first glance, it is in fact deeply flawed. In reality, no business on earth will have a completely flat churn rate over their customers' lifecycle.

This isn't to say that all businesses will have the same non-flat churn rate; churn rates will vary hugely depending on the business model. To give just one example, let's consider what the churn rate of a mobile network provider might look like.

Churn rates aren't flat

In the first 3 months after a user joins a mobile network, their chance to churn may well be higher than average.

If they join the service only to find that the network's coverage isn't good, or that the cost is working out higher than expected, then they're likely to leave straightaway. This means that the network has a high initial churn rate.

From month 3, the monthly churn rate could drop significantly. If there were any reasons for new customers to churn, they likely would've already found them in the first 3 months. If the customer hasn't churned by month 3, then there's a good chance they'll make it to month 12.

But let's say that the contract changes after 12 months. Perhaps an introductory offer expires, or the price increases. This could lead to a spike in churn rate, as suddenly customers have a new reason to leave. This might leave us with a churn rate which looks something like this.

Dealing with this complexity

So, here's the question - how are you supposed to deal with a churn rate like this if you want to calculate an LTV?

You can't simply take an average of the churn rate; this ignores aspects like the high churn rate in months 1 to 3, which immediately cuts your cohort size down. Using an average churn rate doesn't capture this information, and essentially leads you to calculate an incorrect LTV.

The truth is, there's no easy way to model an LTV in this situation, at least not with traditional methods. The best way to cope with this complexity is to turn to simulations.

A simulation-based approach

Even if you're not familiar with using simulations, the idea of using them to calculate LTV is fairly straightforward.

To start, let's consider an imaginary customer of our mobile network, who we'll call Ana. What we're going to do is to try to work out how much profit we make from having her as a customer.

Let's begin by saying that we make $30 worth of revenue off her each month, the cost of servicing her as a customer is $5 a month, and that our customer churn rate is:

  • 5% in months 1 to 3
  • 2% in months 4 to 12

(we'll just look at the first 12 months for now, to keep things simple)

Now, we know we make $25 ($30 minus the $5 costs) off of her in month 1. Based on the 5% churn rate, she has a 95% chance of making it into month 2. We can account for this by choosing a random number between 1 and 100, and saying:

  • If the number is between 96 and 100, then Ana churns in month 1, and doesn't make it into month 2. Her total lifetime value is $25.
  • If the number is between 1 and 95, then Ana makes it into month 2, and her lifetime value increases to $50.

Essentially we keep iterating through each month and proceeding exactly as described above. We choose a random number and, if it falls in some range determined by her churn rate that month, we stop her from moving to the next month and her lifetime value is finalised.

If it falls outside of that range, we add another $25 to her lifetime value and move to the next month.

All good things come to an end, and eventually Ana will churn. At that point we sum up all the profit that we were able to make from having her as a customer (which will be $25 multiplied by the number of months she was a customer for) and we have her lifetime value (not counting any customer acquisition costs).

How does this help us?

Ana is just one customer, and depending on the random numbers generated, she might have remained a customer for 1 month or 100 months. Clearly we're not going to base our LTV on just this one fictitious example.

To solve this, we're going to create more examples. We're going to create thousands more Ana's. Each time we do, we're going to make a note of what their individual lifetime value was.

Once we've done this thousands of times over, we'll have enough data points to build a distribution. It might look something like the below.

What we've achieved here is twofold:

  • From our distribution we can calculate an average LTV, and know that this doesn't rely on any shaky assumptions about us having a flat churn rate.
  • The very fact we can see our LTV distribution is helpful, because it helps us understand how broadly or narrowly spread our individual customer LTVs are.

How do you do this in practice

The title of this article is a little bit inaccurate. Truth be told, you can do the above in a spreadsheet; it's just really difficult.

The way you'd do it is to build a sheet with one row for every simulation of Ana that you wanted to run. Each column would represent a month in her potential customer lifetime.

You'd then write a formula in each cell which used something like RAND() to generate a random number to simulate whether she continued being a customer that month. You'd combine this with your margin to work out the profit you'd made from her that month, in that simulation.

What your spreadsheet would be doing is effectively running thousands of simulations for Ana, which you could then aggregate to build an LTV distribution.

This is a little bit tedious though; spreadsheets just aren't built for running simulations.

Running simulations in Causal

Causal is an in-browser modelling tool which runs the simulations for you; you don't even have to think about them.

This makes it simple to build complex LTV models. All you have to do is to create a few variables:

  • Revenue per month per user
  • Margin %
  • Churn rate (broken down by time period)

And Causal does the rest of the work for you.

For a simple example of how this works in practice, check out the model below. You can change any of the variables in the top section and see how they affect the graphs.

Every time you make a change, Causal will run thousands of simulations in the background for you, and aggregate the results automatically.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
PERSONAL FINANCE
Buy vs Rent
Should you buy a house or rent?
Startur
B2B SaaS Revenue
Forecast your inbound and outbound leads to determine revenue, and understand what kind of sales funnel you need to hit your revenue targets.
FINANCE
Detailed Headcount Model
Understand the breakdown of your headcount and payroll costs by Department (Sales, Engineering, etc.) and plan your future hires.

You just can't calculate LTV in a spreadsheet

Feb 28, 2021
By 
Mack Grenfell
Table of Contents
Heading 2
Heading 3

Lifetime value (or LTV) is a measure of how much profit you make from a customer over their lifetime.

LTV is arguably the most important financial metric for a wide range of businesses. If your business uses any sort of subscription model, or relies on repeat customer purchases, then it's critical for you to know what your LTV is.

Knowing your customer LTV lets you know how hard you can push on marketing, how much you can afford to spend on retention strategies, as well as enabling you to build accurate financial models for your business.

For all its usefulness, LTV is a surprisingly hard metric to calculate accurately. To see why, lets look at how people typically calculate LTV, and why those methods fail.

How most people calculate LTV

The typical way to calculate LTV is to use a formula like:

It's a quick and easy formula to remember, but it relies on one crucial assumption; that the chance of a user churning is independent of how far into their lifetime they are.

Put another way, for the formula above to work, the chance of a user churning in month 1 has to be the same as the chance of them churning in month 50, and so on.

While this assumption might not seem totally unreasonable at first glance, it is in fact deeply flawed. In reality, no business on earth will have a completely flat churn rate over their customers' lifecycle.

This isn't to say that all businesses will have the same non-flat churn rate; churn rates will vary hugely depending on the business model. To give just one example, let's consider what the churn rate of a mobile network provider might look like.

Churn rates aren't flat

In the first 3 months after a user joins a mobile network, their chance to churn may well be higher than average.

If they join the service only to find that the network's coverage isn't good, or that the cost is working out higher than expected, then they're likely to leave straightaway. This means that the network has a high initial churn rate.

From month 3, the monthly churn rate could drop significantly. If there were any reasons for new customers to churn, they likely would've already found them in the first 3 months. If the customer hasn't churned by month 3, then there's a good chance they'll make it to month 12.

But let's say that the contract changes after 12 months. Perhaps an introductory offer expires, or the price increases. This could lead to a spike in churn rate, as suddenly customers have a new reason to leave. This might leave us with a churn rate which looks something like this.

Dealing with this complexity

So, here's the question - how are you supposed to deal with a churn rate like this if you want to calculate an LTV?

You can't simply take an average of the churn rate; this ignores aspects like the high churn rate in months 1 to 3, which immediately cuts your cohort size down. Using an average churn rate doesn't capture this information, and essentially leads you to calculate an incorrect LTV.

The truth is, there's no easy way to model an LTV in this situation, at least not with traditional methods. The best way to cope with this complexity is to turn to simulations.

A simulation-based approach

Even if you're not familiar with using simulations, the idea of using them to calculate LTV is fairly straightforward.

To start, let's consider an imaginary customer of our mobile network, who we'll call Ana. What we're going to do is to try to work out how much profit we make from having her as a customer.

Let's begin by saying that we make $30 worth of revenue off her each month, the cost of servicing her as a customer is $5 a month, and that our customer churn rate is:

  • 5% in months 1 to 3
  • 2% in months 4 to 12

(we'll just look at the first 12 months for now, to keep things simple)

Now, we know we make $25 ($30 minus the $5 costs) off of her in month 1. Based on the 5% churn rate, she has a 95% chance of making it into month 2. We can account for this by choosing a random number between 1 and 100, and saying:

  • If the number is between 96 and 100, then Ana churns in month 1, and doesn't make it into month 2. Her total lifetime value is $25.
  • If the number is between 1 and 95, then Ana makes it into month 2, and her lifetime value increases to $50.

Essentially we keep iterating through each month and proceeding exactly as described above. We choose a random number and, if it falls in some range determined by her churn rate that month, we stop her from moving to the next month and her lifetime value is finalised.

If it falls outside of that range, we add another $25 to her lifetime value and move to the next month.

All good things come to an end, and eventually Ana will churn. At that point we sum up all the profit that we were able to make from having her as a customer (which will be $25 multiplied by the number of months she was a customer for) and we have her lifetime value (not counting any customer acquisition costs).

How does this help us?

Ana is just one customer, and depending on the random numbers generated, she might have remained a customer for 1 month or 100 months. Clearly we're not going to base our LTV on just this one fictitious example.

To solve this, we're going to create more examples. We're going to create thousands more Ana's. Each time we do, we're going to make a note of what their individual lifetime value was.

Once we've done this thousands of times over, we'll have enough data points to build a distribution. It might look something like the below.

What we've achieved here is twofold:

  • From our distribution we can calculate an average LTV, and know that this doesn't rely on any shaky assumptions about us having a flat churn rate.
  • The very fact we can see our LTV distribution is helpful, because it helps us understand how broadly or narrowly spread our individual customer LTVs are.

How do you do this in practice

The title of this article is a little bit inaccurate. Truth be told, you can do the above in a spreadsheet; it's just really difficult.

The way you'd do it is to build a sheet with one row for every simulation of Ana that you wanted to run. Each column would represent a month in her potential customer lifetime.

You'd then write a formula in each cell which used something like RAND() to generate a random number to simulate whether she continued being a customer that month. You'd combine this with your margin to work out the profit you'd made from her that month, in that simulation.

What your spreadsheet would be doing is effectively running thousands of simulations for Ana, which you could then aggregate to build an LTV distribution.

This is a little bit tedious though; spreadsheets just aren't built for running simulations.

Running simulations in Causal

Causal is an in-browser modelling tool which runs the simulations for you; you don't even have to think about them.

This makes it simple to build complex LTV models. All you have to do is to create a few variables:

  • Revenue per month per user
  • Margin %
  • Churn rate (broken down by time period)

And Causal does the rest of the work for you.

For a simple example of how this works in practice, check out the model below. You can change any of the variables in the top section and see how they affect the graphs.

Every time you make a change, Causal will run thousands of simulations in the background for you, and aggregate the results automatically.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.