Calculating standard deviation for a portfolio

stepan1987

Board Regular
Joined
May 6, 2011
Messages
92
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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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?
 
Upvote 0
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
 
Upvote 0
Stepan,

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

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top