# 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?

• By earnings, do you mean Profit&Loss ? Aug 14 '19 at 23:41
• Yes, net earnings/income. Aug 14 '19 at 23:46
• 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.
– will
Aug 15 '19 at 6:06
• 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? Aug 15 '19 at 8:40
• Be aware that Excel's RAND() function is very poor. See discussion here: stackoverflow.com/questions/38891165/… 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

• 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. Aug 15 '19 at 16:04
• @amdopt Yes. Thank you! 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 ;-)

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