Reweighting Returns so no return is greater than 25%

1. Reweighting Returns so no return is greater than 25%

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

2. Re: Reweighting Returns so no return is greater than 25%

Welcome to the forum.

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

3. Re: Reweighting Returns so no return is greater than 25%

I have now attached the file I think

4. Re: Reweighting Returns so no return is greater than 25%

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

5. Re: Reweighting Returns so no return is greater than 25%

@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).

6. Re: Reweighting Returns so no return is greater than 25%

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

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1