Likelihood of a future event count within a range, from a long-term event frequency: in Excel

What to read first: About the Clear Lines Excel implementation Likelihood of a future event count within a range, from long-term 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 long-term 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₁ and x₂ (inclusive):

=POISSON.DIST(x1-1,frequency,TRUE)

That formula gives you the probability of a count strictly less than x₁, while

=POISSON.DIST(x2,frequency,TRUE)

gives you the probability of a count of x₂ or fewer.

That means that the formula

=POISSON.DIST(x2,frequency,TRUE)-POISSON.DIST(x1-1,frequency,TRUE)

gives you the probability of an event count between x₁ and x₂, inclusive of x₁ and x₂.

For example, if the long-term 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(5-1,3.5,TRUE)

which calculates to 24.8%.

If you want the probability of 5 or more events, the formula is

=1-POISSON.DIST(5-1,3.5,TRUE)

which calculates to 27.5%.

Map of the series

			Likelihood of a future event…
Theory	How to turn an event frequency into likelihood	➜	…*count* within a range, from a long-term event frequency	➜	…count within a range, from a history of events	➜	…size total within a range
Excel	About the Excel implementation		…in Excel. This article		…in Excel.		…in Excel.