Monte Carlo Simulation

**Dgatto** · 11-13-2022, 09:24 AM

I have a Monte Carlo risk simulator in my financial spreadsheet. It matches very closely to online freebies for Median and Percentile(10th, 90th) values. I use arrays for inputs and outputs to improve speed so I can run 1000 scenarios very quickly.
If I set the standard deviation to near zero for both Rate of Return (ROR) and Inflation (INF), I would expect the output to match a fixed model that calculates standard future values with fixed ROR and Inflation. The statistical model set to near zero standard deviation (.00001) always under estimates the simple year by year model. Errors get pretty large after 20 years of iterations. Behavior may be "just the way excel NormInv statistics works", but it bugs me.

ROR = Application.WorksheetFunction.NormInv(Rnd, RORm, RORsd)
Inf = Application.WorksheetFunction.NormInv(Rnd, INFm, INFsd)

This may be more of a statistics question than an Excel question. Sorry, I'm a newbie.

**MrShorty** · 11-13-2022, 11:34 AM

I know past versions of excel (almost exclusively pre-2007) have been criticized for problems with some of its statistical functions. (Random number generators that fail randomization tests and inefficient and unstable algorithms in the standard deviation and regression algorithms for example.) But I've not heard of problems with Excel's normal distribution functions -- especially in the newest versions. Which isn't to say it is impossible that Excel's implementation of the normal distribution isn't problematic, just that I'm unaware of any problems.

I'm not sure I can contribute much else. You haven't shared any details of your model (perhaps someone with expertise in your field needs no more detai?), So I'm not sure how one would go about debugging this so that one could determine a problem with Excel's normal distribution functions. We might have a couple with the necessary expertise. The rest of us would require more details in order to understand the problem.