=1-2*rand()

[kikitinoq]

In Gerard Verschuuren's book on excel monte carlo simulations, he uses the above formula to generate brownian motion. Can someone explain what this does and its signficance? Thanks.

 

[joeu2004]

RAND() returns a value that is roughly between zero and 1. So 1-2*RAND() returns a value roughly between 1 and -1, uniformly distributed.

 

[shg]

Pretty bad simulation. More accurate for 1D motion would be =NORM.S.INV(RAND()), which is normal rather than uniform.

 

[Gerald Higgins]

shg - I'm just curious, would you care to expand on why you think a normal distribution is better for this application than a uniform distribution ?
I'm not an expert in this area at all.

I guess whatever the reason, it's important for the OP to satisfy themselves that the solution is right for their application.

 

[shg]

Almost nothing in nature has a uniform distribution, which has hard-edged limits, and all the examples I see use normal distributions. See, for example, http://cr.middlebury.edu/Watters/public_html/pages/simulations.htm, which describes a simulation as

To move each particle, two random numbers are drawn: one is used to update the x-axis position, the other to update the y-axis position. The random numbers themselves are produced from an algorithm that calculates its output according to a bell-shaped (Gaussian) curve. The peak probability of this Gaussian distribution is set at zero, with decreasing probability of obtaining either a positive or negative value further and further from zero. For the simulations used here, the Gaussian distribution for single particles, lipids and water molecules has a standard deviation (SD) of 5 units, while the Gaussian distribution for the movement of larger ion/water assemblies and proteins is much more tightly clustered around the mean, having an SD of 2 units. As with all Gaussian distributions, +/- 1 SD accounts for about two-thirds of all the values; +/- 3 SD includes 99% of all possible values.

 

[steve the fish]

Theres a quite nice youtube guide here:

https://www.youtube.com/watch?v=3gcLRU24-w0

 

[joeu2004]

Almost nothing in nature has a uniform distribution

Conversely, not every natural behavior can be described by a normal distribution, much less the standard normal distribution.

I don't know anything about Verschuuren, but his wikipage describes him as scientist in the fields of biology and genetics, having written a thesis on a statistical analysis of genetic variation. I suspect that he knows more about statistical simulation in his field than any of us. Alternatively, he might have used a uniform distribution in his examples simply to KISS.

The wikipage about Brownian motion includes an image that depicts the ``characteristic bell-shaped curves of the diffusion of Brownian particles. [.... F]or increasing times they become flatter and flatter until the distribution becomes uniform in the asymptotic time limit``.

 

[kikitinoq]

Thank you for the replies. In his simulation for stock price variation, he did use =NORM.S.INV(RAND())