Skip to content
All library documents

Using Lognormal Returns to Keep Monte Carlo Prices Positive

Article Quant Q&A · Author: Geoffrey Turner
Read the original

Summary

The question concerns simulating futures prices in Excel when a normal draw applied directly to prices can produce impossible negative values. The responses recommend modeling returns first, then deriving prices from those returns. One describes drawing a normally distributed shock and applying it in an exponential return transformation; another describes generating returns from a mean and volatility, then updating the prior price multiplicatively.

This approach makes simulated prices positive by construction and is a basic lognormal-style alternative to adding normal noise directly to price levels. The discussion is brief and gives no validation, calibration guidance, or comparison with other price models. It also does not resolve the question’s concern about stationarity, and the assumptions behind the return distribution may be unsuitable for some futures or time horizons. The snippets are examples of a modeling setup, not evidence that it accurately forecasts or represents observed prices.

Key ideas

  • Directly adding normally distributed shocks to price levels can produce negative simulated prices.
  • Modeling returns and compounding them multiplicatively keeps prices positive.
  • An exponential transformation of a normal shock gives a lognormal-style return model.
  • The discussion does not assess calibration or the suitability of the assumptions for particular futures.

Tags

FuturesStatisticsBacktesting
Full text
# Single-step Monte Carlo in Excel


# Single-step Monte Carlo in Excel












How do you simulate correctly using raw prices not returns?

I have corresponding periods of earnings to Futures but the Excel call function =NORMINV(RAND(),mean,stdev) generates negative Futures prices?

Ignoring stationarity issues, how to create a random variable of positive integers?

## Answer by user89135 (score 2)

https://quant.stackexchange.com/a/47131

The code below is part of a VBA project I did to calculate VaR with Monte Carlo returns. If you eliminate the `-1` at the end all values are positive. You just need to add your own risk free and standard deviation. Excel RAND() is same as VBA RND().

```
For i = 1 To 10000
        stockReturn(i) = Exp((RiskFree - 0.5 * StDv ^ 2) + StDv * Application.NormInv(Rnd(), 0, 1)) - 1
Next i
```

## Answer by demully (score 0)

https://quant.stackexchange.com/a/47123

You need for Returns = normsinv(rand()) * Sigma + Mu for each period. Then Price = e(Price-1 + Return) for the Monte Carlo price

enjoy ;-)

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.