Generating random lognormal distribution

[agbiggs]

I'm trying to generate a random lognormal distribution of stock returns
where the mean return is 6.5% and the standard deviation is 20%.

Previously I've done this with a normal distribution, using
NORMINV(RAND(),mean,stddev). However, since stock returns tend to be
lognormally distributed I'd like to do a lognormal distribution. This
has turned out to be tricky. When I set up
LOGINV(RAND(),ln(mean),ln(stddev)) I get the #NUM error. I believe this
is because the log of a standard deviation expressed in percent (say,
0.20) can be negative. I tried using a larger number (say, multiplying
the mean/stddev by 100 to prevent negative logs) but I'm not sure
that's right.

Does anyone know an easy way to do this? Thanks!

 

[iandjmsmith]

I'm trying to generate a random lognormal distribution of stock returns
where the mean return is 6.5% and the standard deviation is 20%.

Previously I've done this with a normal distribution, using
NORMINV(RAND(),mean,stddev). However, since stock returns tend to be
lognormally distributed I'd like to do a lognormal distribution. This
has turned out to be tricky. When I set up
LOGINV(RAND(),ln(mean),ln(stddev)) I get the #NUM error. I believe this
is because the log of a standard deviation expressed in percent (say,
0.20) can be negative. I tried using a larger number (say, multiplying
the mean/stddev by 100 to prevent negative logs) but I'm not sure
that's right.

Does anyone know an easy way to do this? Thanks!

According to the help information, you are supposed to use

LOGINV(RAND(),mean,stddev).

Ian Smith

 

[agbiggs]

THanks, Ian. I've copied and pasted from the help section. I
interpreted it to mean that if my mean value is 6.5% and stddev is 20%,
that I should enter in ln(6.5%) and ln(20%) (or use them as 6.5 and 20
to avoid negative values). But this seems to produce odd results.

LOGINV(probability,mean,standard_dev)
Probability is a probability associated with the lognormal
distribution.
Mean is the mean of ln(x).
Standard_dev is the standard deviation of ln(x).

 

[agbiggs]

For what it's worth, I've figured out my problem. The lognormal
distribution is of the gross return (i.e., 1 + the return). If you work
from this then things come out ok.