The Poisson distribution is a classic model for the frequency with which events occur. You can use it whenever events occur in a given region and in a certain time window independently of each other and at a constant rate.
Such questions come up in a call centre, in a shop, at a toll station, at insurance companies, in production or in risk management. Below we show how the distribution is defined and when it fits. Then we simulate it step by step in Excel with Risk Kit, compare it with the binomial and the normal distribution and look at when an event is not Poisson distributed.
What is a Poisson distribution?
A Poisson random variable states how often an event occurs in a given region and in a given time window. It therefore only takes whole numbers as values: 0 if no event at all occurs in the time window under consideration, and otherwise 1, 2, 3 and so on.
The distribution has a single parameter. It is usually called lambda (λ), and in terms of content it is the expected frequency of occurrence in the region and in the time window. If we expect three events, for example, then λ = 3. The probability that the event occurs exactly k times is:
P(X = k) = λᵏ · e^(−λ) / k! for k = 0, 1, 2, …
Here k! = 1 · 2 · … · k, and e ≈ 2.718 is Euler’s number.
In theory, the Poisson distribution has no upper limit. Any number of events could therefore occur. In practice, however, this is not the case: the distribution tails off relatively quickly and stays in a neighbourhood around the expected frequency of occurrence. The simulation further below shows this clearly.
What are examples of the Poisson distribution?
Typical applications for the Poisson distribution are questions like these:
- How many calls come into a call centre on one day?
- I have a shop. How many customers come through the door on Tuesday morning between nine and ten o’clock?
- How many cars arrive at a toll station on a given day in a given region?
It is always the same pattern. We narrow down a range, regionally and in time, and ask: how often does an event occur in this range?
When does a Poisson distribution fit?
The Poisson distribution can be used whenever occurrences happen in a region and in a certain time window under two conditions:
- The occurrences happen independently of each other.
- They happen at a constant rate that does not change within the time window and the region under consideration.
Limiting the analysis to one period and one region usually serves exactly this purpose. It is meant to ensure that the rate of occurrence is constant and does not change. If business in my shop is different in the morning between nine and ten o’clock from what it is between ten and eleven o’clock or around lunchtime between eleven and twelve o’clock, it is worth separating these time ranges from each other. We then look at the early, the middle and the late phase separately, so that conditions are homogeneous, that is uniform, in each individual segment.
The Poisson distribution is used very often according to this scheme. Insurance companies use it to examine how many claims their current customer base submits in a certain time window and in a certain region. In production, people look at how many defective parts are produced on one day. All of these are random processes, but one can say fairly confidently that they happen independently and homogeneously.
If, on the other hand, the events are not independent of each other, the Poisson distribution does not fit without further ado. More on this at the end of this article.
How do you simulate a Poisson distribution in Excel?
In short: Risk Kit is an add-in for Microsoft Excel that lets you build and analyse Monte Carlo simulations directly in the spreadsheet. What we show here applies in general, though. Whether you simulate with Risk Kit, with Python or with any other tool: the Poisson distribution has the same parameter everywhere and delivers the same results.
We stay with the example of three expected events. In our spreadsheet, the expected frequency of occurrence is in cell B8, with the label “Erwartete Eintrittshäufigkeit” (expected frequency of occurrence) above it. Cell B8 contains the value 3. The Poisson random variable is to be created in cell D8. We proceed in five steps:
- Select cell D8, open the button for popular distributions on the Risk Kit toolbar (“Beliebt” in the German interface) and choose the Poisson distribution.
- In the input dialog, enter the only parameter lambda, the expected frequency of occurrence. We enter a reference to cell B8. To the right of the field, Risk Kit shows the value the reference points to: 3.
- Clicking “Insert” makes Risk Kit write the function =Poisson_variate(B8) into cell D8.
- Mark cell D8 as an output with the output button (“Ausgabe” in the German interface) and name it “Poisson-Verteilung”. Risk Kit adds OutputName("Poisson-Verteilung") to the formula and highlights the cell in yellow.
- Run the simulation with the “Simulation” button.
Already in the input dialog we can see directly on the right what the frequency distribution will look like. Figure 1 shows this moment. On the left of the spreadsheet, cell B8 contains the 3, and the formula bar at the top confirms this. The title bar of the dialog, “[D8] = Poisson_variate(...)”, shows that the function will be inserted into cell D8. The lambda field contains the reference B8, with the value 3 next to it.
In the preview, the horizontal axis shows the number of events and the vertical axis the probability. The orange area is highest in the range of two and three events, close to the expected three. To the right it flattens quickly. The table on the far right confirms this: the expectation is 3.00. As the maximum it shows the symbol ∞ for infinity, because the distribution has no upper limit. The row for 99.9%, on the other hand, shows 10. So in 99.9% of cases at most ten events occur.
Figure 1: Cell B8 contains the expected frequency of occurrence 3. In the input dialog for cell D8, lambda refers to B8, and the preview on the right shows the frequency distribution (German interface).
After the simulation, Risk Kit shows the result for the output “Poisson-Verteilung” (Figure 2). The distribution has the same shape we have just seen in the preview. The formula bar at the top shows the complete formula of cell D8: =Poisson_variate(B8)+OutputName("Poisson-Verteilung"). The cell itself is highlighted in yellow.
In the histogram, the horizontal axis again shows the number of events. Every whole number from 0 to 11 has its own narrow bar, and there is nothing in between, because the Poisson distribution only takes whole numbers. The height of a bar shows how often this value occurred in the simulation. The horizontal axis is labelled in uneven steps. The bars nevertheless stand exactly on the whole numbers, and only their heights relative to each other matter. The highest bars are at 2 and 3. In the table on the right, the mean is 2.99, almost exactly the expected frequency of occurrence of 3. Although the distribution has no upper limit, no value above eleven occurred in the entire simulation: the minimum is 0 and the range is 11. From nine events onwards the bars are barely visible.
Figure 2: Simulation result for an expected frequency of occurrence of 3. Every whole number from 0 to 11 has its own bar, and the table on the right shows a mean of 2.99 and a standard deviation of 1.74, among other statistics (German interface).
You can also display the result as a cumulative distribution function. For every value on the horizontal axis, it gives the probability that at most this many events occur. To do this, use the button with the chart symbol at the bottom left of the result window and choose “CDF”, short for cumulative distribution function. Figure 3 shows the result: the cumulative distribution function rises like a staircase and jumps up one step at every whole number. Between two whole numbers it stays flat, because no values occur there. Towards the right it quickly approaches 1.
Figure 3: The same result as a cumulative distribution function. It rises like a staircase and jumps up one step at every whole number (German interface).
How is the Poisson distribution related to the binomial and the normal distribution?
The Poisson distribution is closely related to the binomial distribution and also to the normal distribution. A binomial distribution with very many repetitions and a small probability of occurrence can hardly be distinguished from a Poisson distribution with the same expected frequency of occurrence. If, in addition, the expected frequency of occurrence is so high that we practically no longer see zero and the distribution tails off on the left and on the right, then the Poisson distribution also resembles a normal distribution.
Let us take a look. We enter 20 as the expected frequency of occurrence in cell B8 and simulate again. As a cumulative distribution function we now get a nice S-shaped curve, and as a histogram a bell curve. Figure 4 shows the histogram. The bars rise from the left, are highest around 20 and fall again to the right almost like a mirror image. Zero no longer occurs: the table shows a minimum of 5. The mean is 20.01 and the standard deviation 4.47.
Figure 4: Simulation result for an expected frequency of occurrence of 20. The histogram forms a bell curve around 20, and zero no longer occurs (German interface).
Now we lay a binomial distribution over it. This can be done directly in the input dialog. We open the input dialog of the Poisson distribution for cell D8 again, use the button with the chart symbol at the bottom left to choose “Add overlay” (“Overlay hinzufügen” in the German interface) and then select the binomial distribution. It should also have the expected frequency of occurrence 20, for example with a probability of occurrence of 10% and a very high number of repetitions, here 200. After all, 10% of 200 is again 20.
In Figure 5, a second block “Binomial_variate(...)” with P = 10% and N = 200 appears on the left below the fields of the Poisson distribution. In the preview on the right, the Poisson distribution is drawn as an orange area and the binomial distribution as a blue line. The horizontal axis now extends to 200, because the binomial distribution with 200 repetitions allows up to 200 occurrences. That is why both distributions are squeezed together on the left around 20. But you can see that the blue line follows the edge of the orange area almost exactly. In this preview, the binomial distribution can hardly be distinguished from the Poisson distribution.
Figure 5: Input dialog with a binomial distribution as an overlay (P = 10%, N = 200). The blue line lies almost exactly on the orange area of the Poisson distribution (German interface).
The same applies to a normal distribution that we lay over it in addition. Here, too, the expectation is 20. The standard deviation is the square root of 20, entered in the dialog as wurzel(20), the German name of the square root function. This is exactly the standard deviation of the Poisson distribution. It also matches the standard deviation of 4.47 from the simulation in Figure 4.
In Figure 6, a third block “Normal_variate(...)” has been added on the left, with an expectation (“Erwartung”) of 20 and Std. dev. wurzel(20). Next to it is the calculated value 4.47213595. In the preview, the normal distribution is drawn as a green line. Here, too, the distributions lie on top of each other practically seamlessly: the green line follows the orange area and almost completely hides the blue line of the binomial distribution.
Figure 6: In addition, a normal distribution with expectation 20 and standard deviation equal to the square root of 20. All three distributions lie practically on top of each other (German interface).
With many repetitions and a small probability of occurrence, you can therefore replace the Poisson distribution with a binomial distribution with the same expectation, and vice versa. With a high expected frequency of occurrence, this also applies to a normal distribution with expectation λ and standard deviation equal to the square root of λ.
How do you model risks that can occur several times?
Because the Poisson distribution represents independent and homogeneous events very well, it is used very often in enterprise risk management, among other fields. There it serves for risks that can occur several times. This is not a rare case at all: many risks can occur more than once in a period, that is, in a limited time range, for example cyber attacks or cases of fraud.
The simulation then has two stages. In the first step, the Poisson distribution determines how often the event has occurred. In the second step, the damage is determined for each individual occurrence. This is the approach that many companies take.
When is an event not Poisson distributed?
An event is not Poisson distributed, for example, when the occurrences are not independent of each other.
Take motor insurance. Under normal conditions, you can use a Poisson distribution to model how many claims occur in a certain period. This works because the occurrences are completely independent of each other. A car accident happening here has nothing to do with a car accident happening there.
This changes abruptly, however, when insurance claims go back to a systematic event, for example a hailstorm in the region. Suddenly a great many cars are damaged by this one central event, and they are damaged in a way that depends on each other. The owners go to their insurer to claim for the damage under their comprehensive cover. In this situation the Poisson distribution would not be correct.
However, the Poisson distribution can be used again if you take the hail into account. Under the condition that the hailstorm has occurred, a different Poisson distribution applies, with a much higher expected frequency of occurrence than normal. The parameter λ thus becomes random itself and depends on the systematic event. If the claims occur independently of each other once this event is fixed, the model is correct again.
More distributions
For an overview of all distributions, see our guide “When Do I Use Which Distribution?”.
- When do I use a Bernoulli distribution in a Monte Carlo simulation?Definition, formula and examples: when the Bernoulli distribution fits and what role it plays as a switch in a simulation.
- When do I use a binomial distribution in a Monte Carlo simulation?Definition, formula and examples: how the binomial distribution describes the number of successes in n independent trials and how to calculate it in Excel.
- When do I use a normal distribution in a Monte Carlo simulation?Definition, formula and examples: when the normal distribution fits, where its limits lie and how to truncate it.
- When do I use a triangular distribution in a Monte Carlo simulation?Definition, formula and example: when the triangular distribution fits, why the most likely value is not the expected value and where its limits lie.
- Quantifying expert estimates in a Monte Carlo simulation: the Expert distributionQuantifying expert estimates better: how the Expert distribution captures estimates through bounds and quantiles, even without a fixed minimum or maximum.
- When do I use a PERT distribution in a Monte Carlo simulation?Definition, formula and example: how the PERT distribution describes a bounded duration with minimum, most likely value and maximum, and where its limits lie.
- When do I use a lognormal distribution in a Monte Carlo simulation?Definition, properties and limits: when the lognormal distribution correctly describes proportional growth processes and how to parametrise it in Excel.
- Custom distributions in a Monte Carlo simulation: the Flex distributionCustom distributions: how the Flex distribution is built from values and probabilities and when it fits, from expert estimates to transferring simulation results.