Correlated Random Numbers

[jimenef]

Hello.. I am taking a VaR Seminar and have been asked to do a VaR Montecarlo in Excel. So as part of the process i get historical data and get a best fit to assign a distribution. After that I need to generate random scenarios. The thing is, my teacher told me the scenarios need to follow a certain correlation (which must be obtained from the historical one) so basically I need to multiply the pure ortogonal random numbers times a matrix that captures this correlation... I was thinking the historic variance covariance but dont think it is... any ideas?

Thanks very much

 

[NeedOPT]

Cholesky decomposition - Wikipedia, the free encyclopedia

 

[jimenef]

Thanks NeedOPT that was very helpful... I just need to see how to compute it in excel now

 

[NeedOPT]

compute it in excel

I hope you're kidding.

 

[jimenef]

If you mean im kidding because its too easy then pardon me im just entering the subject and am just grasping the concepts..

if you mean im kidding because you cant do it in excel than im out of luck because my teacher wants it that way...

either way im open to criticisms

 

[NeedOPT]

Excel's default RNG is terrible. If you're limiting to a few thousand (?) iterations then it's probably okay.

I believe there are macros available which will give you a less retarded RNG, but in general Excel is not built to do MC. I suggest using MatLab or C/C++ for any serious MC work.

 

[jimenef]

It is basically a pretty rudimentary exercise... something like 6 assets (3 stocks 2 bonds 1 cash) and 5 risk factors (interest rates)... we have 100 historic observations and from that he has asked me to generate the best fit and the scenarios... he has asked for something like 300 scenarios... so i guess excel can do it... i am however looking forward to learning c++ definitely... do you have any idea... right now my ideas have been to use 5 distributions and use an anderson test to see which one adjusts best... then to use a cholesky (which i dont yet know how to program) and then to program it so that it is adjustable to whichever amount of risk factors i input.. i dont think its that hard i know matlab has built in cholesky functions but excel doesnt...

 

[tobias elbert]

This may be overstretching the scope of your assignment a bit, but you could use for instances the R Com Interface plugin to hook up the R engine to Excel. You can download the plugin from here

statconn

You will also need to download R from The R Project for Statistical Computing.

It'll give you access to the entire R functionality, including packages that don't come with the base installation. Cholesky decomposition and random number generators would just be two amongst many more functions you could utilise. Works from cells as well as from within VBA. Quite like it.

This may be a far stretch for the purpose of your assignment, but interesting to know nevertheless.

 

[jimenef]

needopt you were right, excel sucks for doing this... i am trying to use the anderson test to see which distribution adjusts bests and all im getting is info on other programs that do it in two seconds jaja well i guess its part of the learning progress... tobias i checked out R and told my teacher and he told me it was superb as soon as i figured out how to do it alone in excel