So if I have a portfolio of 30 stocks in excel and I wanted to see the portfolio standard deviation, how would I do this? Also if I wanted to see how it would change if I added more securities/ changed securities how would I do that?
name your weight vector wgt and your covariance matrix sigma portstd=(mmult(mmult(transpose(wgt),sigma),wgt))^.5 if wgt is nX1. if it is 1Xn use portstd=(mmult(mmult(wgt,sigma),transpose(wgt)))^.5 If you add more securities, add more zeros to the end of the wgt vector. Set up the covariance matrix so that if you add in more data, the covariance matrix is automatically filled. Tricky because excel covariance isn’t an array function, but there are multiple ways to do this. Then rename the named ranges to incorporate the new data. This is an approximation. The derivative of portfolio standard deviation is (sigma*w)/(w’*sigma*w)^.5. If you start with some portfolio w, calculate that derivative (should be a vector, let’s call it m_std), then find m_std’*a where a=w_new-w is an nX1 vector of the difference between some new weights and the old weights. For instance if you go from w=[60% 40%]’ to w_new=[65% 35%]’, you use a=[5% -5%]’.
Awesome thanks!