I am trying to make a monte carlo simulation with excel. Normally I would use the function NORM.INV(RAND();mean;standardeviation) to produce numbers. However this time I am trying to produce a simulation with LOG Normal distribution.
I took the LN of the numbers and calculculated the mean and standard deviation. But when I typed in the numbers with LOG.INV function in excel, it produces very large numbers.
What am I doing wrong? I would appreciate any help. Thanks.
Let $X$ be a uniform variable on $[0,1]$.
Then if $\Phi(x)$ is the cumulative distribution function of the standard normal distribution, $\Phi^{-1}(X)$ will have a standard normal distribution.
If $Y$ is log-normal distributed then $\ln(Y)$ has a normal distribution. Equivalently, if $X$ is normally distributed then $e^X$ has a log-normal distribution. So we can generate log-normal distribution by: $$ Y = e^{\Phi^{-1}(X)} $$ For excel use RAND() to generate a uniform distribution and LOGNORM.INV(X,0,1) to get the inverse of the lognormal (second argument is the mean the third one is the st.dev of the normal distribution), so the formula could be:
LOGNORM.INV(RAND(),0,1)
Alternatively, you can take
EXP(NORM.INV(RAND(),0,1))
