Why Subtracting One from a Lognormal Price Ratio Gives Simple Return
Summary
The document explains a spreadsheet simulation that draws a lognormal value and subtracts one. The key interpretation is that the lognormal draw can represent a gross price ratio, such as the ending price divided by the starting price. Subtracting one converts that ratio into a simple arithmetic return; it does not make the result normally distributed.
The response distinguishes simple return from log return, defined as the logarithm of one plus the simple return. Under the stated setup, the log return is normal while the simple return follows a shifted lognormal distribution. For small returns, the logarithm is approximately equal to the simple return, but this approximation becomes less accurate as returns grow in magnitude. The spreadsheet function’s parameters and assumptions are not investigated further, so the exact simulated distribution depends on how those inputs are configured.
Key ideas
- A lognormal draw can represent a gross price ratio rather than a return directly.
- Subtracting one from a gross price ratio produces a simple arithmetic return.
- A normal log return generally corresponds to a non-normal, shifted lognormal simple return.
- Simple and log returns are close for small returns, but the approximation weakens for larger moves.
Tags
Full text
# Log normal price simulation
# 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?
## Answer by loxol (score 2)
https://quant.stackexchange.com/a/35760
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.
## Answer by Matthew Gunn (score 1)
https://quant.stackexchange.com/a/35772
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.)Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.