How do I calculate portfolio standard deviation in Excel?

Question

How do I calculate a portfolio standard deviation given a return?

If I have 3 portfolios with similar size of $300 million.

Portfolio A : 
Expected Return % / Dollar Amount : 3.78% / $11.34m, Standard Deviation : 3.88%
Portfolio B : 
Expected Return % / Dollar Amount: 3.54% / $10.62m, Standard Deviation : 3.75%
Portfolio C : 
Expected Return % / Dollar Amount : 3.20% / $9.6m, Standard Deviation : 3.48%

I would like to plot the data points for expected return and standard deviation into a normal distribution so that I will be able to calculate the standard deviation if I want a $9m expected return. Formulas for Excel would also be helpful.

D Stanley · Answer

To calculate the variance of a portfolio you also need the weights of each asset (ω(i)), and the correlation (or covariance) between each asset (ρ(ij) or COV(ij)).  From there, the formula is:

σ²(p) = ω²(1)σ²(1) + ω²(2)σ²(2) + ω²(3)σ²(3) 
      + 2ρ(12)ω(1)ω(2)σ(1)σ(2) 
      + 2ρ(13)ω(1)ω(3)σ(1)σ(3) 
      + 2ρ(23)ω(2)ω(3)σ(2)σ(3)

If you have covariances instead of correlations, the formula is:

σ²(p) = ω²(1)σ²(1) + ω²(2)σ²(2) + ω²(3)σ²(3) 
      + 2COV(12)ω(1)ω(2) 
      + 2COV(13)ω(1)ω(3) 
      + 2COV(23)ω(2)ω(3)

If you assume the correlations are all 0 (the assets are completely independent), then the last three terms go away. If you equally-weight the assets, then the formula becomes

σ²(p) = σ²(1) + σ²(2) + σ²(3) 
        ---------------------
                 9

From there the excel calculations are the same from any other normal distribution with a mean and standard deviation (which is the square root of variance).

Denis · Answer

My teacher in Financial Instruments calculated it fairly easily in the following way.
He first calculates the portfolio variance using the following formula:
=SUMPRODUCT(weights array,MMULT(Covariance matrix,weights array))
He then gets the standard deviation by taking a square root of the answer and this is the portfolio st.dev.

How do I calculate portfolio standard deviation in Excel?

2 Answers

Add your own answers!

Ask a Question