# Correlation Matrix to Variance Covariance Matrix Portfolio STDEV

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!

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.

• Thank you for the response; I'm giving it a go and I'll keep you updated! Mar 6, 2022 at 18:15
• What excel function should I use to do the second part: multiplying the matrix by sqrt weights? The function =MMULT(CorrelationMatrix,Sqrt(WeightsArray) is giving me a column array instead of a number and =sumproduct is giving me a #VALUE error Mar 6, 2022 at 18:23