Results 1 to 6 of 6
Thread: Spreadsheet request

20160102, 12:29 #1
 Join Date
 Dec 2009
 Location
 Pittsford,NY
 Posts
 898
 Thanks
 543
 Thanked 35 Times in 27 Posts
Spreadsheet request
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:

20160102, 13:00 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,664
 Thanks
 390
 Thanked 1,510 Times in 1,371 Posts
Dick,
I think this is what you are after?
DickY.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: DickY.xlsx
BTW: I assumed the sum of Volities = 1? And I forgot to format C as %.
HTH
Happy 2016Last edited by RetiredGeek; 20160102 at 13:04.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
DickY (20160102)

20160102, 20:29 #3
 Join Date
 Dec 2009
 Location
 Pittsford,NY
 Posts
 898
 Thanks
 543
 Thanked 35 Times in 27 Posts
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

20160103, 10:21 #4
 Join Date
 Dec 2009
 Location
 Pittsford,NY
 Posts
 898
 Thanks
 543
 Thanked 35 Times in 27 Posts
RG:
The sum of col. C needs to equal 1, which it does. Rather than having hardcoded 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

20160103, 12:20 #5
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,664
 Thanks
 390
 Thanked 1,510 Times in 1,371 Posts
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20160103, 13:51 #6
 Join Date
 Dec 2009
 Location
 Pittsford,NY
 Posts
 898
 Thanks
 543
 Thanked 35 Times in 27 Posts
Thank you RG for all your help.
DickY