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:
notion image
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:
  1. the y-axis is always between zero and one
  1. 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))