• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Portfolio Weights

P

Palantir

New Member
Hello. I am building a excel sheet which will find the return and risk of a large number of portfolios in order to create an efficient frontier. The problem I have is:


- I want to create a large set of portfolios with random weights. Say, x go to large cap, y go to small cap, z go to bonds etc.


So how do I create a set of random numbers yet make sure that the weights add up to 1?


Really appreciate your help.
 
Not necessarily fool-proof due to the random nature, but this formula:

=RAND()*(1-SUM($A$1:A1))

copied down far enough (>15 cells) should generate a set a numbers that equal ~1.


Other idea, does the weighting have to equal 1? Perhaps you could use a weighted average?

http://chandoo.org/forums/topic/weighted-average-function
 
Last edited by a moderator:
here is my 2 pennies. if there is a column to spare, I'd go the simple way of using a helper column for % of total on the random column.


So one column has just RAND() and the bottom cell will have a total of all random numbers (will not add up to 1 in most likely scenario). then the helper column would calculate the relative weight.


In a sense, the weight will come up to 1 and is generated purely by random.
 
Luke M - I appreciated the response. The portfolio weights have to add up to 1, because they cannot exceed 100% in total.


I tried a modification of yours, but thing is, that the portfolios don't come out randomly weighted. What I mean is, I want to create a set of portfolios, say 1000 in total, with random weights in each asset. So if we sum up asset weights across the 1,000 portfolios, they should be roughly equal.
 
Fred - Could you clarify what you mean by the helper column?


To illustrate this is what I have:


Large Cap-----Small Cap----Bonds-----Real Estate---Cash-------Total--------Scenario

X%----------A%----------B%----------C%----------D%----------100%----------1

A%----------B%----------C%----------D%----------E%----------100%----------2

... ... ... ... ... .... ...

B%----------C%----------D%----------E%----------F%----------100%----------1100
 
I like Fred's idea...using the above layout, you could do it with 6 helper columns (I'll say AA:AE)

AA:AD contain a simply RAND() formula, AE contains:

=SUM(AA2:AD2)


Then, in your main table, the formulas for the percentages would be:

=AA2/$AE2


Thus, each percentage weight is calculated by figuring out how much of a random number goes into a random SUM, while still making sure all the percentages add up to 1.
 
You must log in or register to reply here.
Back
Top