The lognormal distribution is a classic model for proportional growth processes. If, for example, a pay rise of 5% is granted, someone who earns a lot today gets more than someone who earns little today. Both get 5% of their current salary.
Below we show how the distribution is defined and how it arises step by step in a growth process. We look at its two characteristic properties and use the prices of the DAX to check where its limits lie. Then we parametrise it in Excel with Risk Kit and truncate unrealistically large values.
What is a lognormal distribution?
Proportional growth processes are fairly common. A large tree gains more mass than a small tree. A large bacterial culture grows more than a small one, the increase is larger there. A high price is raised more by the inflation rate than a low price. The relative change is the same in each case, but the absolute change differs.
Specifically, the lognormal distribution assumes that the logarithm of the growth factor is normally distributed. The distribution has two parameters, μ and σ. They are the expected value and the standard deviation of the normal distribution that describes the logarithmic growth factor. So for a lognormally distributed random variable X, ln X is normally distributed. In the formula, σ² stands for the variance, the square of the standard deviation:
ln X ~ N(μ, σ²)
How does a lognormal distribution arise in a growth process?
Let us look at this using a list in Excel. In Figure 1, column A holds the day, column B the logarithm of the growth factor and column C the value. On day 0 the value is 100, in cell C2.
We assume that the logarithmic growth factor is normally distributed, with an expected growth rate of 0.03% and a volatility (standard deviation) of 1.5%. Risk Kit draws these random numbers in column B, in cell B3 for example with the function =+Normal_variate(0,03%;1,5%) (German Excel uses commas as decimal separators and semicolons between arguments). There it has just returned −0.5358%.
The new value is the old value times e to the power of the logarithmic growth factor. Cell C3 therefore contains the formula =+C2*EXP(B3), which you can see in the formula bar in Figure 1. The exponential function cancels out the logarithm. So we simply multiply yesterday’s value by the growth factor. In this way, the starting value of 100 becomes 99.47 on day 1.
Figure 1: A growth process in Excel. Column B contains the logarithmic growth factors, column C the value. Cell C3 calculates the value for day 1 with =+C2*EXP(B3) (German interface).
On day 2 the procedure is the same. But now the starting value is no longer the value of day 0, it is the value of day 1. The previous day is always the new starting value. In this way, a new value arises step by step from the previous day’s value, and we get a growth process.
The value after each step is the starting value times the product of all growth factors so far. After each step, this process is lognormally distributed. This shows us that the product of independent lognormally distributed random variables is again lognormally distributed.
The value after 100 days
We repeat this step many times in a row, namely for 100 days. The cell with the value after 100 days is marked as an output with the name “Wert nach 100 Tagen Wachstum” (value after 100 days of growth). Then we simulate what this value looks like after 100 days of growth.
Next, we calibrate a lognormal distribution to the simulated values. That means we fit a lognormal distribution to the simulated data. To do this, we select the lognormal distribution in the Risk Kit calibration dialog and click “Kalibrieren” (calibrate).
Figure 2 shows the result. The orange bars are the histogram of the simulated values after 100 days. The horizontal axis shows the value, the vertical axis the density: the higher a bar, the more often values in this range occurred. The blue line is the calibrated lognormal distribution. It fits the data excellently. The estimated parameters are shown at the bottom left, Mu = 4.6350 and Sigma = 0.1498.
Figure 2: Calibration to the simulated values after 100 days. The blue lognormal distribution lies close to the orange histogram, and the estimated parameters are shown at the bottom left (German interface).
The “PDF (Log)” tab shows the density on a logarithmic scale. This makes it even easier to distinguish the details, that is, the small values. There, too, the lognormal distribution fits very well everywhere.
What are the properties of a lognormal distribution?
The lognormal distribution has two characteristic properties.
It cannot become negative
First, the lognormal distribution cannot become negative. A positive factor is always multiplied by the starting value, so we stay positive. Things that are negative can in principle not be lognormally distributed. This is an important limit.
Exchange rates and share prices, for example, are always positive. If an exchange rate became negative, I would simply throw the currency away instead of paying money to get rid of it. It would be similar with shares.
But this does not apply to every price factor. Interest rates, for example, can become negative, as we have seen over the last ten years. Raw material prices can also become negative. The electricity price becomes negative when there is overproduction, and then we have to pay someone to take the electricity off our hands. In principle this can happen with all raw materials when there is an oversupply. So these prices are not necessarily lognormally distributed in every situation.
It is always right-skewed
The second property is that the lognormal distribution is always right-skewed. It is not symmetric, its right end is longer. As we will see further below, this right end can also become extremely long. In the extreme case, the tail can therefore become very dangerous.
When does a lognormal distribution fit?
We have already seen a first limit: what can become negative is not lognormally distributed in every situation. A second limit shows up when we apply the lognormal distribution to real price developments.
Example: the returns of the DAX
As an example we take the DAX from 1994 to 2025. These are values that we can actually observe in the market. From them we can calculate the logarithmic price factors backwards, the so-called returns. The price factor is today’s price divided by yesterday’s price. We take the logarithm of it.
If the DAX were lognormally distributed, these returns would have to be normally distributed. So we calibrate a normal distribution to all the returns. The result in Figure 3 does not look all that good. The orange bars are the histogram of the daily returns. The blue line is the normal distribution that fits the data best, with Mu = 0.0003 and Sigma = 0.0140. It is too low in the middle, because the bars rise far above the line there. On the flanks, that is, to the left and right of the peak, the line lies above the bars. In the wings, that is, at the far ends, it is too low again. At the top left is the Kolmogorov-Smirnov value, a measure of how far the distribution deviates from the data: here it is 0.0741, while for the lognormal distribution in Figure 2 it was 0.0061.
Figure 3: Normal distribution calibrated to the daily returns of the DAX. The blue line is too low in the middle, too high on the flanks and too low again in the wings (German interface).
This is even easier to see when the density is shown on a logarithmic scale. On the flanks the normal distribution is too high, and in the tails, that is, in the wings, it is far too low.
A counterpart to the central limit theorem
How does this come about? In the article on the normal distribution we introduced the central limit theorem. It provides a framework for when the normal distribution fits particularly well. It states that the sum of many random influences is normally distributed if each individual influence is more or less independent of the others and if no influence is dominant, that is, each one is negligible in relation to the sum. Then the sum is approximately normally distributed.
Something analogous applies to the lognormal distribution. A product of many growth factors is approximately lognormally distributed as a whole if each individual factor is more or less independent of the others and none plays a dominant role. Each factor then disappears negligibly in the mass.
Individual events stand out
What does this look like for the development of the DAX? Figure 4 shows two lines. The blue line is the DAX itself, with its values on the right-hand axis. From 1994 it keeps rising, with ups and downs, up to today. The orange line shows the daily logarithmic growth rates, that is, the returns, with the scale on the left-hand axis.
What stands out about these growth rates is that the development is not uniform. Again and again there are swings that stand out from the rest, that is, very large values. Some of them we can name:
- On 11 September 2001 the World Trade Center was attacked.
- In October 2008 the financial crisis reached its peak.
- In March 2020 prices collapsed at the start of the pandemic. This is where the orange line has its deepest drop, at about −13%.
- At the beginning of March 2022, shortly after the start of the war in Ukraine, the DAX swung sharply.
Figure 4: DAX values (blue, right-hand axis) and daily logarithmic growth rates (orange, left-hand axis) from 1994 to 2025. Individual swings stand far out from the rest (German labels).
The assumption that all the logarithmic growth rates that play a role in the price development here are negligible in the overall context and disappear in the noise is therefore not fulfilled. There are individual prominent effects that stand completely out from the rest. This is exactly what we saw in the calibration in Figure 3.
Distributions for extreme events
We can therefore calibrate the returns again and add further distributions that are better suited than the normal distribution to represent extreme events. These are the Laplace distribution, the normal inverse Gaussian distribution and the Poly distribution.
In Figure 5 we see that the fit is now much better than before. The lines of the Poly distribution (blue), the normal inverse Gaussian distribution (green) and the Laplace distribution (red) follow the histogram in the middle, on the flanks and in the wings much, much better than the normal distribution (purple) that we already had. These three distributions can, so to speak, represent the long tails. On the logarithmic scale this becomes even clearer: in the wings their fit is very much better than that of the normal distribution.
Figure 5: New calibration to the returns of the DAX. The Poly, normal inverse Gaussian and Laplace distributions lie close to the histogram, the normal distribution (purple) does not (German interface).
That individual events stand out so far from the rest is an important limit even for the lognormal distribution’s showcase discipline, proportional growth. It, too, can be described with a lognormal distribution only if no single change dominates.
How do you simulate a lognormal 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 lognormal distribution has the same parameters μ and σ everywhere and delivers the same results. Risk Kit also offers two alternative parametrisations, which we show below.
So far we have calibrated the distributions with data. So we did not determine its parameters ourselves, we derived them from data using an algorithm. But how can you parametrise the lognormal distribution as an expert? In Risk Kit this is done in the input dialog of the distribution. We enter the parameters directly in the dialog and proceed as follows:
- Select cell E5, open the button for popular distributions on the Risk Kit toolbar (“Beliebt” in the German interface) and choose the lognormal distribution. Risk Kit opens the input dialog of the function LogNormal_variate for cell E5.
- The “Parameter” field initially shows “Standard”. This is the textbook parametrisation with the fields Mu and Sigma.
The standard parametrisation with Mu and Sigma
As explained above, Mu and Sigma describe the normal distribution of the logarithmic growth factor. For the growth factor of a single day in our growth process, these would be Mu = 0.03% and Sigma = 1.5%.
In practice, however, this representation is very opaque for experts, because the results depend extremely on these numbers. If I enter 1 for both Mu and Sigma, I get a lognormal distribution that lies roughly between 0 and 350. But if I enter 10 for both, it does not go up to 3,500, it goes into absurd orders of magnitude with more zeros than I have ever seen. Small changes to these parameters lead to gigantic changes in the result. This is so sensitive that it is useless for experts. That is why Risk Kit offers alternative parametrisations.
Expectation and standard deviation
That is why we choose another version in the “Parameter” combo box, for example “Erwartung + Standardabw.” (expectation + standard deviation). The fields are then called E and S. In this parametrisation, E and S are the expected value and the standard deviation of the lognormal distribution itself, that is, of the result. Here we say that we want 1 million as the expected value, with a standard deviation of 500,000.
Figure 6 shows the dialog with these values. The Excel name box at the top left shows E5, the cell for which the dialog was opened. On the left of the dialog are the “Parameter” field with “Erwartung + Standardabw.” and, below it, E and S. In the middle, Risk Kit shows the density of the distribution, headed LogNormal2_variate. So the function changes with the parametrisation: “Erwartung + Standardabw.” is LogNormal2_variate, “Perzentil + Konfidenzniveau” is LogNormal3_variate (Figure 7). In Figure 6, however, the title bar and the description of the dialog still show the standard version LogNormal_variate with the parameters mu and sigma. Below it is the formula of the density shown, =LogNormal2_pdf(x;1000000;500000;;). On the right are the statistics. The expectation (“Erwartung”) is 1,000,000.00, and the maximum (“Max.”) shows the symbol ∞.
Figure 6: Lognormal distribution with an expected value of 1 million and a standard deviation of 500,000. The horizontal axis runs up to 8.5 million. The table from Figure 1, with newly drawn random numbers, is still visible in the background (German interface).
It is important that I always get immediate visual feedback on what the distribution looks like. The chart in Risk Kit shows the range up to an event of 1 in 1 million. As said above, the distribution is always positive and right-skewed and can extend far out. That is why the horizontal axis in Figure 6 only ends at 8.5 million: the distribution exceeds this value only once in a million simulation runs.
Expected value, percentile and confidence level
The second alternative parametrisation is called “Perzentil + Konfidenzniveau” (percentile + confidence level). It takes the expected value E as well as a percentile Q with the confidence level C. So in addition to the expected value of 1 million, I can say that I am sure the value stays below 3 million, with a probability of 99%. Risk Kit then shows the corresponding lognormal distribution.
With this parametrisation you have to be careful, though. There is a relationship between the quantile for a given confidence level and the expected value, and the two cannot be arbitrarily far apart. If I raise the quantile to 14 million, for example, the chart suddenly extends into the billions. Here, too, the visual feedback from the chart is very important.
If I go even higher, at some point it is no longer possible to fit a lognormal distribution through these values. Here we reach the limit of feasibility. With a quantile of 15 million, Risk Kit reports next to the field that Q is too large compared with e to be the c-percentile of a lognormal distribution.
How do you truncate a lognormal distribution?
We have seen that the lognormal distribution can potentially take values so large that they no longer represent a realistic order of magnitude for our application. Let us assume, for example, an expected value of 1 million and a 99% quantile of 5 million. Then the distribution goes beyond 40 million if you do not slow it down.
Here it can make sense to rescue the distribution by truncating it. In the Risk Kit input dialog this works as follows:
- At the bottom left of the dialog, click the small arrow next to the symbol with the distributions and choose “Abschneiden” (truncate).
- The dialog now shows the additional fields “Untere Grenze” (lower bound) and “Obere Grenze” (upper bound).
- Enter the value 15000000 under “Obere Grenze”. So the distribution should not become larger than 15 million.
Figure 7 shows the result. On the left are the parameters E = 1000000, Q = 5000000 and C = 99%, below them the empty lower bound and the upper bound of 15000000. The chart now only runs to about 15 million instead of beyond 40 million. In the statistics on the right, the maximum now shows 15,000,000.00 instead of ∞. So a maximum is inserted artificially.
Figure 7: The lognormal distribution with an expected value of 1 million and a 99% quantile of 5 million, truncated at 15 million. The expectation and the 99% quantile change only slightly (German interface).
Truncating has a slight effect on the 99% quantile. It is now 4,977,570.27 instead of 5 million and thus stays in the same order of magnitude. The same applies to the expected value, which is now 997,428.29 instead of 1 million. The probability mass that we cut off at the top is so small that it has hardly any effect on these two figures. But the distribution stays within a realistic order of magnitude. Under certain circumstances, this considerably extends the range of applications of the lognormal distribution.
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 Poisson distribution in a Monte Carlo simulation?Definition, formula and examples: how the Poisson distribution describes the frequency of independent events and when it fits.
- 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.
- 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.