Array Formula for Standard Deviation for Portfolio

[mgirvin]

Dear Most Amazing Excel Team,

I have data for calculating the Standard Deviation for a Portfolio of stocks. My data is in cells A1 to E6 and looks like this:

....... Weight 0.3 0.4 0.3
....... P( ) E(R) A E(R) B E(R) C
Boom 0.15 0.3 0.45 0.33
Good 0.45 0.12 0.1 0.15
Poor 0.35 0.01 -0.15 -0.05
Bust 0.05 -0.2 -0.3 -0.09

My Array formula, that is entered in cell B8, looks like this:

{=SQRT(((SUM(C1:E1*C3:E3)-SUM(C3:E6*C1:E$1*B3:B6))^2)*B3+((SUM(C1:E1*C4:E4)-SUM(C3:E6*C1:E1*B3:B6))^2)*B4+((SUM(C1:E1*C5:E5)-SUM(C3:E6*C1:E1*B3:B6))^2)*B5+((SUM(C1:E1*C6:E6)-SUM(C3:E6*C$1:E$1*B3:B6))^2)*B6)}

This works, but there must be a more elegant and compact way to do this?

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SQRT(SUM(((MMULT((C1:E1)*(C3:E6),TRANSPOSE(COLUMN(C1:E1)^0))-SUM((C3:E6)*(C1:E1)*(B3:B6)))^2)*(B3:B6)))

Hope this helps!

 

[mgirvin]

Dear Domenic,
I am honored that you posted such an amazing formula! Thank you very much!!!!!!!

It took me a while to post back because I had to remember my matrix algebra (lucky I keep all my old textbooks). I spent an hour doing some matrix algebra by hand so that I could get it down again.

I have a few questions about your fantastic formula:
{=SQRT(SUM(((MMULT((C1:E1)*(C3:E6),TRANSPOSE(COLUMN(C1:E1)^0))-SUM((C3:E6)*(C1:E1)*(B3:B6)))^2)*(B3:B6)))}

1) Did you use the COLUMN function so that if we inserted a column and add a new stock that the formula would update?
2) If I used the formula, { =SQRT(SUM((MMULT(C1:E1*C3:E6,TRANSPOSE(COLUMN(C1:E1)^0))-SUM(C3:E6*C1:E1*B3:B6))^2*B3:B6))}, without the parenthesis around the ranges of cells that are being multiplied, is there a disadvantage to this? I noticed in your formula that you had parenthesis around each range that was being multiplied.
3) Are you familiar with the MMULT function because you do matrix algebra or stock analysis on a regular basis?

You are awesome Domenic!!!!

 

[Domenic]

Thank you very much!!!!!!!

You're very welcome! Thanks for the feedback!

1) Did you use the COLUMN function so that if we inserted a column and add a new stock that the formula would update?

The TRANSPOSE and COLUMN functions are used to return a vertical array of 1's so that MMULT could be exploited to return the desired outcome. So, therefore...

TRANSPOSE(COLUMN(C1:E1)^0)

...could easily be replaced by...

{1;1;1}

However, when the data spans a larger number of columns it's easier to reference the columns. And, yes, the range will automatically adjust when one or more columns are inserted.

2) If I used the formula, { =SQRT(SUM((MMULT(C1:E1*C3:E6,TRANSPOSE(COLUMN(C1:E1)^0))-SUM(C3:E6*C1:E1*B3:B6))^2*B3:B6))}, without the parenthesis around the ranges of cells that are being multiplied, is there a disadvantage to this? I noticed in your formula that you had parenthesis around each range that was being multiplied.

The parenthesis around each range is not needed. I only use them for aesthetic reasons.

3) Are you familiar with the MMULT function because you do matrix algebra or stock analysis on a regular basis?

None of the above... I've become familiar with MMULT by frequenting this Board and learning from others. Definitely an interesting function, one which can be used in a variety of ways.

 

[mgirvin]

Dear Domenic,

Thank you for helping me to learn so much!

Talk with you again soon!