# Log normal price simulation

I'm trying to figure out a spreadsheet I have which simulates 50000 returns in excel using the following function:

LOGNORM.INV(RAND(),0,0.35)-1


Question:

How does adding a minus 1 to the end of this formula convert what is essentially a log normally distributed price to a normally distributed return?

• Subtracting one doesn't convert it to a normally distributed return. – Matthew Gunn Aug 24 '17 at 19:10

## 2 Answers

To my knowledge, adding a minus 1 does not transform a log normal variable into an normal distributed variable. The only thing that I can think of which make sense is the log normal represents a price ratio $\frac{P_t}{P_0}$ (for instance if the price process is a geometric browninan motion). In this case adding -1 does transform price ratio into arithmetic return $R_t=\frac{P_t - P_0}{P_0} =\frac{P_t}{P_0}-1$. if you make the assumption that $P_0=1$, then indeed $R_t=P_t-1$. But in any case, the return does not follow normal law.

To be precise, that code generates draws from a shifted lognormal distribution.

Define $R_t = \frac{P_t + D_t}{P_{t-1}} - 1$ as the return from $t-1$ to $t$.

Define $r_t = \log \left( 1 + R_t \right)$ as the log return from $t-1$ to $t$. (Note if $D_t=0$ then $r_t = \log P_t - \log P_{t-1}$.)

Your code above generates returns where the corresponding log return follows the normal distribution.

As a practical matter, the linear approximation of $f(x) = \log(1+x)$ around $x=0$ is given by $x$, hence for $r_t$ near zero, we have $r_t \approx R_t$. If a return is .02, the log return is .0198. (Of course, this breaks down the farther one is from zero.)