Below are 4 models that consistent of different asset data. These models assess the standard deviation of each security based on daily return data, create correlation and covariance matrices, and calculate portfolio expected return and variance / standard deviation based on varying weights of each asset. By defining constraints as a non-linear optimization model, we are able to solve for the optimal mix of securities to either maximize return given a minimum portfolio standard deviation, or minimize risk given a required return.

The four models are broken down as follows:

Here's a look at the 200 ETF Model. The data in input into the top-left section of the worksheet, and all of the the following fields will calculate automatically. The matrices are dynamic, so this model can be used on any number of securities over any historical timeframe.

Note: there are thousands of cells hidden to simplify the model.

Feel free to download the files below!

Portfolio Variance is calculated as follows: