Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    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:

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    Last edited by RetiredGeek; 2016-01-02 at 14:04.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Dick-Y (2016-01-02)

  4. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    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

  5. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    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. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Dick-Y View Post
    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.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    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
  •