By applying Cholesky decomposition to the portfolio of 4 stocks(A,B,C and D), usnig Monte-Carlo simulation and taking into account Geometric Brownian Motion(GBM) we simulate our portfolio to see possible outcomes.
Let a correlation matrix be defined as follows:
The matrix is positive defined, therefore the Cholesky decomposition is used:
Stock price movement formula’s inputs are volatility and mean. Also, current stock price will be taken into consideration:
Next step is to simulate random variables. Although we use RAND() function in Excel, other possible generators can be used to finitune the final results.
Standard normal cumulative distribution function also used to apply stock price normal distribution, so the final function is NORMSINV(RAND()) produces the following results:
Of cause, it is better to take as many as posiible steps to get better results, here is shown only first 5 steps.
These variables are used with Cholesky matrix to get correlated random variables:
Now,to clarify the results, all the values in column A are the same as in the previous picture. the values in the B column are calculated by using Cholesky matrix(the second raw) and random variable values for A and B, for example, -0.57696=0.8*(-0.87294)+0.6*0.202329.
Column C and D are calculated accordingly to the same algorithms but there are additional variable. For example, value D in the top right cell is:
Lognormal prices according to GBM formula and stock prices taken from Lognormal prices with EXP() formula are shown below:
Now we have complete algorithm to make Monte-Carlo simulation to estimate risk and possible outcomes of an investment portfolio:
1. Use a model, i.e. GBM for stock prices
2. Define correlation matrix calculated from stocks historical prices.
3. Generate random numbers
4. Estimate the output applying stock volatility, mean and Choletsky decomposition