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

 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 x1 and x2 (inclusive):

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

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

=POISSON.DIST(x2,frequency,TRUE)

gives you the probability of a count of x2 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 x1 and x2, inclusive of x1 and x2.

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.