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



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?

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

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


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.