1
$\begingroup$

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.

$\endgroup$
1
  • $\begingroup$ @HerrK. Please post answers as answers. $\endgroup$
    – Giskard
    Sep 29 at 14:21
3
$\begingroup$

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

$\endgroup$
1
  • $\begingroup$ Thank you. You saved my life. :) Thanks for your help. $\endgroup$
    – Onur
    Sep 29 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.