r/excel•4 yr. ago

Solver tool with multiple objectives (portfolio optimization)

solved

I have seen more posts on this subject, but the people asking these questions were very unclear, so I am going to try and be as clear as possible. Short story: I got 5 mutual funds in 1 portfolio and my goal is as following (it's a school project btw):

- Maximum Sharpe ratio

- Maximum return

- Minimum risk (stdev)

The 'changing by variable cells' is bound to the weight, so the % I am going to invest in each mutual fund. The minimum is 5%. The problem I have is that I can only select one 'set objective'. Funnily enough the maximum Sharpe ratio and the minimum risk (stdev) share the same weights, but the maximum return does not. Is it possible, in any way, to use 2 or 3 objectives instead of 1? This way I have the maximum return, minimum risk and maximum Sharpe ratio, I know the maximum return will never be the real maximum if I do it this way, but I need some kind of average.

Changing the constraints won't really help, although it is the last option if I can't find the solution to my problem. For better understanding I will post a screenshot as well . Thanks in advance guys!

Excel version -> latest

My Excel sheet + solver options

Posted Dec 23, 2021, 4:44 PM UTC · edited

Sort by:

AutoModeratorMOD•4 yr. ago

u/BeNiceToAll - Your post was submitted successfully.

Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

not_speshal461•4 yr. ago

Shouldn't maximizing the Sharpe Ratio be sufficient? Since the numerator is the Return and denominator is the Risk, maximizing the SR automatically gives you the optimal solution for maximizing Return and minimizing Risk.

You can't (AFAIK) have multiple objectives in Solver.

BeNiceToAllOP•4 yr. ago

I asked this question mainly because my teacher demanded the 3 goals, but I think you are right. Maximizing the Sharpe ratio should be the best either way. I'll discuss this in detail with my teacher and I'll keep the post flagged as not solved just in case someone does know a way to do it. Thanks for replying!

not_speshal461•4 yr. ago

Yes I think the sharpe ratio implicitly covers the other two. If you think of a typical plot of risk on the x-axis vs return on the y-axis, there’s only so much risk you can minimise for a particular level of return and vice versa. But you should cross-check :).

P.S. Unless I’m lost in translation, the results in top table (rows 52-60) and different from the bottom table (rows 64-72)