Converting Correlations into Portfolio Covariance and Volatility
Summary
The document explains how to turn a correlation matrix into a covariance matrix when asset standard deviations are available. For each asset pair, multiply their correlation by the standard deviation of each asset; the resulting entries form a covariance matrix. The same calculation includes diagonal entries, where an asset’s correlation with itself is one, giving its variance.
To obtain portfolio variance, apply the weight vector on both sides of the covariance matrix, then take the square root to get portfolio standard deviation. The example gives an Excel matrix formula and notes that older Excel versions may require array-entry mode. This method assumes the correlations, standard deviations, and weights refer to compatible assets and periods. The document offers spreadsheet guidance rather than a worked numerical example, and it does not discuss estimation error, changing correlations, or whether the inputs adequately capture portfolio risk.
Key ideas
- Pairwise covariance equals correlation multiplied by the two assets’ standard deviations.
- The covariance matrix uses the same asset ordering as the correlation matrix and standard-deviation vector.
- Portfolio variance is calculated by applying the weights on both sides of the covariance matrix.
- Portfolio standard deviation is the square root of portfolio variance.
- Excel array-entry requirements depend on the version being used.
Tags
Full text
# Correlation Matrix to Variance Covariance Matrix Portfolio STDEV
# 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!
## Answer by AlRacoon (score 3, accepted)
https://quant.stackexchange.com/a/70091
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.Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.