efficient frontier, what if and data tables

[nickmangan]

Hi

I would be grateful for your help.

Attached is a spreadsheet with the prices, returns, correlation and standard deviation of a portfolio of investments. I am able to produce the standard deviation for the portfolio but want to run a simulation to see what allocation produces the lowest standard deviation and maximum return. I have a worksheet titled "Simulation" which has 50 different permutations of asset allocation but I am trying to find a way of solving the standard deviation for each one. I am told that a Data table function/what if would work, but I don't know how. I am trying to solve column M in the simulation worksheet and would be grateful for any help.

Many thanks

Nick

[Pauleyb]

This is quite an undertaking. I'm curious why you are doing this yourself, as I'm sure there are some online tools that will do this for you. I know this sounds contemptuous, but if you are struggling with this part, then you may need to just use the online tools, as there will be more math/methods to find the efficiency curve for the portfolio. I'm thinking this is a homework assignment, and in those cases I try to help 'push' but I don't provide the outright answer...

I see you have your correlation matrix, you'll also need a covariance matrix. You'll use the covariance matrix along with the weights to determine the each weighted portfolio's variance.