Calculating standard deviation for a portfolio

[stepan1987]

Hi guys,

I need to calculate standard deviation for a portfolio with 31 stock. I have a column with the stock names (column B), their mean return (column F), standard deviation (column G) and 31*31 correlation matrix.

Is there a convenient way to calculate this stuff?

Thanks a lot in advance!

 

[stepan1987]

ps and of course I ve got respective weights in column O

 

[stepan1987]

Any ideas guys?

 

[shg]

If range xi contains the returns and wi the weights, the weighted SD of the population is

=SQRT(SUMPRODUCT(xi, xi, wi)/SUM(wi) - (SUMPRODUCT(xi, wi)/SUM(wi))^2)

See http://www.mrexcel.com/forum/showthread.php?p=2716746 and http://www.itl.nist.gov/div898/software/dataplot/refman2/ch2/weightsd.pdf

 

[stepan1987]

shg,
Thanks but it does not account for the correlations between the returns.
The formula needs to be: squared root of (sum of (weight*return)^2 + sum of 2*weight1*weight2*return1*return2*correlation (1,2)

I have 2 columns
return1 weight1
return2 weight2


return 31 weight 31
And a correlation matrix (31x31)

1 2 .........................31
1 (1,1) (2,1) .........(1,31)
2 (2,1) (2,2)
.
.
31 (1,31) (2,31)...... (31,31)

So what how does this formula look like?

 

[stepan1987]

I got it solved successfully in case anybody would need it.

The formua that is needed is
=SQRT(MMULT(MMULT(TRANSPOSE(S$29:S$59);'Correlation matrix'!C$4:AG$34);S$29:S$59))

where S$29:S$59 represents respective weights multiplied by standard deviation
C$4:AG$34 is the correlation matrix 31x31

good luck

 

[bdonnelly713]

Stepan,

Just curious did you use historical standard deviation or did you create estimates for standard deviations based off probabilities of different returns?