wilmott.com

I'm trying to simulate stocks (may be 3 to 4 stocks) behaviour base on the monte carlo simulation. I'm not quiet sure how this could be done. is anyone done this before on the excel. Can you send this to me to nsan5@hotmail.comthank you

Hi Nipon,I've attached a file which shows how it can be done. I've used cholesky decomposition to get factors, the worked out 4 appropriately correlated random variables. email me if you don't understand something ()(I have fixed up the error in the matrix - thanks Skaca for picking it up.)

gjlipman,I've found a small error in the decomposition of the matrix (stock 3 and 4 would not have the correct correlation). The corrected file is attached...Skaca

Pls refer to the VBA code for Cholesky decomposition:Function nCholesky(Correlation) 'Creating the Cholesky Decomposition Factors Dim mRs As Single mRs = nCountRC(Correlation, True) 'Correlation.Rows.Count Dim aCholesky() As Double 'Cholesky Decomposition Matrix ReDim aCholesky(1 To mRs, 1 To mRs) As Double For i = 1 To mRs aCholesky(i, 1) = Correlation(i, 1) Next i For i = 2 To mRs For j = 2 To i If i = j Then aCholesky2 = 0 For k = 1 To j - 1 aCholesky2 = aCholesky2 + aCholesky(i, k) ^ 2 Next k aCholesky(i, j) = Sqr(1 - aCholesky2) Else aCholeskyA = 0 For k = 1 To j - 1 aCholeskyA = aCholeskyA + aCholesky(i, k) * aCholesky(j, k) Next k aCholesky(i, j) = (Correlation(i, j) - aCholeskyA) / aCholesky(j, k) End If Next j Next i nCholesky = aCholeskyEnd FunctionHowever, I think u need to validate if the correlation matrix is positive definite before passing that in. Im havent figure out how to validate it yet. Is anyone can help me out with that???RgdsRocco

There is also a very handy bit of code in PWOQF p935 to find the cholesky decomposition of a matrix.A matrix is positive definite if and only if all eigenvalues are positive. Any true correlation matrix will be positive definite - if it isn't positive definite then you have some small inconsistency. That said, if you work out all correlation coefficients using historical data, you may (due to sampling error) get some small negative eigenvalues. Do you have any functions handy to work out the eigenvalues of the matrix? If so, that is possibly the easiest way to check.

Thanks for the clarification. However, my main concern is actually how do workout eigenvalues of a n*n matrix in excel. Has anyone got an existing program for that?

Try the following free Excel add-in named PopTools, it contains what you are looking for, and a lot of other useful stuff:PopToolsTanaka

Perhaps is too late, but try http://www.geocities.com/WallStreet/9245/vba11.htm .I hope it helps

The corrected website for the vba code ishttp://www.geocities.com/WallStreet/9245/