Solver problem. Portfolio Optimization.

[Keltamustat]

Hi. I'm trying to optimize the stock portfolio. I have 10 stocks and I would like to choose min 5 and max 10 of them to optimize the expected return.
Obviously, 5 is the correct answer here. The minimum weight of each chosen stock is 5 % and maximum is 30 %. Everything within the constraints should
be okay at the moment. The only constrain missing is the binary. I've tried to build it but I don't get it.


Portfolio Optimization.PNG

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Keltamustat]

Great. Thanks for letting me know The attachment is now downloaded.

[Alf]

Perhaps like this? I've updated formula in cell AJ61 but you need to check formulas in range AJ62:AJ65 and update these.

Alf

[Keltamustat]

Thank you. The idea is that first I'm trying to optimize the portfolio just by using expected return. Without cells AJ62:AJ65.
When I run the solver now, It still doesn't give weights for the portfolio. The optimal portfolio should be in this case 5 stocks and the weights
30-30-30-5-5, as the min is 5 % and max 30 %.

[Keltamustat]

with 5 stocks.PNG

This is what I made manually. I guess the solver should give the same result with these constraints?

[Alf]

As you have the constraint setup like this

set_constr.jpg
solver could never give you 0% as a value for any cell in the range AJ48:AJ57.

Alf

[Keltamustat]

Okay. How can I make the solver first choose 5-10 best performing stocks that it will give 30-30-30-5-5 for these and zeros to rest of them?

[Alf]

Not sure, will have a look at it today - tomorrow.

Alf

[Alf]

I've tested a bit and if you set solver to find the the highest value of the 4 best preforming stocks you get a better solution than the result using 5 of the best stock.

solver_4.jpg

using 5 you need to specify the 5th stock to be 5%. In this case I used cell AJ51 but I could probably also used AJ57 as the return there is also 7%.

solver_5.jpg

If you don't do solver find its maximum using 4 stocks but add a binary of 1 even if the weight of this stock is 0, see line for KEMIRA.HS.csv4. So it looks like Solver uses the 5 best stocks but in reality it only uses 4 unless it is forced to use the 5th.

solver4_5.jpg

Alf

Ps If you do have problem viewing the images right click on each of them and select "Open image in new tab".

[Keltamustat]

Thank you sir! You made my day.

[Alf]

You are welcome and thanks for feedback.

Alf