I am looking to create a Monte Carlo generator in Excel to plot correlated asset paths for a portfolio containing 1 to 10 assets. I have the correlation matrix for all 10 assets and have performed the Cholesky Decomposition to obtain the lower NXN matrix output using some VBA code.

I am looking for some guidance on when/how to incorporate the asset weights of the portfolio into my path generation.

As an example for a three asset portfolio I generate three separate series of random variables using the NORMSINV(RAND()) = RN function to return the sigma. Then multiply each random variable by the corresponding cholesky output and sum the series to get a correlated random variable.

CRV (correlated ran. variables) = =RN1*chol1 + RN2*chol2 + RN3*chol3

I then found instruction on setting up your drift and volatility terms to generate a series of the log of prices factoring in the above input.

Log of Prices=LN(Starting Price)+(Drift-0.5*volatility * volatility) + volatility*CRV

Where do I factor in my asset weights? Let's say 20/30/50 for a three asset portfolio?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.