<p>Let <span class="math-container">$X$</span> be a uniform variable on <span class="math-container">$[0,1]$</span>.</p>
<p>Then if <span class="math-container">$\Phi(x)$</span> is the cumulative distribution function of the standard normal distribution, <span class="math-container">$\Phi^{-1}(X)$</span> will have a standard normal distribution.</p>
<p>If <span class="math-container">$Y$</span> is log-normal distributed then <span class="math-container">$\ln(Y)$</span> has a normal distribution. Equivalently, if <span class="math-container">$X$</span> is normally distributed then <span class="math-container">$e^X$</span> has a log-normal distribution. So we can generate log-normal distribution by:
<span class="math-container">$$
Y = e^{\Phi^{-1}(X)}
$$</span>
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:</p>
<blockquote>
<p>LOGNORM.INV(RAND(),0,1)</p>
</blockquote>
<p>Alternatively, you can take</p>
<blockquote>
<p>EXP(NORM.INV(RAND(),0,1))</p>
</blockquote>

CC BY-SA 4.0
2021-09-29T17:46:19.927Z
Thank you. You saved my life. :) Thanks for your help.
Onur
CC BY-SA 4.0
2021-09-29T14:27:43.873Z
2021-09-29T14:27:43.873Z
tdm
<p>I am trying to make a monte carlo simulation with excel. Normally I would use the function <code>NORM.INV(RAND();mean;standardeviation)</code> to produce numbers. However this time I am trying to produce a simulation with LOG Normal distribution.</p>
<p>I took the <code>LN</code> of the numbers and calculculated the mean and standard deviation. But  when I typed in the numbers with <code>LOG.INV</code> function in excel, it produces very large numbers.</p>
<p>What am I doing wrong? I would appreciate any help. Thanks.</p>

CC BY-SA 4.0
2021-09-29T14:21:03.693Z
@HerrK. Please post answers as answers.
Giskard
<simulations>
How do we simulate with lognormal distribution in excel?
Onur
# How do we simulate with lognormal distribution in excel?

(Asked by: username_0 on 2021-09-29)

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.

**Tags:** simulations
> **Comments:**
> **username_1**: @HerrK. Please post answers as answers.  

---

## 1 answers

### Answer by username_2 on 2021-09-29. Score: 3 | Accepted answer

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

> **Comments:**
> **username_0**: Thank you. You saved my life. :) Thanks for your help.  

---

