Stochastic Modeling with Left Skewed/Extreme Value Distribution

[krz]

Hi guys,

This is my first post but I've been reading this forum for a while and it has been very helpful to my job (business analyst at a healthcare consulting firm).

I've run into a problem that I haven't found the answer to through hours of searching/googling so I figured there's no better place to pose the question than here.

I've built a monte carlo/stochastic model via VBA with 3 sets of raw data so I have 3 distributions as inputs. The model works moderately well.

The problem I am having is that I've assumed the 3 sets of data are normally distributed when they are not. They are skewed to the left so I am not capturing potentially catastrophic events/fat tails in my model.

The reason I used a normal distribution is because I'm limited in my ability. I just used worksheetfunction.norminv(rnd(), mu, sigma) since excel doesn't include a built in inverse function for extreme value distributions (gumbel, weibull)

The quick and easy solution is to use an addin like Crystal Ball which I have but I hate relying on addins. I'd much rather write the code myself and learn how to do it.

Cliff Notes:
How can I incorporate an extreme value/left skewed distribution into my montecarlo model?

Much appreciated guys,
Chris

 

[VoG]

Wit0ut seeing your formulas iis di8fficult to advise Try a Google for for the error

 

[shg]

Would a UDF that generates Weibull random variates help?

 

[shg]

Or via formula:

Weibull: =Scale * -LN(1-RAND()) ^ (1/Shape)

Gumbel: =Location - Scale * LN(-LN(RAND()))

 

[krz]

Or via formula:

Weibull: =Scale * -LN(1-RAND()) ^ (1/Shape)

Gumbel: =Location - Scale * LN(-LN(RAND()))

I think this may help.

I will incorporate this into my model and report back.

Thank you for the prompt reply.

 

[shg]

You're welcome, good luck.

EDIT:

Gumbel: =Location - Scale * LN(-LN(1-RAND())) (because RAND() can return 0)

 

[krz]

You're welcome, good luck.

EDIT:

Gumbel: =Location - Scale * LN(-LN(1-RAND())) (because RAND() can return 0)

I just have to estimate those parameters. I've found a few resources through textbooks and google on that though. I'll post up again if I run into any more problems.

Thanks.

 

[T. Valko]

You're welcome, good luck.

EDIT:

Gumbel: =Location - Scale * LN(-LN(1-RAND())) (because RAND() can return 0)

Somewhat related...

A few years ago I asked this question in the MS ngs:

What are the odds of RAND() returning EXACTLY 0?

Never did get a definitve answer.

In my own testing (in Excel 2007) of 100 million iterations I never got close to EXACTLY 0.

What prompted me to ask this and test it was some poster claimed they got RAND() to return EXACTLY 0 TWICE in one Excel session.