How we developed a payback calculator that won us a €69m Series C
During my time with my current employer, a German Insurtech startup, one of the most interesting projects is to develop a payback calculator which served as the backbone figure that helped us to close a recent €69m Series C funding round. 🥳
The payback period, the output from this payback calculator, answers the question, “If I now invest one euro into your business, how long does it take for me to earn that one euro back?”. Needless to say, it is one of the most important metrics we report to our board every month. Meanwhile, the payback calculator is also a powerful tool we use for internal steering. It helped the executives to decide on priority, plan the annual budget as well as our product team to evaluate and prioritise different features.
With this post, I would like to share how we develop this payback calculator, how we make it more than just one boring spreadsheet and what I believe we did well and what we could do better. At the end of the post, I also share a payback calculator template with some fake numbers to play around with.
This post will cover the following four parts:
- The one metric that matters
- Build the curve
- Go beyond a boring sheet
- Food for thought
The one metric that matters
While we were working towards closing our Series B funding, we noticed that different investors asked for different top metrics to assess our unit economics. The top3 most frequently requested ones are Customer Lifetime Value (CLV), Payback Period and Internal Rate of Return (IRR).
The three metrics actually stem from one single curve, that is, the cumulative cash flow per customer throughout his or her lifetime. They look at the curve from different angles: CLV measures the ultimate height of the curve, payback reflects the slope of the curve and IRR tells us the overall profitability.
In the end, we decided to choose the payback months as “the one metric that matters” to monitor and optimise for the next months or years before the Series C round.
The reasons are pretty straightforward. The payback period is a simple indicator that reflects the momentum in so many aspects of our business: user acquisition, monetisation, retention and operational efficiency, etc. On the other hand, IRR is a very complex concept for anyone who is not familiar with Finance & Accounting, but we want to have a KPI that everyone in the team can easily understand so that we feel motivated and engaged. CLV is not favourable for it is easy to manipulate by changing the assumption about customer lifetime — one can easily change the lifetime from 5 years to 10 years and end up with a higher CLV.
The goal was clear to me — we need to develop a simple, consistent and convincing logic that can best summarise our cohort development into the three metrics, and a tool that can ensure we are not only reporting it but also using it for our business steering.
Challenge accepted! ✅
Build the curve
As mentioned above, the key to estimating the payback period is to plot the cumulative cash flow throughout the lifetime of a customer, which, obviously is highly dependent on your business model. What’s even “worse”, as a startup, your business model can also evolve vibrantly over time — which is exactly our case. 😉
Nevertheless, I would argue that a payback modelling can always be broken down into the following 4 components, regardless of which business model you have:
- Cost per Acquisition, calculated as the sum of marketing spend divided by the number of customers acquired. This is an upfront and one-off cost that occurs only at Year 0, i.e., the initial investment, and determines the starting point of the cumulative cash flow.
- Revenue, the incoming cash flow per each new customer. This can be either up front or recurring depending on the exact business model. Revenue plays a major role in determining the slope and height of the cumulative cash flow curve and can best reflect a business’ ability to monetise and retain its customers. The most tricky part I found in building the payback calculator is to estimate the future revenue, as it is quite challenging for a startup business that is usually younger than five years to have sufficient data for revenue forecasting. At the end of this section, I will share a quick heuristic approach that we have used in tackling this problem.
- Variable Costs. This is usually modelled as a fraction of the revenue. Some typical scenarios are: you need to pay a certain share of the gross sales revenue as a sales bonus to the sales agents who close the deals, some customers cancel the paid subscriptions within 30 days, or when we onboard a revenue-share partnership and need to pay to the partner X% of the gross revenue we earn from customers with this partnership.
- Fixed Costs. This is usually modelled as a recurring, fixed amount of costs per customer per month. One can put into this bucket the cost of customer service, HR, Finance, Product & Tech, etc. Once you have decided what components to be considered as fixed costs, a quick way to get a rough estimate is to look at the monthly P&L, take the sum of all fixed costs in a particular month and divide it by the total number of customers your business serves in that month (I would take the average of the total number of customers at the beginning of the month and that at the end of the month).
Now, as mentioned before, the most tricky part is to estimate the future revenue. One heuristic approach we tried is:
- Derive from historical data the average cumulative revenue per customer by the end of each period (30 days in our case) of his or her lifetime. This should already give a brief picture of the shape of the curve.
- If necessary, forecast the cumulative revenue in years to come using simple methods, e.g., logarithmic/linear/polynomial functions.
- Once we have the baseline curve, we assume every single new cohort will perform 100% as well as the baseline. Once a cohort is 1-period old (i.e., everyone from that cohort has been with the business for at least 30 days), say cumulatively we have actually earned €25 from every single customer in this cohort and the target value per the baseline curve is €20, we calculate an up(down)lift of 25% (25/20*100%-1) and apply this ratio to the whole cumulative revenue curve, i.e., we assume customers from this cohort will always generate 25% more revenue.
- When this cohort gets 2-period old, we learnt something new about this cohort: each customer from this cohort generated €5 during their 2nd period (and thus cumulatively €30) while per the baseline they should achieve €32 cumulatively. We now update our expectation about this cohort and assume customers from this cohort will always generate 6% less revenue than planned. Hence, assuming other things equal, the payback period of this cohort should get longer than what we see when we only knew about its first period.
- We repeat the 4th step over time for each cohort so that we can always updating our expectation about cohort revenue performance using our latest knowledge about the cohort.
This method is very simple to implement in a spreadsheet and later on move to a SQL function if you want to automate the reporting. With this method, we end up with a cohort payback period report like this:
The chart shows that the newer cohorts have a significantly shorter payback period than older ones (15 as the average of all 2019 cohorts and 10 for all 2020 cohorts). Our unit economics has been constantly improving! 😃
Go beyond a boring sheet
What I am really proud of about our approach is that we went beyond a boring 10MB spreadsheet and added two key functionalities that turn the payback calculator from a pure reporting sheet into an interactive steering tool that enables us to make fast decisions.
One thing we did is to add a playground tab to our spreadsheet for high-level modelling. By playing around with different params, users can quickly see the changes resulting from their (sometimes wild) assumptions and find answers to questions like “What would happen if we go all-out on bringing down our CPA from €100 to €50?” or “To what extent can we afford an increase in the fixed costs?”.
This tab also helps bring more transparency into the payback reporting. Since we shared this payback calculator (a google sheet) with our investors and let them check-in at any time the latest figures, they can also play around with this tab if they want to have a better understanding of the logic or validate their hypotheses.
Moreover, I also created a mini case builder based on the payback calculator. This enables our team to test their assumptions and quickly find out the impacts of their roadmap features on the top KPIs. For instance, if the monetisation team plans to improve the lead funnel conversion rate from 40% (the baseline conversion rate derived from the last-12-month trend) to 45% with a new feature, they can simply enter their assumptions on Column “Estimate” of the tab “Case Assumptions”, accompanied with a short description or a link to the User Story in Column “Initiative” to explain how the initiative works. Note that Column “Baseline” is reserved for baseline, usually based on up-to-date figures or L12M average. Whenever possible, each cell in this column should be linked to an automatic dashboard on your BI tool, so that users can easily find the latest trends.
Then one can navigate back to tab “Case Dashboard” and notice that compared with the baseline model (the blue curve), the new model with this roadmap item will result in an improvement of payback month from 21 months to 13 months.
The team can even conduct more complex scenario modelling by changing multiple parameters at once. When used properly, this tool becomes a powerful benchmarking tool to assess the impacts of different initiatives across departments and saves us time on building a huge business case every time.
Food for thought
During the last months at my current employer, we moved our payback reporting from the Google sheet to an automatic dashboard on our BI tool. We still keep the sheet calculator though and use it for scenario modelling and steering.
Looking back, I believe there are several things we have done really well:
- We started with a simple heuristic approach while maintaining a dynamic view. With this approach, we managed to constantly update the modelling logic to adapt to the changes in our business model, e.g., add more components, increase the metric granularity, etc.
- We offered the maximum transparency into our logic and gave our investors a holistic view of our cohort development more than just a single payback number. This transparency, combined with the simplicity of the logic itself, helped us to win the trust of our existing and new investors.
- We went beyond a simple reporting spreadsheet and embedded the logic into our business steering to achieve consistency between monitoring and steering.
On the other hand, one key improvement we can do in the future is to switch to a more dynamic, real-time (or near real-time) logic using data science technique, e.g., to predict individual customer quality. This would be bringing the payback metric up to a higher granularity and facilitate operational steering.
Here I attach a payback calculator template with some fake numbers to play around with https://docs.google.com/spreadsheets/d/1BHxXkzdLkCizL3Aze8WSnokAGgVPE0jlZhO8xIefjeA/edit#gid=231624412
Any feedback is welcome! :)