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). I've attached screen shots of the spreadsheet and the solver parameters. 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, 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
I'm not sure what you mean by that. Do you mean sell some of the stocks that are over-weighted and use the proceeds to buy stocks that are under-weighted? I can do that, but it incurs costs (brokerage fees and capital gains taxes). PS: What's "TL;DR"?
Going off of the first sentence in your opening post, there should be 3 transactions at most during each review: you either add or remove money from each mutual fund in order to achieve your desired 50/40/10 split. This is so obvious it makes me assume that this is not what you're really after. TL;DR = too long, didn't read!! (which probably explains my simplistic answer) Please Register or Log in to view the hidden image!
Yes, of course, your (uninformed) interpretation of the problem is trivially simple. As I just said above, I don't want to remove funds. That involves costs that I would prefer to avoid. Holy cow. This is the science forum, not the twitter forum. Perhaps I made my post too long, my apologies, but I tried to provide enough information that there would be no misunderstandings. In the past, I've posted questions that were too short and then spent 5-10 iterations clarifying misunderstandings. Clearly, I left off a key factor -- the problem is to rebalance without selling any stock. Ya think?
OK, I understand what you're after now. Are you married to using the Solver feature? Because I think what you're seeking is to minimize the mean squared error between rows 16 and 7, rather than what you have. Consider if C18 was +10% and E18 was -10%! You would have an "optimal solution" which is obviously wrong.
A solution where C18 = +10% and E18 = -10% is not optimal. That would result in F18 = 20%. I am summing the absolute values of the percentages, not the raw percentages. Another approach is to sum the squares of the percentages. I've tried it both ways. In neither does The Solver get the perfect answer. It always gets a good answer and in less obvious cases, it may get the best answer, but in simple cases where there is an obvious perfect answer, it doesn't always get it. No, I am not invested in The Solver. I just thought it was a cool tool that I had never used and this would be a good project to test it out on. There are really two problems or questions: the practical and the theoretical. For the practical problem, The Solver seems to work well enough. I am buying mutual funds, not measuring the universal gravitational constant. Ten decimal places of accuracy is neither needed not useful. Even if I get it exactly right today (50/40/10), the funds will change prices the next day and it will be off. Besides, I will never blindly use the results. I'll always eyeball them to see if they make sense. For the theoretical problem, I would like to know why The Solver cannot get the right answer when there is a right answer. If it's a limitation of the method, fine. But if I have it set up wrong, then I'd like to understand how to set it up right. The example that puzzles me is different results between the simple case of adding $1,000 to the existing $1,000 portfolio, which The Solver gets right, and the almost-as-simple case of adding $1,100 to that same portfolio. The correct answer is to first add the $1,000, which brings the portfolio into perfect balance, then add the $100 in the 50/40/10 proportions, keeping everything in perfect balance. I think the difficulty is that The Solver uses an iterative approximation approach (a la Newton-Raphson) and it stops when it gets within some threshold error. It's not looking for a perfect solution. The "perfect" solution I thought I was getting for the $1,000 investment turns out to be not quite perfect. If I increase the number of decimal places that are displayed, I see that there is an small error in the 5th decimal place. I've attached three new images. Each one shows both the manual solution and the Solver solution so the results can be more easily compared. The first one (01) shows adding $1,000 to the $1,000 portfolio. The Solver comes very close to the perfect solution, but not perfect. The second one (02) shows adding $1,100 to the same $1,000 portfolio. The Solver misses this one badly by not investing anything in Fund C. The third one (03) shows adding $1,200 to the same $1,000 portfolio. The Solver comes very close to the perfect solution, but not perfect. I tried to come up with a closed form equation for the best solution, but failed. Is there one?
I appreciate that you want to know why this isn't working in addition to having an actual, reliable solution. I don't have time for anything but a very short response right now, but I would start with removing your constraints to simplify the logic for the Solver. You don't need to ensure that the values are all positive in the samples you're working with, for example.
Jennifer, Hey there. First off, it's neat that you've mixed an enrichment strategy with a technical problem with a sort-of mother-son project. I highly recommend that you prove him right--that may be one of your best long term strategies. Please Register or Log in to view the hidden image! Under the "Options" for Solver - what are you using for Tolerance? Try lowering the number to see if it performs better. The optimization algorithm quantizes, and this looks like the place to control that feature.
