Generating Correlated Asset Paths and Applying Portfolio Weights

Summary

The document describes a question about simulating correlated prices for a portfolio in Excel. It starts with independent standard normal draws, combines them using rows of a Cholesky factor to produce correlated random variables, then uses those variables with drift and volatility to construct log-price paths. The unresolved issue is where portfolio weights, such as a three-asset allocation, belong in this process.

It provides no answer or evidence about how to apply the weights. The distinction to investigate is whether the goal is to simulate each asset’s path or to calculate the value or return of the weighted portfolio: the described Cholesky step models dependence among asset returns, while the question leaves the portfolio aggregation step unspecified. The document is therefore useful as a prompt about simulation design, but it does not establish a complete weighted portfolio method or discuss assumptions such as return distributions, rebalancing, or time steps.

Key ideas

• Cholesky decomposition can transform independent normal draws into correlated random variables.
• The proposed simulation uses correlated draws with drift and volatility to generate log-price paths.
• The document asks where portfolio weights enter but does not provide a resolution.
• Asset path simulation and portfolio-level aggregation are distinct parts of the modeling task.

Tags

# Adding Asset Weights To Cholesky Output - Monte Carlo in VBA


# Adding Asset Weights To Cholesky Output - Monte Carlo in VBA












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?

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.