Wehrspohn Risk Management

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

By Dr. Uwe Wehrspohn.

The normal distribution with its bell curve describes quantities that arise from many small, mutually independent influences, such as measurement errors or, with limitations, returns of stock portfolios. We explain its definition and parameters, when to use it, where its limits lie and how to simulate it in Excel with Risk Kit, including truncation and expert estimates.

The normal distribution is one of the most famous distributions of all. In Germany it is known above all through the mathematician Carl Friedrich Gauss, who described it as early as the beginning of the 19th century. Its hallmark is the characteristic bell curve.

In a Monte Carlo simulation, the normal distribution is a good model whenever many small, mutually independent influences add up to an overall result. Below we show how the distribution is defined, when it fits and where its limits lie. Then we simulate it step by step in Excel with Risk Kit and look at some special properties of the normal distribution.

What is a normal distribution?

The normal distribution is described by two classic parameters: the expected value μ and the standard deviation σ. The expected value is the centre of the bell curve. The standard deviation determines how far the curve spreads to the left and right: the larger it is, the wider the bell.

Written as a formula, the density of the normal distribution is:

f(x) = 1 / (σ √(2π)) · exp(−(x − μ)² / (2σ²))

An example: expected value 20, standard deviation 10

Take, for example, an expected value of 20 and a standard deviation of 10. Figure 1 shows the corresponding bell curve as the preview in Risk Kit displays it. The horizontal axis shows the possible values, the vertical axis the density. The higher the curve above a value, the more often values near it occur. The peak lies at the expected value 20, and the curve falls off symmetrically to the left and right.

Next to it are statistics of the distribution. Expected value and median are 20.00. The rows with percentages are quantiles: 1% of the values lie below −3.26, 99% lie below 43.26. Min. and Max. show −∞ and ∞. So the distribution is unbounded on both sides; it lives on the entire real line.

Figure 1: Normal distribution with expected value 20 and standard deviation 10. On the left the bell curve, on the right statistics such as the median and quantiles (German interface).

When does a normal distribution fit?

A good indicator of when the normal distribution lends itself as a model is the central limit theorem. It says that sums of small influences, none of which is dominant and which are independent of each other, converge to a normal distribution. So when many small, non-dominant, independent influences add up, the result is a normal distribution.

Why the bell curve arises

This context also makes it immediately clear where the bell shape comes from. When many things add up, deviations to the right and deviations to the left tend to offset each other. Opposing effects net out. That is why our overall result very often ends up in a middle range.

To get a very large or, conversely, a very small value, many of these influences have to go in the same direction, either up or down. That they all go in the same direction is of course much less likely than that they are thoroughly mixed and balance each other out. This is why the curve is high in the middle and flattens out towards the edges.

Measurement errors

The classic application is measurement errors. This is why the normal distribution is so important in physics and engineering. Measurement errors depend on many small effects that add up and are independent of each other. That is why they deviate sometimes to the right and sometimes to the left of the expected value.

Returns of stocks and stock portfolios

Another famous example is the daily returns of stocks and especially of stock portfolios. Stock prices are influenced by many factors that rain down on the markets. Many small pieces of information have their influence here. That is why the price fluctuates sometimes up and sometimes down, and in sum the whole thing is normally distributed.

For a stock portfolio this effect is even more pronounced, because here the sum is also taken over different stocks. The catchment area of the information being processed is therefore even broader.

The limit: dominant events

The stock market, however, also directly shows a limit of the normal distribution. It requires many small, non-dominant effects to add up. On the financial market, though, major central news can arrive. It pushes the price to extremes up or down, so that returns become very large or very small.

Such events occur more often on the financial market than they would under a normal distribution, precisely because dominant effects occur there. In this situation one speaks of so-called long tails. This means that large deviations from the expected value occur more often than under the normal distribution.

Sales fluctuations: normal case and crisis

We can argue in a very similar way about sales fluctuations. Sales also depend on many factors, and in day-to-day business they are mostly normally distributed. Here too, however, important events can occasionally trigger large deviations.

You can “rescue” the normal distribution here by making a case distinction. An upstream random experiment first decides whether we are in a normal scenario or in a particularly pronounced special case. In both cases we may then be able to use a normal distribution, but with a different expected value and a different standard deviation. In the normal case we expect a certain development, and in a crisis possibly a different development with a different expected value and a different spread.

