Customer Lifetime Value Excel Template

This Excel template helps you to calculate the revenue you receive from a customer over their lifetime.

by Ben Murray
CFO passionate about financial visibility, SaaS Metrics, and SaaS economics.Follow 176

22 reviews1,317 views|Start the discussion!|Bookmark

Description
What is Customer Lifetime Value (CLTV)?

Customer Lifetime Value is the amount of revenue or margin you receive from a customer over their lifetime. It is also known as Lifetime Value (LTV) or CLTV.

How do we calculate Customer Lifetime Value?

CLTV can mean many things, depending on how you calculate it. The formula used to calculate it is the following:

(Annual Recurring Revenue - Average Cost of Service) / (% Churn + WACC - Subscription Increase)

This formula produces the lifetime margin (factors in ACS) of one customer after discounting the time value of money (WACC) and churn but offset by customer subscription growth. Here, think business valuation - you are valuing a customer's cash flows.

What makes a Good CLTV?

Is a good CLTV $5k? $50k? or $100k? Really, it is hard to say as it depends on other factors in your business. In certain organisations, $5k would be a great lifetime value, and a value of $100k could be bad to others. CLTV is most useful when compared to your customer acquisition costs (CAC). However, this manifests itself in the CLTV:CAC ratio.

An Example of Customer Lifetime Value

When compared to your CAC, CLTV is really meaningful. For example, if it costs $10k to acquire one new customer and you have a CLTV of $10k, then you are in trouble. Your CLTV:CAC equals 1. This means that you make no money from this customer at all.

Ben suggests using a CLTV formula that incorporates customer margin. This is because if you use the customer's lifetime value of revenue, you won't have considered the costs to support that customer during their time with you. David Skok, an expert in this field suggests a 3:1 ratio on CLTV/CAC. Ben views the CLTV/CAC as the return on your customer acquisition investment.

If you are new to CAC, it is simply the sales and marketing expenses a business spends on new customer acquisition, divided by the number of new customer acquired.

CLTV Formula Inputs

In order to calculate CLTV, you will need your ARPA (average recurring revenue per account), ACS (average cost of service per account), WACC (weighted average cost of capital), dollar churn percentage, and the average dollar percentage growth per customer. This is the version Ben uses, but there are other versions out there you can use.

This template included sensitivity tables to understand how changes in two variables affect the customer lifetime value formula.

Things to Consider

This is a point in time calculation, which means next month your numbers will be different based on the performance of your business. Cohort analysis comes to play here. This month, your CLTV could be $20K and your CLTV/CAC ratio is 1.5, and next month it could better or worse. Let’s hope for better. Your WACC has most likely remained constant, but your churn rates improved and your account executives grew the existing customer base at a better rate, meaning your metrics will improve.

Conclusion

Indeed, there are several SaaS metrics available, but CLTV and CAC are metrics Ben would be measuring to understand the unit economics and the health of your SaaS business. You do not want to spend more time acquiring a customer than the total return on that customer.

This Best Practice includes
1 Excel File