Tomasz Tunguz at RedPoint just posted about Revenue at Risk (RaR) which inspired us to share our approach to calculating this important statistic.
Revenue at risk (RaR) is an important number for SaaS services to watch, but the version that many people use is not as informative as it should be – maybe that’s why many companies don’t bother. When you say “risk”, it means you are measuring something bad that could happen, but might or might not – hence risk. What is missing from most RaR calculations is the notion of how much risk we are talking about. Consider the standard calculation for probabilistic revenue at risk:
Monthly RaR = MRR * Churn rate
That RaR number is your expected loss to churn in the next month (and you can annualize it if you want.) But how likely is it that you will lose exactly that amount, and how useful is it to have one number? It is really just your last months churn loss.
At Sparked, we’ve developed a new RaR number that uses a more sophisticated notion borrowed from the finance industry: we add a confidence level for the RaR. This is borrowed directly from the financial concept of Value at Risk (or VaR, see http://en.wikipedia.org/wiki/Value_at_risk.) The idea of the confidence level is that instead of asking what is the loss you are most likely to receive, you ask instead what is the loss you are expected to receive with a certain probability. The most common probability threshold in finance is 95% or 99%, but for RaR we recommend using a range of 75% to 90%, for reasons I will discuss in a minute. What does the 90% confidence level mean? It means that the 90% RaR is the amount of loss (to churn) such that you are 90% likely to lose less than that amount; with 10% probability you will lose that much or more. Or if it is 75% confidence you are 75% likely to lose less than that amount, and 25% likely to lose more.
So here is the power of the approach using a confidence level: you have a clear idea of how likely you are to suffer the loss, rather than just saying this is what you are “expected” to lose. Because it could be that you are most likely to lose $50,000, but it may be only a bit less likely that you will lose $100,000. How do you know? With a confidence level, you can map out different scenarios and attach a clear likelihood: e.g.. you may determine that with 75% probability you will lose less than $50,000 (1 in 4 chance of losing more than this much) and with 90% probability you will lose less than $100,000 (1 in 10 chance of losing more than $100,000.)
Okay, that’s a nice concept – but how do you calculate it? The math is a bit more complicated than the simple most-likely RaR, but it can easily be done in a spreadsheet as long as you assume an equal churn probability and MRR on all your subscribers – you just have to use a binomial probability calculation, and a little interpolation. Check out the example below, where we assume 1000 subscribers, 1% churn and $20 MRR. The simple most likely loss is 1000*1%*$20=$200. But how likely is that, and what about other possible losses?
It all comes to the cumulative binomial probabilities – here’s how it works:
- Column A shows a possible # of people who churn
- Column B is the loss associated with that level of churn.
- Column C is where it gets interesting: that’s the probability that exactly that many people churn. The excel formula for cell C6 is =BINOM.DIST(A6,$B$1,$B$2,FALSE). Note that the most likely number of people to churn is 10: 12.57% probability.
- Column E is the cumulative binomial distribution: the probability that at most that many people churn. The formula for cell E7 is almost the same: just change the “cumulative” flag to TRUE: =BINOM.DIST(A6,$B$1,$B$2,TRUE) So this is the probability that the loss is less than or equal to the given amount – that’s exactly what me mean by the RaR confidence level.
So how do you find the loss at the 50% confidence level? That’s the loss you would receive with at least 50% likelihood. But there’s no 50% in column D because churn events are discrete – there’s no exact number of churns which you are 50% likely to have less than, but we can interpolate between the nearest numbers: that’s 9 or fewer churners with 45.7% probability and 10 or fewers churners with 58.3% probability. The 50% RaR turns out to be $187 dollars – the formula to calculate it in cell C26 is =(A27-D14)/(D15-D14)*(B15-B14)+B14. So that’s a bit less than the most likely churn of $200. And now we can now see that the $200, most likely, number is associated with 58.3% confidence: that is, the service is 58.% likely to lose at most $200, and 41.7% likely to lose more than $200.
The RaR for different confidence levels is shown in cells B26 to B30: we can see that with 1 in 4 chance the loss in the next month will exceed $231, with 1 in 10 chance the loss will exceed $273, etc. This is a much more useful way of view RaR since it attaches a specific likelihood to each outcome. Even if you only want a single RaR number, you can explicitly choose the confidence level so you know exactly what you are getting.
What confidence levels should you choose? For most SaaS companies with monthly billing we recommend confidence levels between 75% and 90% because that spans the range where such an event is reasonably likely to happen in the next year. You can also calculate this with binomial probabilities: If a loss is 10% likely than you are 34% likely to see that loss at least once in the next 12 months (=1-BINOM.DIST(1,12,0.1,TRUE)); if a loss is 25% likely you are 84% likely to see that loss at least once in the 12 months (=1-BINOM.DIST(1,12,0.25,TRUE))). If you look at confidence levels above 90% or below 75% its either not that likely or practically guaranteed you will see that loss and its not quite as interesting.
That’s it for the basic method, there is something much more interesting we can do and we’ll go over it in a future post: what about using non-uniform churn probabilities, like the ones you get from a churn forecasting system like Sparked’s Retention Radar? In that case you can make an even more useful RaR number by simulation of churn with your modeled probabilities.
Example of calculating RaR with Confidence Levels: