SaaS KPI Modeling - Customer Lifetime Value and CaC Payback

These are one of the two most difficult metrics in the SaaS planning and business modeling world. The excel template I have built here is a great tool for organizations to see how valuable their current customers are or how not valuable as well as get a more clear picture on their customer acquisition cost structures / MRR pricing and how it all inter-relates. Also, check out this SaaS Rolling Revenue Forecast.


The template will be sent to you after purchase via your PayPal/purchasing e-mail. This is included in the SaaS financial model template bundle.

This has been updated to add LTV to CaC ratio as well as a 60-month actual data tracker and monthly granularity to the Customer Lifetime Value.

The simple goal of this SaaS business planner is to let the user enter in some pretty straight forward metrics and from those high-level inputs they can have auto calculations give them the more complex financial ratios / values that explain to them what their position really looks like and if it is good/bad or otherwise.

The excel sheet contains two tabs:
  • Tab 1: Focuses on CaC payback (how many months it will take the gross profit of your recurring revenue to pay back the cost it took to add that revenue initially). That means on-going costs and acquisition costs are being taken into account. You will enter the monthly marketing and sales staff expenses in order to start this metric. Additionally, one-time revenue for those newly acquired users is also being taken into account. Usually you want this number to at the very worst be less than 8 months.
  • Tab 2: Now we get into customer lifetime value. Because value has many definitions, there are two calculations being done here. The first is average annual gross revenue and the second is discounted annual gross revenue. Based on what you enter for a retention rate and discount rate, the values will populate accordingly.
*Update - Note that in the video I show the one-time revenue in the LTV tab as happening in year 0 and including revenue from all users. Also, you can see I did not add it into the LTV of the calculations. This has been updated and changed to a per user basis to match the annual recurring revenue calculations and adds into the LTV box cell F2.

Check out full 5-year SaaS excel templates: SaaS and 'saas-like' templates

Screenshots of Template:
CaC and payback calculator

LTV Calculator

SaaS KPI tracker

saas visualizations

SaaS CaC visual

saas ltv cohort analysis

present value of ltv cohort analysis