Think of the cumulative distribution function of any distribution, for example, the lognormal distribution. It looks something like this:
![notion image](https://www.notion.so/image/https%3A%2F%2Fs3-us-west-2.amazonaws.com%2Fsecure.notion-static.com%2F401b4091-0094-4b9d-8355-571ad396700c%2FUntitled.png?table=block&id=9ae1a770-bf5b-425a-b84e-b7b89a6326dc&cache=v2)
Â
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))
Â