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))**Â