Spreadsheet for rebalancing via [contributions]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Topic Author
ajcp
Posts: 645
Joined: Fri Dec 13, 2013 6:44 pm

Spreadsheet for rebalancing via [contributions]

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.

Here is a copy of the spreadsheet https://drive.google.com/file/d/0B-U7_l ... sp=sharing

Thanks!
kolea
Posts: 1322
Joined: Fri Jul 11, 2014 5:30 pm
Location: Maui and Columbia River Gorge

Re: Spreadsheet for rebalancing via contirrutions

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.
Kolea (pron. ko-lay-uh). Golden plover.
Posts: 68505
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

Re: Spreadsheet for rebalancing via [contributions]

TwoByFour has a good approach.

I use the Rebalancing spreadsheet, which is at the bottom of this wiki page: Rebalancing

FYI - I retitled your thread (typo). If you want to change it further, just edit the Subject: line in Post #1.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
kitkatnyc
Posts: 48
Joined: Wed Sep 30, 2015 1:39 pm

Re: Spreadsheet for rebalancing via [contributions]

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...
Epsilon Delta
Posts: 8090
Joined: Thu Apr 28, 2011 7:00 pm

Re: Spreadsheet for rebalancing via [contributions]

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.
#Cruncher
Posts: 3106
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Spreadsheet for rebalancing via [contributions]

Here is a method which works by only considering the assets that are underweight.

Code: Select all

``````                              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%``````

Code: Select all

``````  8  Total                    100%    94,000            104,000    10,000   100.0%
9  Amount to add                    10,000
-------
10  Total                           104,000
11  Underweight Only          85%    86,000``````
Selected formulas:

Code: Select all

``````C11: 85%    =SUMIF(\$E\$2:\$E\$7, TRUE, C2:C7)
D11: 86,000 =SUMIF(\$E\$2:\$E\$7, TRUE, D2:D7) + D9
E2:  TRUE   =D2 < C2 * D\$10
F2:  25,294 =IF(E2, D\$11 * (C2 / C\$11), D2)``````
Clive
Posts: 1950
Joined: Sat Jun 13, 2009 5:49 am

Re: Spreadsheet for rebalancing via [contributions]

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.
Epsilon Delta
Posts: 8090
Joined: Thu Apr 28, 2011 7:00 pm

Re: Spreadsheet for rebalancing via [contributions]

#Cruncher wrote:Here is a method which works by only considering the assets that are underweight.

Code: Select all

``````                              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%``````

Code: Select all

``````  8  Total                    100%    94,000            104,000    10,000   100.0%
9  Amount to add                    10,000
-------
10  Total                           104,000
11  Underweight Only          85%    86,000``````
Selected formulas:

Code: Select all

``````C11: 85%    =SUMIF(\$E\$2:\$E\$7, TRUE, C2:C7)
D11: 86,000 =SUMIF(\$E\$2:\$E\$7, TRUE, D2:D7) + D9
E2:  TRUE   =D2 < C2 * D\$10
F2:  25,294 =IF(E2, D\$11 * (C2 / C\$11), D2)``````

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.

Code: Select all

``````                              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%``````

Code: Select all

``````  8  Total                    100%    94,000            104,000    10,000   100.0%
9  Amount to add                    10,000
-------
10  Total                           104,000
11  Underweight Only          85%    86,000``````
#Cruncher
Posts: 3106
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Spreadsheet for rebalancing via [contributions]

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.

Code: Select all

``````                              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%``````

Code: Select all

``````  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 ``````

Code: Select all

``````C12: 60%    =SUMIF(\$G\$2:\$G\$7, TRUE, C2:C7)
D12: 60,001 =SUMIF(\$G\$2:\$G\$7, TRUE, D2:D7) + D9
G2:  TRUE   =AND(E2, F2 >= D2)
H2:  25,000 =IF(G2, \$D\$12 * (\$C2 / \$C\$12), \$D2)``````
Epsilon Delta
Posts: 8090
Joined: Thu Apr 28, 2011 7:00 pm

Re: Spreadsheet for rebalancing via [contributions]

Code: Select all

``````                              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%``````

Code: Select all

``````  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.
#Cruncher
Posts: 3106
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Spreadsheet for rebalancing via [contributions]

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.

Code: Select all

``````                              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%``````

Code: Select all

``````  8  Total                    100%    94,000   10,000        0   104,000   10,000  100.0%
9  Amount to add                    10,000
-------
10  Total                           104,000
11  Total negative                            (2,400)
12  Total positive                            12,400``````
Selected formulas:

Code: Select all

``````E2:   6,000  =D\$10 * C2 - D2
E11: (2,400) =SUMIF(E2:E7, "<0", E2:E7)
F2:  (1,161) =IF(E2 < 0, -E2, E\$11 * (E2 / E\$12))
G2:  24,839  =SUM(D2:F2)``````
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. )
bayview
Posts: 2293
Joined: Thu Aug 02, 2012 7:05 pm
Location: WNC

Re: Spreadsheet for rebalancing via [contributions]

#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
JoinToday
Posts: 917
Joined: Sat Mar 10, 2007 9:59 pm

Re: Spreadsheet for rebalancing via [contributions]

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.

The amounts required to bring the funds into balance:
G2 = max(F2/C2, F3/C3, F4/C4, F5/C5, F6/C6, F7/C7) * C2 - F2
G3 = max(F2/C2, F3/C3, F4/C4, F5/C5, F6/C6, F7/C7) * C3 - F3
G4 = max(F2/C2, F3/C3, F4/C4, F5/C5, F6/C6, F7/C7) * C4 - F4
G5 = max(F2/C2, F3/C3, F4/C4, F5/C5, F6/C6, F7/C7) * C5 - F5
G6 = max(F2/C2, F3/C3, F4/C4, F5/C5, F6/C6, F7/C7) * C6 - F6
G7 = max(F2/C2, F3/C3, F4/C4, F5/C5, F6/C6, F7/C7) * C7 - F7

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
#Cruncher
Posts: 3106
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Spreadsheet for rebalancing via [contributions]

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.)

Code: Select all

``````                            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%``````

Code: Select all

``````  8  Total (Max in Col E)    100%   120,000   94,000    26,000   10,000  104,000  100.0%
9  New Money                                10,000
-------
10  Total                                   104,000``````
Selected formulas:

Code: Select all

``````E2:  80,000  =F2 / C2
E8: 120,000  =MAX(E2:E7)
G2:  10,000  =E\$8 * C2 - F2
H2:   3,846  =IF(F\$9 > G\$8, (F\$9 - G\$8) * C2 + G2, G2 * (F\$9 / G\$8))``````
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.