Wehrspohn Risk Management

When do I use a binomial distribution in a Monte Carlo simulation?

By Dr. Uwe Wehrspohn.

The binomial distribution carries out an experiment with the outcomes success or failure n times, independently of each other, and gives the probability that 0, 1, 2 or more successes occur. We explain its definition and examples, when to use it, how to simulate it and calculate it directly in Excel with Risk Kit, and how to proceed when the number of trials is itself random.

In the Bernoulli distribution, an experiment is carried out that has either success or failure as its outcome (see the article on the Bernoulli distribution). The binomial distribution continues this idea and carries out such an experiment n times in a row. It gives the probability that 0, 1, 2 or more successes occur.

There are many examples of this: coin tosses and dice games, medicines and patients, exams, light bulbs or surveys. Below we show how the distribution is defined and when it fits. Then we simulate it in Excel with Risk Kit, calculate its probabilities directly and show how to proceed when the number of trials is not fixed in advance.

What is a binomial distribution?

A binomial distribution exists precisely when we repeat the same experiment n times and all repetitions are completely independent of each other. As in the Bernoulli distribution, each single experiment has only two possible outcomes, success or failure. The binomial random variable is the number of successes. It can take the values 0, 1, 2 and so on up to n.

The distribution has two parameters: the success probability p of the single experiment and the number of trials n. The probability of getting exactly k successes is:

P(X = k) = (n choose k) · pᵏ · (1 − p)ⁿ⁻ᵏ for k = 0, 1, …, n

Here, (n choose k) is the number of ways to distribute k successes among n trials.

Example: ten coin tosses

Take the example of a coin that I toss ten times. The success probability that it shows heads is 50%. Figure 1 shows the number of successes with their associated probability. The horizontal axis shows the number of successes from 0 to 10, that is, how often the coin shows heads in the ten tosses. The height of each bar is the probability of exactly this number. The vertical axis gives it as a decimal, so 0.25 corresponds to 25%.

The highest bar is in the middle: I get five successes with a probability of about 25%. Towards the edges the bars quickly become smaller. It is very unlikely that I have no success at all in ten tosses or that every toss is a success. That is why the bars at 0 and at 10 are hardly visible.

Figure 1: Binomial distribution for ten tosses with a success probability of 50%. Five successes are the most likely, at about 25%.

Any success probability

The use of the binomial distribution is not limited to the case in which the success probability is exactly 50%, as with a fair coin. Suppose, for example, that we are playing Ludo and want to roll a six to bring a piece out of the start area. Then the success probability is one sixth, or about 17%. We stay with ten rolls.

Figure 2 shows the probabilities that 0, 1, 2, 3, 4 and so on successes occur. Compared with Figure 1, the picture has changed considerably. The highest bar is now at one success and reaches just over 0.3, that is, just over 30%. No success and two successes are also quite likely. From four successes onwards the bars become very small, and from six successes onwards they are hardly visible.

Figure 2: Binomial distribution for ten rolls with a success probability of one sixth. One success is the most likely.

When does a binomial distribution fit?

A binomial distribution fits when the following conditions are met:

  • We repeat the same experiment several times. Each single experiment has only two outcomes, success or failure, and the success probability p is the same for each of them.
  • All repetitions are completely independent of each other.
  • We are interested in how many successes occur in total.
  • We know in advance how many trials we will make.

The last condition describes the ideal case. In practice it is not always met. We show further below how to proceed in that case.

The fields of application of the binomial distribution are just as varied as those of the Bernoulli distribution. In all of the following situations, the number we are looking for is binomially distributed, provided that the success probability is the same for all of them:

  • I have given a medicine to 20 patients. In how many patients is the medicine effective?
  • 50 students take an exam. How many students pass the exam?
  • I have ten light bulbs that burn for one month. How many of them break during this month?
  • I conduct a survey and ask 50 people a certain question. How many of these people give a very specific answer?

How do you simulate a binomial 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 binomial distribution has the same parameters everywhere and delivers the same results.

