Valuation of investment portfolios, structured products and different strategies that consist of several underlying assets may require advanced methodology and algorithms.
Monte Carlo technique that includes valuation algorithm is often used in such cases, especially if there is no closed form solution for option pricing.
Standard inputs parameters to Monte Carlo process are:
1. Correlation/covariance matrix
2. Volatility of underlyings
3. Drift and starting price
4. Time
5. Assumption that prices are lognormally distributed and follow geometric Brownian motion

Random variables can be simulated in Excel, using NORMSINV(RAND)) functions.
The RAND() function generates random numbers, while NORMSINV() returns the inverse of the standard normal cumulative distribution.
Monte Carlo process can be implemented in VBA code by applying N steps simulated paths in a while loop.
Correlation matrix is used in the Cholesky decomposition along with random variables taken from NORMSINV(RAND)) functions to generate correlated random variables.
Cholesky decomposition assumes that every positive definite matrix A can be factored as multiplication of lower triangular matrix having positive diagonal elements with its inverse matrix(upper triangular).
There is no mystery and for simplicity case, here is an example:

Simple matrix multiplication(Correlation matrix A = Lower triangular matrix L * Upper Triangular) helps to understand what happens.
A11= L11*L11+0*0+0*0+0*0
A21= L21*L11+L22*0+0*0+0*0
A31= L31*L11+L32*0+L33*0+0*0
A41= L41*L11+L42*0+L43*0+L44*0
A22= L21*L21+L22*L22+0*0+0*0
A32= L31*L21+L32*L22+L33*0+0*0
A42= L41*L21+L42*L22+L43*0+L44*0
A33= L31*L31+L32*L32+L33*L33+L44*0
A43= L41*L31+L42*L32+L43*L33+L44*0
A44= L41*L41+L42*L42+L43*L43+L44*L44

Cholesky decomposition means that correlation matrix A is known and L should be found.
The above calculations are made in reverse, finding L values instead of A values.
L11= sqrt(A11)
L21= A21/L11
L31= A31/L11
L41= A41/L11
L22= sqrt(A22-L21*L21)
L32= (A32-L31*L21)/L22
L42= (A42-L41*L21)/L22
L33= sqrt(A33-(L31*L31+L32*L32))
L43= (A43-(L41*L31+L42*L32))/L33
L44= sqrt(A44-(L41*L41+L42*L42+L43*L43))
All the values from the Lower triangular matrix are found and this calculation is known as the Cholesky algorithm.

Let’s assume we have a correlation matrix of 4 underlying assets:

Using Cholesky decomposition, the lower triangular matrix is:

The first column calculation:
1.00=sqrt(1)
0.80=0.8/1.00
0.20=0.2/1.00
0.50=0.5/1.00
The second column calculation(numbers rounded two the second decimal):
0.60=sqrt(1-0.80*0.80)
0.57=(0.5-0.2*0.8)/0.6
0.17 = (0.5-0.5*0.8)/0.6
The third column calculation(numbers rounded two the second decimal):
0.80=sqrt(1-(0.20*0.20+0.57*0.57))
0.38=(0.5-(0.50*0.20+0.17*0.57))/0.80
The last column calculation(numbers rounded two the second decimal):
0.76=sqrt(1-(0.50*0.50+0.17*0.17+0.38*0.38))