Excel Array Formula Series 14.1: Stock Returns

[Yohasakura2005]

Hi Mr Excel

Just a quick question. Why do you need to put the column in dollar sign for the weightage when you do the standard deviation?

Hope to hear from you.

Cheers

Anthony

[ExcelIsFun]

Try these videos:

Excel Basics #8: Cell References Relative & Absolute

Excel Basics #9: Mixed Cell References in Formulas

and

Excel Finance Class 104: Expected Return & Standard Deviation For 1 Stock -- Estimating Future

Excel Finance Class 105: Expected Return & Standard Deviation For Portfolio -- Estimating Future

and

Excel Magic Trick 747: Expected Returns and Standard Deviation Single Cell Array Formula

[Yohasakura2005]

Thank you. I understand what the dollar sign is for but I am not sure if it is necessary to put that dollar sign there.

[ExcelIsFun]

What minute mark are you referring to?

Also, the newer Finance videos (make 2.5 years after the one above), have more efficient methods to do the above calculation, especially Excel Magic Trick 747.

[ExcelIsFun]

If you are referring to the 2:00 or 4:00 minute mark, the Dollar signs are necessary to lock down the Probabilities. If you try it without locking the cell references, the formula will not work.

In addition, do you really understand what the dollar sign means - that it locks the formula only when the formula is copied across the columns? Perhaps you do, but if you do not, it is a trick worth learning.

[Yohasakura2005]

Ah I see what you mean. As the Excel multiply the next column by the weightage or probabilities, it moves if we do not lock the column for the weightage. Thank u.

[ExcelIsFun]

Do check it out EMT 747 - it is a much better technique to do this!

[ExcelIsFun]

You are welcome!

[tushluasfresno]

Great excel, just one question.. How do you find the "Weight" of each stock?

[ExcelIsFun]

Weight is just the proportion of each stock in portfolio.

[tushluasfresno]

Pardon of me if I'm new to this, but how do you calculate it? I would appreciate it if you respond.

[ExcelIsFun]

If stock A proportion = $100, stock B proportion = $100, stock C proportion = $100

Weight A = $100/$300 = 1/3 = 0.333

Weight B= $100/$300 = 1/3 = 0.333

Weight C = $100/$300 = 1/3 = 0.333

[testarossah]

Awesome!!

Thanks alot!

You make it look so easy :)

[ExcelIsFun]

You are welcome!

[STARR209]

this is a life saver!! Thankyou so much for sharing!

[ExcelIsFun]

You are welcome!

[zubayar]

Awesome JOB !

[ExcelIsFun]

Dear zubayar,

Thank you!

--excelisfun