We stay with the coin toss. In our spreadsheet, the success probability of 50% is in cell B8 and the number of trials, 10, is in cell B10. The binomial random variable is to be created in cell D8. We proceed in five steps:

  1. Select cell D8, open the button for popular distributions on the Risk Kit toolbar (“Beliebt” in the German interface) and choose the binomial distribution.
  2. In the input dialog, enter the two parameters, the success probability P and the number of trials N. We enter a reference to cell B8 for P and a reference to cell B10 for N. To the right of the fields, Risk Kit shows the values the references point to: 0.5 and 10.
  3. Clicking “Insert” makes Risk Kit write the function =Binomial_variate(B8;B10) into cell D8. The German version of Excel separates the arguments with a semicolon.
  4. Mark cell D8 as an output and name it “Binomial-Verteilung”. Risk Kit adds +OutputName("Binomial-Verteilung") to the formula and highlights the cell in yellow.
  5. Run the simulation.

Figure 3 shows the input dialog from step 2. The function field is set to “Zufallszahl” (random number), and below it are the fields P and N with the references to B8 and B10. To the right, Risk Kit shows a preview of the distribution. It has the same shape as Figure 1: it is highest in the middle at 5 and falls off towards the edges. The table on the far right shows, among other things, the expected value, 5.00.

Figure 3: Input dialog for cell D8. The parameter P refers to B8 (0.5), the parameter N to B10 (10). On the right, Risk Kit shows a preview of the distribution (German interface).

Figure 4 shows the state after the simulation. At the top, the formula bar shows the complete formula of cell D8: =Binomial_variate(B8;B10)+OutputName("Binomial-Verteilung"). Cell D8 is marked in yellow as an output. Below it, Risk Kit shows the result for the output “Binomial-Verteilung”. On the left is the histogram of the simulated values. The horizontal axis shows the numbers of successes from 0 to 10, and the height of each bar is the relative frequency with which this number occurred in the simulation. The simulated distribution looks exactly like the preview in the input dialog: the highest bar is at 5 and reaches about 0.25. On the right are the statistics of the simulated values, for example a mean of 5.01.

Figure 4: Cell D8 is marked as an output, and the formula bar shows the complete formula. The histogram of the simulation has the same shape as the preview, and the mean is 5.01 (German interface).

The dice example works the same way. We change the success probability in cell B8 to one sixth and simulate again. For the relative frequencies of the individual numbers of successes we get practically the same values that the chart in Figure 2 shows.

How do you calculate the probabilities of a binomial distribution directly?

We can simulate these results, but we can also calculate them directly. For this, cells C14 to C24 of the spreadsheet contain the possible numbers of successes from 0 to 10. Column D, headed “Ws n Erfolge” (probability of n successes), is to contain the probability of getting exactly as many successes as stated in column C. Column E, headed “Höchstens n Erfolge” (at most n successes), is to contain the probability of getting at most that many successes. The n in the headings stands for this number, not for the number of trials. The success probability in B8 is set to one sixth, and the number of trials in B10 to 10.

Probabilities of exactly this many successes (PMF)

For the individual probabilities, we set the function field to “PDF / PMF”. PMF stands for probability mass function. It gives the probability that the random variable takes exactly one particular value, here exactly one particular number of successes. We proceed in four steps:

  1. Select cell D14, choose the binomial distribution again via the popular distributions button on the Risk Kit toolbar and set the function field to “PDF / PMF”.
  2. Enter the parameters: for X the desired number of successes, here a reference to cell C14 with the value 0, for P the success probability in B8 and for N the number of trials in B10. In the name field we refer to the column heading in D13. The name only serves as a label here.
  3. Clicking “Insert” makes Risk Kit write the formula =Binomial_pmf(C14;B8;B10)+OutputName(D13) into cell D14.
  4. Fix the references to B8 and B10 with dollar signs, so that the formula reads =Binomial_pmf(C14;$B$8;$B$10)+OutputName(D13), and copy the formula down to cell D24.

Figure 5 shows the input dialog from step 2. Below the fields is the result, 0.161505583. With a success probability of one sixth and ten trials, the probability of no success at all is therefore 16.2%. This is the same value that the bar at 0 in Figure 2 shows. The yellow cell D8 shows a newly drawn random number each time.

Figure 5: Input dialog for cell D14 with the function “PDF / PMF”. X refers to C14 (0 successes), P to B8 and N to B10. The result is 0.1615, that is, 16.2% (German interface).

