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.

  • You need correlations and weights to calculate a variance. Do you know the correlations between the portfolios or would you assume they are completely independent (correlation = 0)? Is the portfolio equally weighted between the three sub-portfolios? – D Stanley Jun 7 '17 at 18:47

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) 

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

  • Thanks everyone for your input. Appreciate it.Currently, The 3 portfolios have similar weights. Eg (80% bonds and 20% equity). As for the mean, do i just average out the 3 return (11.34+10.62+9.6)/3 ? Many thanks. – Fabian Tan Jun 7 '17 at 22:55
  • By the way , do you have any useful url that i can read up on? Thank you – Fabian Tan Jun 8 '17 at 0:27
  • @FabianTan I'm not talking about the weights within portfolios A, B, and C - I'm asking about how much of each portfolio are in your composite portfolio - Do you have equal amount of A, B, and C in your portfolio? – D Stanley Jun 8 '17 at 13:50
  • @FabianTan Correlated variance is a pretty basic concept in Modern Portfolio Theory. Just google it and you should get plenty of material. – D Stanley Jun 8 '17 at 13:52

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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