Reweighting Returns so no return is greater than 25%

[Seanp2000]

Hi,

I have 30 stock returns but one of the stocks has a a weight of 49%. I dont want any stock to have a weight greater than 20%. How do I reduce the one stocks weight to 20% and redistribute the remaining weight to the other stocks?

Thanks
Sean

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Seanp2000]

I have now attached the file I think

[Seanp2000]

The column with Stock Weight is the one which I want to reweight with no stock over 20% of the weight and then all the others get reweighted

[joeu2004]

@Seanp2000, see the attached file. Presumably, the formula in G2 was F2/$F$32. The formula now is:

=MIN($I$2, (1 - SUM($G$1:G1)) * F2/SUM(F2:$F$31))

where I2 contains the max weight.

The formula prorates the remaining total percentage in proportion to the remaining market cap.

FYI, with your example, if the max weight is 11.62% or less, the relative rank of the sector weights changes. See columns M, P and Q.

PS.... Because you replaced formulas with their results, changes in column G might not be reflected in dependent columns. I did reverse-engineer the formulas in the sector weights column (L).

[Seanp2000]

!Thanks a million Joeu2004. That is exactly what I wanted. Genius!