What to read first: About the Clear Lines Excel implementation Likelihood of a future event count within a range, from longterm event frequency 

Risk specialists  Version 1.0 Beta 
How to know the likelihood of any number of risk events in future, when you have limited history.
Using Excel you can get the probability of exactly one event in a year, given an average frequency of 0.2 per year (one every 5 years), as:
=POISSON.DIST(1,0.2,FALSE)
The answer is 0.16375, or about 16%. The FALSE parameter on the end means that you want the probability of exactly one occurrence, not the cumulative probability of one or fewer occurrences.
You can get the probability of x or fewer occurrences using the cumulative Poisson distribution. In Excel you just flip the third parameter to TRUE, so that the probability of one or fewer events with a longterm average of 0.2 per year is:
=POISSON.DIST(1,0.2,TRUE)
The answer is 0.98248 or about 98.2%. The difference is the probability of no events, which is 81.9%. The remaining 1.8% represents the probability of two or more events in one year.
You can use the cumulative distribution twice to get the probability of an event count within any range. Suppose we want the probability of an event count between x_{1} and x_{2} (inclusive):
=POISSON.DIST(x11,frequency,TRUE)
That formula gives you the probability of a count strictly less than x_{1}, while
=POISSON.DIST(x2,frequency,TRUE)
gives you the probability of a count of x_{2} or fewer.
That means that the formula
=POISSON.DIST(x2,frequency,TRUE)POISSON.DIST(x11,frequency,TRUE)
gives you the probability of an event count between x_{1} and x_{2}, inclusive of x_{1} and x_{2}.
For example, if the longterm frequency is 3.5 (average 7 events per two time units), the probability of an event count of 5, 6 or 7 in one time unit is
=POISSON.DIST(7,3.5,TRUE)POISSON.DIST(51,3.5,TRUE)
which calculates to 24.8%.
If you want the probability of 5 or more events, the formula is
=1POISSON.DIST(51,3.5,TRUE)
which calculates to 27.5%.
Map of the series
Likelihood of a future event… 

Theory 
➜  ➜  ➜  
Excel 
About the Excel implementation 
…in Excel. 
Download the complete Clear Lines Excel Workbook (17 MB)
Main article on repeating risk events and likelihood
➜
Next article for risk specialists
Parent articles
Likelihood of a future event count within a range, from longterm event frequency


Risk specialists  Version 1.0 Beta 