Monte Carlo-type simulations

[ktwiford]

Does anyone know whether Excel has statistical functions that would allow a very basic Monte Carlo type simulation? I'm running a financial analysis and want to test the sensitivity of returns based upon fluctuations in 5-10 variables. I'd like to just put a function into the model that will allow a variable to fluctuaute within a range, based upon an assumed distribution, then have the model iterate through in order to produce an "expected" result after 1,000 iterations or so

Thanks!

 

[Harlan Grove]

Does anyone know whether Excel has statistical functions that would allow a
very basic Monte Carlo type simulation? I'm running a financial analysis and
want to test the sensitivity of returns based upon fluctuations in 5-10
variables. I'd like to just put a function into the model that will allow a
variable to fluctuaute within a range, based upon an assumed distribution,
then have the model iterate through in order to produce an "expected" result
after 1,000 iterations or so.

1,000 iterations based on 5-10 independently simulated variables isn't adequate
to produce reliable statistics. The ideal number of iterations depends on how
sensitive your formula is to changes in any of the independent variables
individually and in all combinations and what the distributions of those
variables happen to be. 100 iterations for each variable alone may give an idea
of how sensitive your formula is to each alone, but multiply this by an order of
magnitude or two to get a reasonable number of iterations for all 10 varying
independently and simultaneously.

 

[Harlan Grove]

Forgot to state that Excel does have inverse distribution functions, so it's
possible to generate pseudorandom deviates from several distributions. That
said, in Excel 2000 and prior many of these functions were poorly implemented.
You may want to check out Ian Smith's alternative library. See

http://www.google.com/groups?selm=407CAB33.8020801@no_e-mail.com