Which values can a normal distribution take?

The normal distribution is a continuous distribution: between any two values it can take any value in between. It is also unbounded, as Figure 1 already showed: at least in theory, every value on the real line occurs.

This can lead it into ranges of values that are not suitable for our model. Many quantities cannot take negative values, for example lengths or weights. In our example from Figure 1, however, the curve reaches well into the negative range on the left. According to the table, 1% of the values lie below −3.26 and 0.1% even below −10.90. For a length or a weight, this would violate a fundamental assumption.

To correct this, we can cut off the distribution, which is also called truncating. To do so, we set a lower and an upper bound for the range of values we want to allow for the normal distribution. If I set the lower bound to 0, the normal distribution can no longer become negative. The next section shows how this works in practice.

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

We stay with our example. In the spreadsheet, the expected value 20 is in cell B8 and the standard deviation 10 is in cell B10. The normally distributed random number is to be created in cell D8. We assume that our quantity cannot become negative and therefore truncate the distribution at 0. We proceed in seven steps:

  1. Select cell D8, open the button for popular distributions on the Risk Kit toolbar (“Beliebt” in the German interface) and choose the normal distribution. The input dialog of the function Normal_variate opens. The “Parameter” field shows “Standard”, that is, the description via expected value and standard deviation.
  2. Enter the two parameters: a reference to cell B8 in the expected value field (“Erwartung”) and a reference to cell B10 in the “Std. dev.” field. To the right of the fields, Risk Kit shows the values the references point to: 20 and 10.
  3. At the bottom left of the dialog, open the button with the two small distributions and choose the truncate option (“Abschneiden”). The dialog gets two more fields, the lower bound (“Untere Grenze”) and the upper bound (“Obere Grenze”).
  4. Enter 0 as the lower bound and leave the upper bound empty. The distribution stays open at the top.
  5. Clicking “Insert” makes Risk Kit write the function =Normal_variate(B8;B10;0) into cell D8.
  6. Mark cell D8 as an output using the “Output” button (“Ausgabe”) and give it the output name “Trunkierte Normalverteilung”. Risk Kit adds OutputName("Trunkierte Normalverteilung") to the formula and highlights the cell in yellow.
  7. Run the simulation.

Figure 2 shows steps 1 to 3. On the left of the spreadsheet are the inputs: the expected value 20 in B8 and the standard deviation 10 in B10. On the right, the input dialog for cell D8 is open, as the title bar “[D8] = Normal_variate(...)” shows. The orange wedge points to the target cell D8. The expected value field refers to B8, the “Std. dev.” field to B10. At the bottom left, the menu of the button with the two distributions is open, and the truncate option (“Abschneiden”) is highlighted.

Figure 2: Input dialog for cell D8 with references to B8 and B10. The menu at the bottom left truncates the distribution (German interface).

In Figure 3, the two new fields are filled in. The lower bound is 0. The field for the upper bound is empty, and next to it Risk Kit shows “any”. The preview shows the effect immediately: the curve now begins at 0 and breaks off there; to the left of it there are no more values. In the table, Min. is now 0.00. The expected value has risen to 20.55, because the truncated left part is missing.

Figure 3: The normal distribution with a lower bound of 0. The curve begins at 0, and the minimum in the table is 0.00 (German interface).

Figure 4 shows the result of steps 5 and 6. Cell D8 is highlighted in yellow and thus marked as an output. The formula bar shows the complete formula. The first two arguments of Normal_variate are the references to the expected value and the standard deviation, and the third argument is the lower bound 0. The cell itself contains a single random number from this distribution, here 11.8942515.

Figure 4: Cell D8 is marked as an output. The formula bar shows the complete formula =Normal_variate(B8;B10;0)+OutputName("Trunkierte Normalverteilung") (German interface).

After the simulation we see our normal distribution again, now truncated. Figure 5 shows the histogram of the simulated values on the left. Each bar stands for a narrow range of values, and its height shows how often values fall into it. The bars begin at 0; negative values no longer occur. Values around 20 are the most frequent, and to the right the bars flatten out. On the right are the statistics of the simulated values: the mean is 20.42, the standard deviation 9.39 and the minimum 0.00.

