Can anyone help me with creating a spreadsheet that does the following:
I want to weight how I allocate stock investments in a portfolio by a volatility percentage factor by doing the following (i.e., this example is for a 2 asset portfolio:
Using the inverse of the percentages (1 / %), sum them, and then calculate the % of the total for each asset. If you had two assets (.25 and .50 volatility), assume you want to invest twice as much in the 25% one. (1 / .25) + (1 / .50) = 4 + 2 = 6 (total). 4/6 = 2/3 for the .25 and 2/6 = 1/3 for the ,50 volatilities.)

The challenge is I would like to be able to do the above for a variable number of stocks up to a max of 40, so the spreadsheet would allow for an entry of how many stocks would make up the portfolio, and then in column A would be a column of that many volatility factor numbers, starting in A2.
For example, here’s a list of 20 volatility numbers to test:

2. Dick,

I think this is what you are after?
Dick-Y.PNG

I used some Dynamic Range Names (they will allow up to 50 entries, e.g. to row 51).
The formulas in C & E use If statements so if there is nothing in A both columns will show blanks.

Test Workbook: Dick-Y.xlsx

BTW: I assumed the sum of Volities = 1? And I forgot to format C as %.

HTH

Happy 2016

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

Dick-Y (2016-01-02)

4. RG:
Thanks! I was watching a movie with my wife, so I am sorry I am late in responding to thank you.

I'm sure it'll work, and I'll test it tomorrow morning to prove that.

Happy New Year to you and yours!
Dick

5. RG:
The sum of col. C needs to equal 1, which it does. Rather than having hard-coded numbers in col B, I changed cell B2 to =1/A2. I hope that doesn't foul anything else in your example spreadsheet.

I then tested this changed spreadsheet with 10 vol numbers and it works.
Thank you.

What this sheet will allow me to do is figure out how much money to allocate among "n" numbers of stocks, based on the input VOLs, and your calculations of the inverses.

F/U question:
If I insert a new column A (for my stock symbols) will that foul up any of your Dynamic Named Ranges, or anything else in your spreadsheet?

Thank you,
Dick

6. Originally Posted by Dick-Y
RG:
F/U question:
If I insert a new column A (for my stock symbols) will that foul up any of your Dynamic Named Ranges, or anything else in your spreadsheet?
No they will adjust automatically to the insertion of a column at the left.

7. Thank you RG for all your help.
Dick-Y

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•