This Excel spreadsheet was built to let anyone plug in some high level assumptions and see the resulting customer lifetime value. It is designed to be a metric that can be tracked from month-to-month or whatever period you are entering data for.
Example Sheet: https://docs.google.com/spreadsheets/d/1Nbt-ZGfiCl6QeihA7DZg8HlLKzE-byig/edit#gid=1228582505
Check out advanced SaaS financial models here: https://www.smarthelping.com/p/as-service-business-models.html
The calculation is fairly simple, here it is:
Total MRR or Revenue Earned in the Period
less Cost of Goods Sold
equals Gross Profit
then, define a retention rate (the spreadsheet does the calculation for you) but in general a monthly retention rate of 95% means an average customer life of 20 months (1/5%)
Divide the total Gross Profit by the period ending customers in the period that the gross profit was earned from. Typically this is just going to be a monthly figure.
Customer Lifetime Value (cLTV) = (Average Gross Profit per Customer) * (Total Periods a Customer Exists for on Average)
Note, this calculation breaks down the customer revenue a bit more than just face value and looks at the actual gross profit of each customer when figuring out their value over the average life they are a customer for.
You can see how much the retention rate may impact lifetime value and if you wanted to get fancy you can turn this into a sensitivity analysis that isolated the retention rate variable and holds all other variables the same in order to see various LTV outputs per various retention rates. Have fun making a table!
The above template is accessible via Google Sheets and you can easily download it to Excel if you wanted by going to File and then choose Download and select 'Microsoft Excel'
Also, for your own editable version of the spreadsheet, just hit File and Make a Copy.
You may also enjoy this SaaS pricing and margin analyzer model and this Customer Cohort Modeling (historical data analysis)
