Array Formula for Standard Deviation for Portfolio

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,236
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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!
 
Last edited:
Upvote 0
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!!!!
 
Upvote 0
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.
 
Upvote 0
Dear Domenic,

Thank you for helping me to learn so much!

Talk with you again soon!
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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