#### JenniferMurphy

##### Well-known Member

- Joined
- Jul 23, 2011

- Messages
- 2,676

- Office Version
- 365

- Platform
- Windows

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