Lifetime value spreadsheet

Why use Lifetime value models?

Lifetime value models have many applications, but they are most powerful as a tool to help businesses prove the future value they can generate from marketing campaign activities.

It’s much better evaluating investment based on long-term returns from marketing activities rather than the short-term success of a single sale.

How will the template help me and my business?

Lifetime value analysis will help you plan and measure investment levels for customer acquisition programmes. As you’ll know, every new campaign should have a clear target cost for allowable customer acquisition (CPA). If you’re making the business case to your boss or colleagues in finance or investors they help give you project numbers to back up your arguments for more investment.

LTV models are important for many different times of businesses like retail, utilities, travel or web services like software services or publishing where there will be recurring revenue. They are also important to creating business plans and forecasts for startup businesses to help estimate future revenue.

How is the template structured?

Read through the ‘Read Me’ tab first. Then go to the ‘Lifetime Value Model’ tab, edit the table with data for your business to calculate LTV. When you input your data into the blue cells, the orange cells will automatically update.

The rows in the spreadsheet model are:

1. Customers – this is the number of initial customers. It declines each year dependent on your retention rate.
2. Margin – this is the profit (gross margin) contributed by this group of customers based on the typical type of product they purchase.
3. Years – lifetime value shows the revenue and cost for each year, although it can be used for other time periods, e.g., a monthly subscription.
4. Customers – this is the customers in each year dependent on the retention rate.
5. Retention rate – the proportion of customers who stay with the company over time. In lifetime value modelling, this is usually found to increase year-on-year, since customers who stay loyal are more likely to remain loyal.
6. Average revenue per annum – likewise, typical spend increases through time.
7. Total revenue
8. Net profit (at 20% margin) – LTV modelling is based on profit contributed by this group of customers.
9. Discount rate – since the value of money held at a point in time will decrease due to inflation, a discount rate factor is applied to calculate the value of future returns in terms of current day value.
10. NPV contribution – this is the profitability after taking the discount factor into account to give the net present value in future years.
11. Cumulative NPV contribution – this adds the previous year’s NPV for each year.
12. Lifetime value at net present value – this is the figure that is used to set an acceptable cost per acquisition.