Figure 5: Simulation result of the truncated normal distribution. On the left the histogram, which begins at 0, on the right the statistics, including a mean of 20.42, a standard deviation of 9.39 and a minimum of 0.00 (German interface).

Where do the parameters come from: data or experts?

The normal distribution is described by the expected value and the standard deviation. If we have data to which we can fit (calibrate) the distribution, these two values can be estimated directly from the data. For this case, this parameterisation is perfectly suited.

It is different if we want to determine the normal distribution from expert estimates. Experts often cannot state the standard deviation well. It is an abstract statistical measure that, to a certain extent, eludes everyday experience.

That is why Risk Kit offers a second parameterisation, namely via percentile and confidence level. Percentile and quantile mean the same thing here. Here, too, the expected value is a parameter. Instead of the standard deviation, however, I enter a quantile together with the probability with which it should not be exceeded. For example, I say: my expected value is 20, my quantile is 30, and it should not be exceeded with a probability of 99%. Then 30 is the 99% quantile. With a probability of 1% I am above it, and with a probability of 99% below it.

Figure 6 shows this input. In the “Parameter” field, “Perzentil + Konfidenzniveau” (percentile + confidence level) is now selected. For this parameterisation Risk Kit uses the function Normal2_variate, which is also shown above the table on the right. Below it are the three parameters: “Expectation” with the reference to cell B8, that is, the expected value 20, “C” with the confidence level 0.99 and “Q” with the quantile 30. The fields for the bounds are empty, so the distribution is not truncated here. In the preview, a dashed vertical line marks the value 30, and in the table the row for 99% accordingly shows 30.00. The title “Trunkierte Normalverteilung” above the chart is still the output name of cell D8 from the previous section.

Figure 6: The second parameterisation via percentile and confidence level, with expected value 20 from B8, confidence level 0.99 and quantile 30. The dashed line marks the quantile at 30 (German interface).

What are soft bounds?

This example also shows: although the normal distribution is infinitely wide, in practice it has so-called soft bounds. In the table in Figure 6, Min. and Max. show −∞ and ∞. Nevertheless, the 0.1% quantile is 6.72 and the 99.9% quantile is 33.28. So almost all values stay within a fairly narrow range around the expected value 20.

Towards the outside, the curve goes to zero so quickly that in a normal simulation the normal distribution does not leave a certain range around the expected value. You can say that it does not leave the range of three standard deviations to the left and right with a probability of about 99.7%. If we take four standard deviations, the probability of staying within this range is considerably greater still. For many practical purposes, the normal distribution is therefore bounded after all.

For quantities with a naturally bounded range of values, this is a good thing. It means the normal distribution can also be used for quantities that cannot take the entire range of values at all. One example is people's body height. It is approximately normally distributed, but of course it cannot become negative. We can truncate here, but the range of values is not exhausted anyway: we do not even come close to the bound. That is why the distribution of body height is very close to the normal distribution despite the bound at 0. For rare, very large losses, on the other hand, it is a disadvantage, because deviations of more than four to five standard deviations practically never occur.

Why do sums stay normally distributed?

The normal distribution has a very special property that is extremely rare. Linear combinations of independent normally distributed random variables are again normally distributed, for example their sums. I can therefore build chains of normal distributions.

This is used in particular when simulating prices or developments in general. You start from a certain value and model a change in this quantity over a certain period, for example a price change. From there you continue over the next period and then over the third. If all these changes are normally distributed and independent of each other, the total change over all three periods is also normally distributed.

Regardless of the length of the time window, we therefore always stay within the same model. This very convenient property is the reason why a great many classic models for price developments are based on the normal distribution.

The normal distribution is closely related to many other probability distributions. The binomial and the Poisson distribution can be approximately described by a normal distribution for suitable parameter values. Further relatives are the chi-squared distribution and the t-distribution.

Particularly closely related is the sister of the normal distribution, the lognormal distribution. The logarithm of a lognormally distributed random variable is normally distributed. The two therefore arise from each other through a simple transformation and are directly connected.

So if I describe the logarithmic returns of stocks with a normal distribution, the stock prices are lognormally distributed. This is a very classic model for stocks that exploits the relationship between these two distributions and that we encounter again and again on the financial markets. We will look at it more closely in the article on the lognormal distribution.

More distributions

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