Help needed with SOLVER output (investment portfolio)

Hi everyone, first time poster here. I have played around with this for quite some time by myself, but cannot get solver to work for me, so I thought I would reach out to some gurus online. I am quite confident it is a simple solve (pun intended ).

Overview/Details:
- I have created a spreadsheet to keep track of my investment portfolio, with the idea being that every once in a while, I would use Solver to tell me the optimal amount of each fund I should purchase in order to bring me as close as possible back to my desired allocations
- I will not be selling any funds at any point, so this is a buy-only set up (e.g. the optimal answer will not include selling any amount of one fund and buying others, it will only involve buying funds). For example, let's say every 3-6 months or so, I will put in a couple thousand dollars, and during that time, the values of the different funds would have changed, and I would like to quickly figure out which funds I should put more or less money into to bring them back to the percentage of the total portfolio I'd like them to be
- I set the spreadsheet up so that the solver would be trying to reduce the total % I am away from my ideal asset allocations. A simplified example: if I had two funds, and I wanted them to each hold 50% weighting in my $100 portfolio, but currently Fund A was worth 60% ($60) and Fund B was worth 40% ($40), then both funds would be 10% away from their ideal allocation of 50%, and the total percentage away from a perfect allocation would be 20%. I set the solver up to try to reduce that total of 20% by as much as possible. If I had $50 to add to my $100 portfolio, buying $15 of Fund A (now totaling $75) and $35 of Fund B (now also totaling $75) would be the ideal allocation, bringing them both to my desired 50% allocation, reducing the total percentage away from ideal to 0%.
- Solver set to reach a solution by changing the cells that adjust how much of each fund to buy (purple cells, M8:M14)
- I've tried using a multitude of different constraints, currently it is set to just one constraint: the total amount of new funds to buy cannot exceed the new amount I have to invest (N2)
- Currently set to Nonlinear (not sure what would be correct here, but I am sure I have tried all different options here in the past)
- Edit: I also plan to include a constraint that makes use of most of the new money I have to invest (e.g. if I have $2000 to invest, it wouldn't be much use if the optimal Solver output was to only buy $100). But for now, I am just trying to get the Solver output to work, and plan to add that constraint once I have it working properly.

I am hoping it is somewhat self explanatory when looking at the spreadsheet, I have set it up in quite an organized fashion (I hope). I cannot get solver to output any amounts at all. Thanks in advance for any help.

You need a tighter constraint for variables, that is

$M$8:$M$14 <= $N$2

and then switch to Evolutionary.
Time limit is set to 30 seconds, best result is 0.17%.

HTH,

Francesco

Thanks Francesco, that seems to be working better! I will have a play around with it, but could you confirm what exactly the new constraint you provided means ($M$8:$M$14 <= $N$2). Does that mean the sum of these cells must be <=$N$2? Or each individual cell must be <=$N$2? I am hoping it is the former.

Assuming it does mean that the sum cannot be greater than N2, I would love to know why using this constraint is tighter/better than what I was doing - I suppose it has to do with the rounding down and extra formulas I was applying to these outputs before summing them?

Finally, if I am now looking at including a constraint that attempts to make use of most of the new money I have to invest, do you have any recommendations? Let's say I want to tell Solver to always use at least 80% of the funds available, should I use a similar constraint such as this (assuming O2 is 80% of the money available to invest): $M$8:$M$14 >= $O$2

I am wondering if this extra constraint would be necessary, as I don't see how using less of the funds available would create a more optimal result, but I just want to make sure I don't end up getting a result that says the best option is to spend $100 when I have $2000 available.

Thanks for the help Francesco, really appreciate it.

The new constraint means the latter, it is only a compact expression for 7 constraints. I added it to define a boundary for the variables used in the Evolutionary algorithm. There are better choices, for instance setting a specific limit for each variable, as you can see in the linear model attached.

I tried to change as little as possible of your original setup.
The model has two non-linearities: a ROUND() function, and an ABS() function. The former is dealt with adding some integer variables, for the second there are both a new variable and two constraint. I think you can safely keep them hidden.

Why bother to transform the model? As you can easily check, Solver will find the solution in a flash. Moreover, we are guaranteed that it is optimal.

As for your last question, if I understand correctly, the constraint SUM($M$8:$M$14) >= $O$2 will not improve your optimal solution, even though adding it will surely force the model to find a new result.

Let me know if you want more details on the equations used to linearize the ABS() function.

HTH,

Francesco