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.

Answer 1

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).

Answer 2

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.