After copying, column D contains the probabilities for all numbers of successes, including the higher ones (Figure 6): 16.2% for no success, 32.3% for one, 29.1% for two, 15.5% for three successes and so on. These are exactly the heights of the bars in the chart next to it.

Figure 6: Column D contains the probabilities of exactly this many successes (PMF), column E those of at most this many successes (CDF). The values in column D are the heights of the bars in the chart (German interface).

Probabilities of at most this many successes (CDF)

If we want to calculate what the distribution function looks like, that is, how likely it is to have at most this many successes, we proceed in the same way. CDF stands for cumulative distribution function. We proceed in three steps:

  1. Select cell E14, choose the binomial distribution on the Risk Kit toolbar again and set the function field to “CDF”.
  2. Enter the parameters: for X the number of successes in C14, for P the success probability in B8 and for N the number of trials in B10. Clicking “Insert” makes Risk Kit write the formula =Binomial_cdf(C14;B8;B10) into cell E14.
  3. Fix the references to B8 and B10 with dollar signs, so that the formula reads =Binomial_cdf(C14;$B$8;$B$10), and copy the formula down to cell E24.

The result is in column E of Figure 6. For zero successes, the smallest possible value, “at most zero” and “exactly zero” mean the same thing. That is why D14 and E14 both show 16.2%. For the higher values, it is in each case the sum of the individual probabilities. For at most one success, for example, it is 16.2% plus 32.3%, that is, 48.5%.

We see here that with a success probability of 17% and ten trials, hardly more than six successes are to be expected. For at most six successes, column E already shows 100.0%. The probability that 7, 8, 9 or 10 successes occur is so small that one decimal place for the percentages is no longer enough to display it. That is why column D shows 0.0% there.

So we can not only draw random numbers from a distribution. We can also calculate other quantities for the distribution. In Risk Kit this works for all distributions that are available.

What if the number of trials is not fixed?

In practice we often have a variation of the ideal case: we do not necessarily know in advance how many trials we will make. Sometimes we do know. I know how many light bulbs I have and how many of them can burn out next month. I know how many machines I have and how many can break down. But as a hospital, I often do not know how many patients I will treat in the next quarter. Or, in a survey, I do not know how many people I will actually ask.

Most surveys are not set up so that I ask exactly 50 people and then stop. I set myself a certain time window and try to question as many people as possible within it. The number of people I ask in total is therefore itself random.

We can apply the binomial distribution in this situation too, by linking two experiments. In the first experiment we determine how many trials we make at all. Only in the second experiment does the binomial distribution come into play, with this number of trials.

Example: a random number of rolls

Let us look at this again with Ludo, this time asking how often I roll the die. We assume that the number of rolls is normally distributed, with an expected value of 10 trials and a standard deviation of 3 trials. To do this, we replace the fixed number 10 in cell B10 with the Risk Kit function Normal_variate(10;3), which draws a normally distributed random number with these two parameters. Because the number of trials has to be a whole number, Risk Kit rounds the drawn value to the nearest whole number.

In Figure 7 this random number has come out at 14. So I would have rolled 14 times, each time with a success probability of 17%. The table and the chart show the probabilities of the individual numbers of successes for these 14 trials. Compared with Figure 6, the distribution has shifted to the right: two successes are now the most likely at 28.4%, and no success at all has only 7.8%. In another situation I might have rolled 11 times, and then we would have slightly different probabilities for the individual numbers of successes.

Figure 7: The number of trials in B10 is now a normally distributed random number, here 14. The table and chart show the binomial distribution for 14 rolls (German interface).

In this situation, the binomial distribution always applies only to the number of trials drawn in the first step. We have a two-stage experiment, and the binomial distribution always comes only in the second step. In this approach, the Monte Carlo simulation runs in several stages that are calculated one after the other. This considerably widens the range of applications: we do not need to know the number of trials beforehand, because it only emerges in the course of the overall experiment.

Randomisation

Making a parameter of a distribution itself random is called randomisation. In Risk Kit, randomisation is possible in principle for all distributions. In the first step the parameters are drawn, and in the second step we draw a random number from the distribution with these parameters.

Can the binomial distribution be generalised?

The binomial distribution can be generalised in other respects as well, and it appears as a special case in other distributions. We will come back to this in later articles.

More distributions

For an overview of all distributions, see our guide “When Do I Use Which Distribution?”.