JenniferMurphy
Wellknown Member
 Joined
 Jul 23, 2011
 Messages
 2,309
 Office Version

 365
 Platform

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