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?

  • $\begingroup$ By earnings, do you mean Profit&Loss ? $\endgroup$
    – Alex C
    Aug 14 '19 at 23:41
  • $\begingroup$ Yes, net earnings/income. $\endgroup$ Aug 14 '19 at 23:46
  • $\begingroup$ Quick tip - if you only have 1 time step, then for you first dimension, equally space your "random" numbers between 0 and 1 (ie 0.1, 0.2, 0.3,...). It hugely reduces the variance. $\endgroup$
    – will
    Aug 15 '19 at 6:06
  • $\begingroup$ Very interesting tip, @will. What do you mean by dimension? How do we partition a time-step even further - thought it is the base intervals length? $\endgroup$ Aug 15 '19 at 8:40
  • $\begingroup$ Be aware that Excel's RAND() function is very poor. See discussion here: stackoverflow.com/questions/38891165/… $\endgroup$
    – amdopt
    Aug 15 '19 at 15:27

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
  • $\begingroup$ You shouldn't just eliminate -1, you should replace it with /100. This will produce an array of returns that are all positive yet more "reasonable". Just eliminating the -1 will produce very high numbers. $\endgroup$
    – amdopt
    Aug 15 '19 at 16:04
  • $\begingroup$ @amdopt Yes. Thank you! $\endgroup$
    – user89135
    Aug 15 '19 at 16:06

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

enjoy ;-)

  • $\begingroup$ This is consistent with my understanding, @demully. What to do if integer is negative? $\endgroup$ Aug 15 '19 at 8:43

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.