Sub-optimum results from Solver

[JenniferMurphy]

I have some money to invest in an existing portfolio that comprises three mutual funds that I try to keep "balanced" at 50%, 40%, and 10% respectfully. In the past, whenever I've had a chance to add to my investments, I try to "rebalance" the funds. That is, I put more into the funds that are below their intended percentage and less into those that are over.

I have always done this by trial and error. My son told me about the Solver in Excel, so I thought I would see if I could get it to calculate the best possible way to distribute the new money across the existing funds so as to rebalance them as closely as possible to the intended percentages.

I set up a sample portfolio as a learning device and to test the Solver. It contains three funds (A, B, C) with total assets of $1,000. The intended distribution percentages are 50%, 40%, and 10%. I gave each fund a current balance ($400, $400, and $200) so that they are out of balance. I then defined an amount for the new investment to be allocated among the three funds so as to bring them as close to in balance as possible.

It worked for several test cases, but then I came across one where The Solver did not find the best solution.

In the example above, Fund C should have 10% of the total assets. But it has double that ($200 of $1,000). If I increase the total assets by double (by investing an additional $1,000), I can bring the portfolio into balance by allocating all of the new money to Funds A and B. The Solver correctly finds this solution.

But when I increase the new investment to $1,100, it fails. A new investment of $1,000 can be thought of as $1,000 (allocated as above) plus $100 (allocated according to the target percentages, since the portfolio is in balance after the $1,000).

Here's the the sheet for that example:

