LTV Calculation Spreadsheet

In Step 17, Estimate the Lifetime Value (LTV) of an Acquired Customer, you are taking the information from your Business Model (Step 15), Pricing Framework (Step 16), and customer research to estimate how much revenue a new customer brings to the business over some time period, usually 5 years.

Keep in mind that, at this stage, the LTV calculation is an estimate based on your work to date. The goal of the calculation is to obtain an estimated range for your LTV, which you will then combine with your COCA (Cost of Customer Acquisition) estimate to determine if the business looks viable at this point. You may need to loop back to your Business Model or Pricing Framework or talk to customers if you are not satisfied with the LTV.

To make the LTV calculation easier for my students and me, I converted the Step 17 worksheet into a spreadsheet. Using the spreadsheet has several advantages:

• It is faster.
• You can change inputs and quickly see the impact on the LTV.
• It allows you to easily play with the inputs to determine a range for the LTV.
• It allows you to see the key drivers for your LTV.
• You can test different business models and pricing frameworks to determine the impact on LTV.

The downside of using the spreadsheet is that it can give you the impression that there is more accuracy in the LTV calculation than actually exists. While you can have fun with spreadsheets, you have to keep in mind that the calculation is an estimate only and will remain so until you start testing. The only test that counts is paying customers.

Download the LTV spreadsheet (Excel file)

The file sheets:

• LTV template
• Example 1 – periodic purchases with a quarterly subscription box
• Example 2 – monthly recurring subscription
• Example 3 – 1-time sale and monthly recurring
• Cost of Equity Capital – calculates the cost of capital using venture capital
• Churn – shows the impact of churn over time

The LTV template is covered in more detail below. The three example sheets show how the template can be used with different business models. I included the Cost of Equity Capital spreadsheet because it can students often struggle to understand why it is so high. The Churn spreadsheet shows the declining numbers of customers at various rates of churn. It helps students understand the impact when they can look down the column and see the numbers of customers decreasing.

LTV Template

The LTV template spreadsheet has two sections, one for inputs and one for outputs. All you have to do is enter the values that are appropriate for your business and review the outputs. The image below shows the Assumptions section, which is the inputs. You may notice that I have added a Revenue type called Periodic Purchases, which is not included in Disciplined Entrepreneurship or Disciplined Entrepreneurship Workbook. This category covers both online and brick and mortar businesses where the customers may buy multiple times a year, but there is no subscription or recurring charge. Examples may include the bakery down the street, an eCommerce business selling t-shirts, and Amazon.

The outputs or results section can be seen in the image below.

Let’s take a look at how you can use the LTV Template using a common business model.

Recurring Charge Example

A common business model is subscription or recurring charge. For this example assume:

• App costing $20/month
• Gross Margin = 90%
• Retention Rate = 80%
• Cost of Capital = 50%

Here is the Assumptions/Inputs section:

Here is the Output section:

Changing Cost of Capital

Assume you can borrow money at 12% instead of using venture capital. Here is the Output section if the Cost of Capital is 12% instead of 50%. The LTV goes from $236 to $440.

Calculating a Range for LTV

You can quickly use the LTV Template to determine a range for your LTV. Assume your Gross Margin is between 85% and 95% and your retention rate is 75% to 85%, what is the possible range of LTV values? If you plug the lower end of each assumption into the spreadsheet, you get an LTV of $198, and if you use the upper end, you get an LTV of $281 (see image below).

Then you start examining your assumptions and asking yourself questions:

• Is our business viable at the lower end of the LTV range?

• What can we do to impact the key inputs?
• How can we increase the Gross Margin?
• How do we increase the Retention Rate?
• Can we lower our Cost of Capital?

Once you start producing your product or service and selling to customers, you will quickly get answers to many of the questions.

Conclusion

I used a simple example of a monthly recurring charge. However, you can use the same process to explore more complicated examples such as businesses that have a 1-time charge and a monthly recurring charge or businesses with periodic purchases and a quarterly subscription box. Have fun with the spreadsheet, and remember it is an estimate until customers start buying your product or service.