2
$\begingroup$

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!

$\endgroup$

1 Answer 1

3
$\begingroup$

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.

$\endgroup$
3
  • $\begingroup$ Thank you for the response; I'm giving it a go and I'll keep you updated! $\endgroup$
    – JamieC113
    Mar 6, 2022 at 18:15
  • $\begingroup$ 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 $\endgroup$
    – JamieC113
    Mar 6, 2022 at 18:23
  • $\begingroup$ @JamieC113, edited the answer to answer your additional question in the comments $\endgroup$
    – AlRacoon
    Mar 6, 2022 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.