modeling Monte Carlo with non-standard distribution

[nbirnbaum]

Hello everybody,

I'm using the NORMINV() function to calculate likely random outcomes of specific variables in a Monte Carlo simulation. (Modeling this after the example from the excellent book, How To Measure Anything.)

However, NORMINV() uses standard distribution. What if my distribution is not standard?


For example, I'm doing a Monte Carlo for how much a house might sell for. The max expected value is $950,000 and the min value is $850,000. However, the likeliest outcome is not in the middle, but slightly lower -- $875,000 rather than the mean of $900,000.

If it was a standard distribution, I would use the formula =NORMINV (RAND(), mean sale price, (max price - min price)/3.29)

So I can't use NORMINV to calculate this. Any idea how I would model this calculation instead?

 

[joeu2004]

Then you don't use NORMINV. What you do use depends on the description of the complete distribution. You did not post sufficient details for us to be more specific.

 

[nbirnbaum]

Then you don't use NORMINV. What you do use depends on the description of the complete distribution. You did not post sufficient details for us to be more specific.

I don't know enough about how to describe distributions. Can you help?

 

[joeu2004]

I don't know enough about how to describe distributions. Can you help?

Truthfully, probably not, if only for lack of time.

I would start with: what is the purpose of your interest?

If it is a class assignment, you are limited by the problem statement and parameters of the assignment, and reality has nothing to do with it. In that case, I would suggest that you post all information provided by the assignment.

On the other hand, if it is for professional reasons with a client depending on the results, it is important that you get correct information. The internet is not a reliable resource. If you are not savvy enough to separate good and bad guidance, it would behoove you to defer to someone who is, ideally a professional. "You get what you pay for".

But if it is just for personal interest, this is might be a good place to start. However, your question sounds more like a statistics forum question than an Excel forum question. Oh well....

As for the distribution, that depends on the data that you have.

A distribution might be discrete. For example, x% between 925,000 and 950,000; y% between 900,000 and 925,000; etc.

Or a distribution might need to be derived from historical data. If it is short (say, 20 lines or less), post it here. Otherwise, I would suggest that you upload an Excel file to a file-sharing website (e.g. box.net/files), and post the public/share URL here. Test the URL first, being careful to log out of the filie-sharing website.

Caveat: Some people in this forum object to that suggestion. That's fine: they don't have to participate. But the fact is: that might be the best way to communicate large amounts of data and/or complex implementations like a Monte Carlo simulation, IMHO.

If it was a standard distribution, I would use the formula =NORMINV (RAND(), mean sale price, (max price - min price)/3.29)

Why would you think that?! In particular, why would the std dev be (max-min)/3.29?!

For a normal distribution, we usually consider the max and min to be the mean +/-3 or +/-4 sd. In that case, sd would be (max-min)/6 or (max-min)/8.

Perhaps you meant to write (max-mean)/3.29. Even then, 3.29 is an unusual z-score to use, IMHO. Arguably, it covers 99.90% of the data v. 99.73% for +/-3 sd and 99.99% for +/-4 sd.