How to generate a random number from a lognormal or any other non-uniform distribution?

Think of the cumulative distribution function of any distribution, for example, the lognormal distribution. It looks something like this:

The cumulative probability starts at zero and grows up to one. What we want to get is a random number along the x-axis. But notice two things:

the y-axis is always between zero and one

for every y-value, there is a unique corresponding x value

The trick is then to pick a uniformly random number between zero and one as the cumulative probability, and then find the matching x value that generates that probability. In Excel 2010, the formula would look like this: =LOGNORM.INV(RAND(),0,0.2)

This will generate a random logreturn from a 20% vol distribution

The Excel LOGNORM.INV function

Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters Mean and Standard_dev. If p = LOGNORM.DIST(x,...) then LOGNORM.INV(p,...) = x.

Use the lognormal distribution to analyze logarithmically transformed data.

LOGNORM.INV(probability, mean, standard_dev)

The LOGNORM.INV function syntax has the following arguments:

Probability Required. A probability associated with the lognormal distribution.

Mean Required. The mean of ln(x).

Standard_dev Required. The standard deviation of ln(x).

🔹

To simulate a daily return, we need to scale the volatility down to a single day. The Excel 2010 code for this is: =LOGNORM.INV(RAND(),0,0.2/SQRT(252))