Correlation Matrix to Variance Covariance Matrix Portfolio STDEV

Question

I have a correlation matrix that I wanted to convert into a variance covariance matrix. I also have the weights in a column in excel along with each assets standard deviation. What excel function can I use to get a variance covariance matrix or portfolio standard deviation if I only have the correlation matrix with weights?

Thank you!

Answer 1

Since

$$Cov_{x,y} = Corr_{x,y} * \alpha_{x} * \alpha_{y}$$

To create a variance-covaariance matrix, create another matrix (with the same dimensions as your correlation matrix), where in each cell you multiply the corresponding correlation from your correlation matrix with the standard deviation of asset x and the standard deviation of asset y. You can use vlookup to pull in the standard deviations from your vector of asset standard deviations.

From this covariance matrix, you can calculate the portfolio variance by multiplying this matrix with the weights vector twice (W^2). The portfolio standard deviation is just the square root of the portfolio variance.

Portfolio Variance: =MMULT(TRANSPOSE(weight_vector),MMULT(covariance_matrix,weight_vector)) ; where weight_vector is the cell reference for the column of portfolio weights, and covariance_matrix is the cell reference of the variance/covariance matrix calculated above.

Don't forget to use CTRL-SHIFT-ENTER to enter the above formula to enter into matrix math mode in Excel.

Take the square root of the Portfolio Variance to calculate the Portfolio Standard Deviation.