[TABLE="width: 661"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]$1,000[/TD]
[TD="colspan: 2"]Current Total Holdings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]$1,100[/TD]
[TD="colspan: 2"]New Investment[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]$2,100[/TD]
[TD="colspan: 2"]New Total Holdings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Holdings[/TD]
[TD]Fund A[/TD]
[TD]Fund B[/TD]
[TD]Fund C[/TD]
[TD]Totals[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Target Holding %s[/TD]
[TD]50%[/TD]
[TD]40%[/TD]
[TD]10%[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Current Holdings[/TD]
[TD]$400.000[/TD]
[TD]$400.000[/TD]
[TD]$200.000[/TD]
[TD]$1,000.000[/TD]
[TD]Formulas (Col C)[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Current Holding %s[/TD]
[TD]40.000%[/TD]
[TD]40.000%[/TD]
[TD]20.000%[/TD]
[TD]100.000%[/TD]
[TD]=C9/$F9[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Current Target Holdings[/TD]
[TD]$500.000[/TD]
[TD]$400.000[/TD]
[TD]$100.000[/TD]
[TD]$1,000.000[/TD]
[TD]=CurrentTotal*C8[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Current Holding Errors[/TD]
[TD]-$100.000[/TD]
[TD]$0.000[/TD]
[TD]+$100.000[/TD]
[TD]$200.000[/TD]
[TD]=C9-C11[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Current Holding % Errors[/TD]
[TD]-20.000%[/TD]
[TD]0.000%[/TD]
[TD]+100.000%[/TD]
[TD]1.2000[/TD]
[TD]=(C9/C11)-1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]New Target Holdings[/TD]
[TD]$1,050.000[/TD]
[TD]$840.000[/TD]
[TD]$210.000[/TD]
[TD]$2,100.000[/TD]
[TD]=NewTotal*C8[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]New Investments[/TD]
[TD]$687.499[/TD]
[TD]$402.501[/TD]
[TD]$10.000[/TD]
[TD]$1,100.000[/TD]
[TD]Solver[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]New Holdings[/TD]
[TD]$1,087.499[/TD]
[TD]$802.501[/TD]
[TD]$210.000[/TD]
[TD]$2,100.000[/TD]
[TD]=C9+C15[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]New Holding %s[/TD]
[TD]51.786%[/TD]
[TD]38.214%[/TD]
[TD]10.000%[/TD]
[TD]100.000%[/TD]
[TD]=C16/$F16[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]New Holding Errors[/TD]
[TD]-$37.499[/TD]
[TD]+$37.499[/TD]
[TD]-$0.000[/TD]
[TD]$74.999[/TD]
[TD]=C14-C16[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]New Holding % Errors[/TD]
[TD]+3.5714%[/TD]
[TD]-4.4642%[/TD]
[TD]+0.0000%[/TD]
[TD]0.0804[/TD]
[TD]=(C16/C14)-1[/TD]
[/TR]
</tbody>[/TABLE]

The Solver target cell is F19, which is the sum of the absolute values of the percentage differences between the actual holdings and the balanced holdings. It is displayed as a number, not a percentage, because of the absolute value.

The Solver (Changing) cells are C15:E15, which are the amount allocated to each fund.

The criteria are that the new find balances must be at least at large as the old ones and all of the new money must be invested.

This may not be clear given the restrictions on this board on attachments, so I've created a PDF explaining what I did in detail. I uploaded it to this Dropbox page:

https://www.dropbox.com/sh/r5mxhfttv29ucpj/AABw-B4VJsOBTsFD-_5KNqyba?dl=0

I would like to know if my Solver setup is correct and, if not, how to fix it.

If it is and the problem has to do with the initial conditions (guesses), I would like to know how to choose better initial conditions.

Thanks

 

[RickXL]

Hi,

Thanks for the puzzle!

I had not used Solver until I came across your question. At first I thought it must be easy to do analytically. Then the reality of linear programming dawned

Eventually I made Solver do what I wanted it to. I am going to try and describe what I did in words. I am not sure if it solves all your problems but it does produce answers and does not seem to be dependent on initial conditions. The spreadsheet looks like this:

[TABLE="width: 418"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]400
[/TD]
[TD]400
[/TD]
[TD]200
[/TD]
[TD]1000
[/TD]
[TD]start
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]50.00%
[/TD]
[TD]40.00%
[/TD]
[TD]10.00%
[/TD]
[TD][/TD]
[TD]%
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1100
[/TD]
[TD]650
[/TD]
[TD]440
[/TD]
[TD]10
[/TD]
[TD]0
[/TD]
[TD]split
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]1050
[/TD]
[TD]840
[/TD]
[TD]210
[/TD]
[TD]
[/TD]
[TD]answer
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]1050
[/TD]
[TD]840
[/TD]
[TD]210
[/TD]
[TD]
[/TD]
[TD]ideal
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]1050
[/TD]
[TD]840
[/TD]
[TD]210
[/TD]
[TD][/TD]
[TD]check
[/TD]
[/TR]
</tbody>[/TABLE]

My starting investments are in B1:D1.
E1 is the total of that row and is used later.
Row 2 has the desired percentages.
A3 is the total new amount to be invested.
B3:D3 are the rows calculated by solver.
E3 has the formula =A3-SUM(B3:D3) so goes to zero when the answer is arrived at.
Row 4 is the starting values in row 1 added to the figures calculated by Solver so is the final amounts in the funds.
Row 5 is the optimum answer based on the total amount of money in the system (i.e. starting values plus new investment). The formula in B5 is: =SUM($E$1,$A$3)*B2 and should be dragged across to C5 and D5.
Row 6 has some deviousness. B6 contains: =IF(B1>B5,B1,B5) So, if the starting value in the fund is greater than the optimum value then the starting value is used as the target. On the other hand, if the optimum target is larger than the starting value then the optimum vaue is used. The formula in B6 is copied across to C6 and D6.

Solver has its Objective as cell E3 and its target value is 0.
Changing values: B3:D3.
Constraints:
B4 <= B6
C4 <= C6
D4 <= D6

Make Unconstrained Variables Non-Negative is ticked.

I hope this helps.

 

[JenniferMurphy]

Rick,

Your solution works. I cannot figure out why mine doesn't. Any clues?

Thanks

PS: I used =max(B1,B5) instead of your IF statement. Any reason that is not better (at least clearer)?

 

[RickXL]

Good news!

Thanks for the feedback.

I think I prefer max() as well. Never having used Solver before I really struggled with working out how to do it. I made several abortive attempts before I could make something hang together. I knew a question had to be asked about which was the larger so my mind thought: IF. I did think there might be other questions but it turned out not. So, yes, use max().

I am sorry but your solution looked far too long and I had enough trouble with mine so I will have to leave the "failure investigation" to you.

Regards,

 

[shg]

This is a rising tide distribution with renaming of the inputs; it doesn't require Solver:

[Table="width:, class:grid"][tr][td] [/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

5. New Investment​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

$ 1,100.00 ​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td][/td][td]

Total​

[/td][td]

Total Value​

[/td][td][/td][td][/td][td]

Total Allocated​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td][/td][td]

100​

[/td][td]

$ 1,000.00​

[/td][td][/td][td][/td][td]

$ 1,100.00​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

1. Name​

[/td][td]

2. Desired Ratio​

[/td][td]

3. Current Value​

[/td][td]

Prior % Recovery​

[/td][td]

Breakpts​

[/td][td]

6. Investment Distribution​

[/td][td]

7. New Ratio​

[/td][/tr]

[tr][td]

14​

[/td][td]Fund A[/td][td]

50 ​

[/td][td]

$ 400 ​

[/td][td]

800.0%​

[/td][td]

$ -​

[/td][td]

$ 650.00 ​

[/td][td]

50.0%​

[/td][/tr]

[tr][td]

15​

[/td][td]Fund B[/td][td]

40 ​

[/td][td]

$ 400 ​

[/td][td]

1000.0%​

