# How do I calculate portfolio standard deviation in Excel?

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? Jun 7, 2017 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)
---------------------
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).

• 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. Jun 7, 2017 at 22:55
• By the way , do you have any useful url that i can read up on? Thank you Jun 8, 2017 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? Jun 8, 2017 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. Jun 8, 2017 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.