My dad has asked for help consolidating his many stocks, bonds, and mutual funds into something easier to manage. He also asked me to create a spreadsheet to tell him how much to add to each fund as he transfers money over. I created the following spreadsheet, but it's not clear to me how to fill in column G.
The desired allocation is correct , the current values of the funds are just numbers I made up to double check my formulas. In this case, the formula says that the one contribution is too small to fully rebalance and you would have to sell some VIGAX. My dad doesn't want to do any rebalancing by selling, so I'd like column G to have a minimum of value and 0, and adjust the other rows to get as close as possible to the desired AA. Basically, I want it to show the values I got manually in column I.
I'm not sure what the best way to go about this is, my first thought is a complicated chain of if statements, wondering if anyone has already created a similar spreadsheet, or just has a better idea.
My dad doesn't want to do any rebalancing by selling...
This means there must be a supply of cash to rebalance only through purchasing. Is that what you want to do? If so, you will have to renormalize the portfolio to the position that has the greatest surplus, which in your case is VIGAX. The normalization value will be the value of VIGAX divided by 0.15. You then use this new normalization to recalculate the actual allocations of each position, the deficit, and column G. At that point VIGAX will have a 0 as the amount to purchase, and all the others will be a positive value, the total of which is the amount of cash you need to come up with to make all those purchases. Once done, it will be balanced. But only for a day.
If he doesn't want to sell, you'll have to add more to rebalance. The only way to do it that I can see is to keep VIGAX as the one set point in the portfolio. 18,000 in VIGAX has to be 15% of the portfolio - which means the total portfolio has to be 120,000. So you can add from there and recalculate the other percentages.
But is the goal to give him the spreadsheet so he can use it in the future without tweaking? That could be more complicated, though could probably be used with something like the solver add-in for Excel. I'll have to think about it...
I may be speaking out of turn, but I suspect the OP wants to deploy the $10,000 to get the portfolio as close to target as possible, "Sorry dad you need another $8,000" probably isn't the answer.
I'd be interested if anybody can get a solver to work. When I set up the problem in LibreOffice the various solvers I tried give up (do not converge), I don't have a copy of Excel, but I recall it's solver was better at auto-scallng which helped it solve non-linear problems, so perhaps it will work.
If I wanted to set up the problem algorithmically form I would do it in multiple steps. The first step I'd find the fund that was most below target and add money to that until it reach the next lowest (or I ran out of money) then increase those two until the matched the third lowest and so on. With 6 funds it only takes 6 steps, and it could be set up in 6 columns. Obviously you'll need to rejigger everything if you add a row, which is not ideal.
Col C Col D Col E Col F Col G Col H
Desired Old Under- New Amount New
Row Symbol Type Alloc Value weight Value to Add Alloc
--- ------ ------------- ----- ------ ------ ------ ------ -----
2 VFINX S&P 500 25% 20,000 TRUE 25,294 5,294 24.3%
3 VIGAX Growth 15% 18,000 FALSE 18,000 - 17.3%
4 VGHCX Health 10% 9,000 TRUE 10,118 1,118 9.7%
5 VBTLX Domestic Bonds 25% 23,000 TRUE 25,294 2,294 24.3%
6 VTIBX Internat Bonds 10% 10,000 TRUE 10,118 118 9.7%
7 VGTSX Internat Stock 15% 14,000 TRUE 15,176 1,176 14.6%
In the UK HMRC (tax office) use average cost. Accordingly its generally more tax efficient to add/reduce additional holdings (funds) than it is to add to existing holdings.
If for instance you buy 100,000 value of a share priced at 1.0 (so 100,000 shares) and the price drops 30% and a rebalance event would have you adding 30,000 more value to stocks, then its better to buy another similar asset at perhaps 0.7/share price than it is to cost average down the average cost of the larger holding. If for instance share prices rebound back to 1.0 and your rebalance event would have you selling perhaps 40,000 of stock value then you can sell 40,000 value of the larger holding at a average buy purchase 1.0 and average sell price of 1.0. Had you added 30,000 to the single stock holding then your average cost would be around 0.91/share and selling some at a 1.0/share price triggers a taxable event. In some cases however it might be more appropriate to reduce the smaller holding rather than the larger holding, such as if you have some capital gains tax exemption/allowance.
You should also consider selling any stock/holdings that end the financial year down relative to cost of purchase, and carry the losses. Which will help reduce capital gains taxes if/when such occur. But you have to be careful to avoid the likes of 30 day trading rules and accordingly either be out of the holding for the 30 days or rotate into a similar but different alternative holding.
If you clump in additions/accumulated dividends in with rebalance trading activity then in effect those trades cost nothing.
i.e. I would question your dad's desire to not do any rebalancing by selling.
Col C Col D Col E Col F Col G Col H
Desired Old Under- New Amount New
Row Symbol Type Alloc Value weight Value to Add Alloc
--- ------ ------------- ----- ------ ------ ------ ------ -----
2 VFINX S&P 500 25% 20,000 TRUE 25,294 5,294 24.3%
3 VIGAX Growth 15% 18,000 FALSE 18,000 - 17.3%
4 VGHCX Health 10% 9,000 TRUE 10,118 1,118 9.7%
5 VBTLX Domestic Bonds 25% 23,000 TRUE 25,294 2,294 24.3%
6 VTIBX Internat Bonds 10% 10,000 TRUE 10,118 118 9.7%
7 VGTSX Internat Stock 15% 14,000 TRUE 15,176 1,176 14.6%
Close, but observe what happens to G5 when I alter the inputs D5 and D6.
I don't want to stop you from trying, but I don't think you can tweak this to be a general solution. That is I don't think there is a solution that can be extended from 6 funds to N funds by just adding rows.
Col C Col D Col E Col F Col G Col H
Desired Old Under- New Amount New
Row Symbol Type Alloc Value weight Value to Add Alloc
--- ------ ------------- ----- ------ ------ ------ ------ -----
2 VFINX S&P 500 25% 20,000 TRUE 25,294 5,294 24.3%
3 VIGAX Growth 15% 18,000 FALSE 18,000 - 17.3%
4 VGHCX Health 10% 9,000 TRUE 10,118 1,118 9.7%
5 VBTLX Domestic Bonds 25% 25,999 TRUE 25,294 -705 24.3%
6 VTIBX Internat Bonds 10% 7,001 TRUE 10,118 3,117 9.7%
7 VGTSX Internat Stock 15% 14,000 TRUE 15,176 1,176 14.6%
Epsilon Delta in previous post wrote:Close, but observe what happens to G5 when I alter the inputs D5 and D6. [It becomes negative.]
Good catch, Epsilon. I didn't check for that possibility.
Epsilon Delta in previous post wrote:I don't want to stop you from trying, but I don't think you can tweak this to be a general solution. That is I don't think there is a solution that can be extended from 6 funds to N funds by just adding rows.
You could very well be right. It wouldn't surprise me if one could prove that my approach cannot succeed for all possible cases. But my math skills are too weak to know. So I'll just keep tweaking. Here is a kluge that seems to work. But I've lost my smugness and will be awaiting your example of a case where it fails.
Col C Col D Col E Col F Col G Col H Col I Col J
Initially Test Still Final
Desired Old Under- New Under- New Amount New
Row Symbol Type Alloc Value weight Value weight Value to Add Alloc
--- ------ ------------- ----- ------ ------ ------ ------ ------ ------ -----
2 VFINX S&P 500 25% 20,000 TRUE 25,294 TRUE 25,000 5,000 24.0%
3 VIGAX Growth 15% 18,000 FALSE 18,000 FALSE 18,000 - 17.3%
4 VGHCX Health 10% 9,000 TRUE 10,118 TRUE 10,000 1,000 9.6%
5 VBTLX Domestic Bonds 25% 25,999 TRUE 25,294 FALSE 25,999 - 25.0%
6 VTIBX Internat Bonds 10% 7,001 TRUE 10,118 TRUE 10,000 2,999 9.6%
7 VGTSX Internat Stock 15% 14,000 TRUE 15,176 TRUE 15,000 1,000 14.4%
Col C Col D Col E Col F Col G Col H Col I Col J
Initially Test Still Final
Desired Old Under- New Under- New Amount New
Row Symbol Type Alloc Value weight Value weight Value to Add Alloc
--- ------ ------------- ----- ------ ------ ------ ------ ------ ------ -----
2 VFINX S&P 500 25% 18,900 TRUE 25,294 TRUE 25,000 6,100 24.0%
3 VIGAX Growth 15% 18,000 FALSE 18,000 FALSE 18,000 - 17.3%
4 VGHCX Health 10% 9,000 TRUE 10,118 TRUE 10,000 1,000 9.6%
5 VBTLX Domestic Bonds 25% 25,999 TRUE 25,294 FALSE 25,999 - 25.0%
6 VTIBX Internat Bonds 10% 7,001 TRUE 10,118 TRUE 10,000 2,999 9.6%
7 VGTSX Internat Stock 15% 15,100 TRUE 15,176 TRUE 15,000 -100 14.4%
8 Total 100% 94,000 104,000 104,000 10,000 100.0%
9 Amount to add 10,000
-------
10 Total 104,000
11 Underweight # 1 85% 86,000
12 Underweight # 2 60% 60,001
This time I adjusted D2 and D7. This caused I7 to go negative.
What happens is H5-F5 = 705. You try to distribute this over the other rows, but that forces H7 too low. My guess is that I can deal with two or three more iterations before I run out of degrees of freedom, but I may have to start adjust the initial values and perhaps the targets a bit more radically. That is the scheme starts to work in a larger and larger set of cases as you repeat the redistribution, so I have to work harder to find cracks.
The other alternative for would be to stop trying to be "fair". In the next round just start at the top and work down. That in column H the total of the negative additions is -100. So we have to add 100 to H7 and subtract 100 from (H2:H6). If H2 is > 100 just do it there and call it a day, otherwise make H2 zero and try to get the rest from H3 and so on. This will definitely satisfy "only additions" but it won't be as close as possible. I'm not sure how far off it could be. It would probably be better to sort with the largest target percentage 1st to keep the errors less noticeable.
Epsilon Delta in previous post wrote:This time I adjusted D2 and D7. This caused I7 to go negative.
Another good counter example. I'm not too surprised my kluge didn't work.
Epsilon Delta in previous post wrote:The other alternative for would be to stop trying to be "fair". In the next round just start at the top and work down. That in column H the total of the negative additions is -100. So we have to add 100 to H7 and subtract 100 from (H2:H6).
I'm not sure if this is what you mean, but here is an approach along this line. Column E just does a straight reallocation regardless of whether the change is positive or negative. Column F then reverses each negative adjustment, and apportions the sum of the negative adjustments among those assets having a positive adjustment.
Col C Col D Col E Col F Col G Col H Col I
Desired Old 1st 2nd New Amount New
Row Symbol Type Alloc Value Adjust Adjust Value to Add Alloc
--- ------ ------------- ----- ------ ------ ------- ------ ------ -----
2 VFINX S&P 500 25% 20,000 6,000 (1,161) 24,839 4,839 23.9%
3 VIGAX Growth 15% 18,000 (2,400) 2,400 18,000 0 17.3%
4 VGHCX Health 10% 9,000 1,400 (271) 10,129 1,129 9.7%
5 VBTLX Domestic Bonds 25% 23,000 3,000 (581) 25,419 2,419 24.4%
6 VTIBX Internat Bonds 10% 10,000 400 (77) 10,323 323 9.9%
7 VGTSX Internat Stock 15% 14,000 1,600 (310) 15,290 1,290 14.7%
This seems so easy, I suspect you will find a case where it fails, Epsilon, or at least gives wild results. ( I hope everyone is not getting too tired of this back-and-forth. )
#Cruncher wrote:This seems so easy, I suspect you will find a case where it fails, Epsilon, or at least gives wild results. ( I hope everyone is not getting too tired of this back-and-forth. )
Speaking only for myself, I'm deeply entertained reading someone with a screen name of #Cruncher claiming weak math skills.
Happy kluging. (kluge-ing?) I have to confess that I do this sort of thing all the time on the back of an envelope or slightly used Post-It note, but I admire those who are determined to find a formula.
The continuous execution of a sound strategy gives you the benefit of the strategy. That's what it's all about. --Rick Ferri
The final answer here (I hope)
I can't follow what you did on your spreadsheet, .... but I hope what I have helps you:
I am assuming cells F2 to F7 is the current value of the various funds, cell F10 is the total current value of your portfolio, cells C2 - C7 is the desired asset allocation. You can compute the actual % and deficit % from columns F and C, but I do not use these in my calculations.
Total amount required to balance funds: TOTAL AMT = G2 + G3 + G4 + G5 + G6 + G7
(I didn't look at your spreadsheet too closely or follow the responses, but this is what I do. You have your spreadsheet funds in rows, I have mine in columns, so I had to do a translation. Hopefully I got it right):
If the amount you are adding (NEW MONEY) is different from TOTAL AMT, the amount you add to each fund is:
H2 = If( NEW MONEY > TOTAL AMT, (NEW MONEY - TOTAL AMT)*C2 + G2, G2*(NEW MONEY)/(TOTAL AMT))
H3 = If( NEW MONEY > TOTAL AMT, (NEW MONEY - TOTAL AMT)*C3 + G3, G3*(NEW MONEY)/(TOTAL AMT))
H4 = If( NEW MONEY > TOTAL AMT, (NEW MONEY - TOTAL AMT)*C4 + G4, G4*(NEW MONEY)/(TOTAL AMT))
H5 = If( NEW MONEY > TOTAL AMT, (NEW MONEY - TOTAL AMT)*C5 + G5, G5*(NEW MONEY)/(TOTAL AMT))
H6 = If( NEW MONEY > TOTAL AMT, (NEW MONEY - TOTAL AMT)*C6 + G6, G6*(NEW MONEY)/(TOTAL AMT))
H7 = If( NEW MONEY > TOTAL AMT, (NEW MONEY - TOTAL AMT)*C7 + G7, G7*(NEW MONEY)/(TOTAL AMT))
I wish I had learned about index funds 25 years ago
JoinToday in previous post wrote:The final answer here (I hope) ...
Here is your method applied against the example in the original post. (I've put your max(F2/C2, F3/C3, F4/C4, F5/C5, F6/C6, F7/C7) calculation into column E, Value for Balance, so it doesn't have to be repeated for each asset.)
Col C Col E Col F Col G Col H Col I Col J
Desired Value for Old Required Amount New New
Row Symbol Type Alloc Balance Value to Balance to Add Value Alloc
--- ------ ------------- ----- ------- ------ ---------- ------- ------- ------
2 VFINX S&P 500 25% 80,000 20,000 10,000 3,846 23,846 22.9%
3 VIGAX Growth 15% 120,000 18,000 0 0 18,000 17.3%
4 VGHCX Health 10% 90,000 9,000 3,000 1,154 10,154 9.8%
5 VBTLX Domestic Bonds 25% 92,000 23,000 7,000 2,692 25,692 24.7%
6 VTIBX Internat Bonds 10% 100,000 10,000 2,000 769 10,769 10.4%
7 VGTSX Internat Stock 15% 93,333 14,000 4,000 1,538 15,538 14.9%
Both your method, JoinToday, and mine in this post from a week ago seem to work. I'm curious whether Epsilon Delta (or anyone else) can find counter examples where either fails to work.
)