[/td][td]

$ 100.00​

[/td][td]

$ 440.00 ​

[/td][td]

40.0%​

[/td][/tr]

[tr][td]

16​

[/td][td]Fund C[/td][td]

10 ​

[/td][td]

$ 200 ​

[/td][td]

2000.0%​

[/td][td]

$ 1,000.00​

[/td][td]

$ 10.00 ​

[/td][td]

10.0%​

[/td][/tr]
[/table]
There's a workbook at https://app.box.com/s/a03b2051a19e8006280e.

 

[RickXL]

"rising tide distribution"

Thank you for bringing that to my attention. I had not heard of it before.

However, as part of my puzzling, I did go through a stage where I had normalised the three funds such that 50, 40 and 10 would all be the same height. So, the wrong percentages would all be different values. Then I thought of the resulting figures as rods of different lengths. I bundled the rods together and realised that putting them in water and increasing the level was the solution I needed. But I could not figure out how to do it!

Good to know that I was on the right path and that greater minds can solve it.

 

[joeu2004]

Forgive me, but I'm struggling to understand why the solution requires Solver or any third-party product. Isn't it as simple as the following table?

[TABLE="class: grid, width: 362"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]
[/TD]
[TD]Fund A[/TD]
[TD] Fund B[/TD]
[TD] Fund C[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Target %[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]10%[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]Current Holding[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]Change Holding[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]1100[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD]Target Holding[/TD]
[TD="align: right"]1050[/TD]
[TD="align: right"]840[/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]2100[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD]Change Holding[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"]440[/TD]
[TD="align: right"]10[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
E3: =SUM(B3:D3)
E5: =SUM(E3:E4)
B5: =$E$5*B2
B6: =B5-B3

Copy B5 into C5:D5, and copy B6 into C6:D6.

E4 can be a positive or negative number.

PS.... Of course, it would be prudent to add round formulas in order to fit real-world constraints. If that is the challenge, I can offer simple solutions.

 

[shg]

The criteria are that the new fund balances must be at least at large as the old ones and all of the new money must be invested.

Change E4 to something less than $1000:

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]

Fund A​

[/td][td]

Fund B​

[/td][td]

Fund C​

[/td][td]

TOTAL​

[/td][/tr]

[tr][td]

2​

[/td][td]Target %[/td][td]

50%​

[/td][td]

40%​

[/td][td]

10%​

[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]Current Holding[/td][td]

$400​

[/td][td]

$400​

[/td][td]

$200​

[/td][td]

$1,000 ​

[/td][/tr]

[tr][td]

4​

[/td][td]Change Holding[/td][td][/td][td][/td][td][/td][td]

$900​

[/td][/tr]

[tr][td]

5​

[/td][td]Target Holding[/td][td]

$950 ​

[/td][td]

$760 ​

[/td][td]

$190 ​

[/td][td]

$1,900 ​

[/td][/tr]

[tr][td]

6​

[/td][td]Change Holding[/td][td]

$550 ​

[/td][td]

$360 ​

[/td][td]

($10)​

[/td][td][/td][/tr]
[/table]

 

[JenniferMurphy]

Forgive me, but I'm struggling to understand why the solution requires Solver or any third-party product. Isn't it as simple as the following table?

[TABLE="class: grid, width: 362"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]
[/TD]
[TD]Fund A[/TD]
[TD] Fund B[/TD]
[TD] Fund C[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Target %[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]10%[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]Current Holding[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]Change Holding[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]1100[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD]Target Holding[/TD]
[TD="align: right"]1050[/TD]
[TD="align: right"]840[/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]2100[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD]Change Holding[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"]440[/TD]
[TD="align: right"]10[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
E3: =SUM(B3:D3)
E5: =SUM(E3:E4)
B5: =$E$5*B2
B6: =B5-B3

Copy B5 into C5:D5, and copy B6 into C6:D6.

E4 can be a positive or negative number.

PS.... Of course, it would be prudent to add round formulas in order to fit real-world constraints. If that is the challenge, I can offer simple solutions.

This solution works only under one of two conditions:

1. When none of the funds have a current holding that exceeds the target holding including the new investment.
2. When selling shares is an acceptable option.

Option #2 is not acceptable to me.

Using these initial conditions, your solution will only work for any new investment of at least $1,000. If you plug in anything less than that, the value in D6 will be negative, which means selling shares, which I do not want to do.

If you plug in $500, D6 will be -$50. If you plug in $0, D6 will be -$100.

 

[joeu2004]

This solution works only under one of two conditions:

1. [....]
2. When selling shares is an acceptable option.

Option #2 is not acceptable to me.

Sorry, I overlooked that limitation, and I misread your goal out of context ("put more into the funds that are below their intended percentage and less into those that are over").