Spreadsheet for Rebalancing Bands

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
subham
Posts: 168
Joined: Wed Oct 01, 2014 1:01 pm

Spreadsheet for Rebalancing Bands

Post by subham »

Newbie here and searched the blog but could not find suitable answer.

I started maintaining my portfolio in excel. I tried to come up with a way to implement the rebalancing with bands with following goals:

1) Implement 5/25 rule in http://www.bogleheads.org/wiki/Rebalancing
2) Rebalance without selling by adding new money even if bands fire
3) Incorporate DCA plan
4) Send alerts to email if bands fire

Very quickly the complexity of my spreadsheet went up and I had to spend hours to debug it. I am now porting it into google spreadsheets to get real time quotes and hopefully will figure out how to send email alerts when the bands fire so i dont need to look at my portfolio frequently.

Having said that, it got me thinking, how is everyone doing this? although these are very basic steps we need to implement, it is not straight forward at all and I was hoping there is a software that does this. For bogle philosophy to be truly simple, the rebalancing exercise should be simplified as well. Else it beats the purpose of passive lazy investing. I am hoping to find a simple mechanism that I can just pass on to my successors to maintain my portfolio when the time comes without scaring them off with unnecessary complexity.

I have looked at a few spreadsheets posted by others (in the wiki) here but none of them have the rebalancng bands implemented and also don't have anything to address #2, #3 and #4 which I think are very useful features.

Am I missing something here? Surely someone would have figured out all this by now!!
User avatar
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: Spreadsheet for Rebalancing Bands

Post by hoppy08520 »

Subham, as an occasional programmer and spreadsheet junkie, I admire your ambition. If you can get your spreadsheet to do all that, the congratulations.

As for me, in my Google spreadsheet, I have a sheet for rebalancing. It does what I need to do, but it doesn't tell me how to DCA. I do make regular 401(k) and IRA and taxable contributions, and they are mostly on auto-pilot, but I do try to tweak them to contribute to underperforming asset classes in order to rebalance with new contributions, but I do this manually. I don't have any email triggers, but I do have one prominent place on my spreadsheet's front-page dashboard that will tell me if I need to rebalance.

On my "rebalancing" sheet, I have each asset class in a row, with columns for current balance and target balance.

Then I have columns that compute what the "high" and "low" is for a 5% absolute band and a 25% relative band, which is a Larry Swedroe recommendation. Then for the "low" band, I use the higher of the two, and for the "high" band, I use the lower of the two, as the operative low and high rebalancing bands.

Then I have a percentage that shows where each asset class's balance lies within the net-low and net-high range - 50% is in the middle; 0% is at the low end of the range, 100% is at the top of the range. A percentage below 0% or above 100% would trigger a rebalance.

On my "front" page of the spreadsheet, on the "dashboard" that I look at regularly, I just have a cell that will turn red and say "rebalance!" if any of the asset classes are below 0% or above 100%.

Apparently you can send emails out of google sheets. I skimmed some of the pages that show you how to do this. What I'm not sure about is whether your sheet will do this while you're asleep, or whether you can get your sheet to recalculate GoogleFinance functions when the spreadsheet is not "open".

Good luck.
User avatar
Aptenodytes
Posts: 3786
Joined: Tue Feb 08, 2011 7:39 pm

Re: Spreadsheet for Rebalancing Bands

Post by Aptenodytes »

You don't need automated emails. In less time than it would take to program that you could take a peak around twice a year.
User avatar
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: Spreadsheet for Rebalancing Bands

Post by hoppy08520 »

Aptenodytes wrote:You don't need automated emails. In less time than it would take to program that you could take a peak around twice a year.
But that would be....boring. Wouldn't it be better to spend many hours creating an ingenious way to send automatic emails?

I'm half-joking, I hope you realize. :)

A lot of people find tweaking formulas and devising the ultimate mastermind spreadsheet to be entertainment for its own sake. It may not be practical, but neither is soduko, crosswords, or other puzzles.
User avatar
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Spreadsheet for Rebalancing Bands

Post by BigFoot48 »

I have a spreadsheet that I update monthly with the current balances and monthly income by fund. A section summarizes that data by the 5%/25% bands and I look at that. If it indicates one of more funds should be rebalanced I take note it but defer any action for 2-4 more months of similar findings to make sure the market is serious about the direction it's going. If it is, I might rebalance or wait until January which is my favorite rebalancing month.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Sbashore
Posts: 952
Joined: Wed Feb 20, 2008 9:38 pm
Location: USA

Re: Spreadsheet for Rebalancing Bands

Post by Sbashore »

BigFoot48 wrote:I have a spreadsheet that I update monthly with the current balances and monthly income by fund. A section summarizes that data by the 5%/25% bands and I look at that. If it indicates one of more funds should be rebalanced I take note it but defer any action for 2-4 more months of similar findings to make sure the market is serious about the direction it's going. If it is, I might rebalance or wait until January which is my favorite rebalancing month.
I do something very similar to this. I use 5%/20%. If I get a rebalance signal I I buy or sell to get back on target. I also restrict any action (buy or sell) for a given asset class to one time per calendar year. That way I avoid a repeat of what I encountered in 2008-2009 when I was getting rebalance signals way too often as things melted down.
Steve | Semper Fi
placeholder
Posts: 8421
Joined: Tue Aug 06, 2013 12:43 pm

Re: Spreadsheet for Rebalancing Bands

Post by placeholder »

My spreadsheet does 1) and shows the deltas that could be used for 2) but my IPS says that I use those to direct new money however if something is outside the range and it can't be fixed by new money then sell/buy is required (hasn't happened in a long time) and finally 3) and 4) are not used as I don't DCA nor do I need alerts.
User avatar
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: Spreadsheet for Rebalancing Bands

Post by hoppy08520 »

A forum member asked me about my own rebalancing spreadsheet. I made a generic version and put it on Google sheets for anyone who'd like to have a look:

Rebalancing Bands template

I also linked to it on the wiki. I hope someone might find it useful. There are other good spreadsheets on the wiki page as well:

Rebalancing - Bogleheads wiki
User avatar
matjen
Posts: 2189
Joined: Sun Nov 20, 2011 10:30 pm

Re: Spreadsheet for Rebalancing Bands

Post by matjen »

hoppy08520 wrote:A forum member asked me about my own rebalancing spreadsheet. I made a generic version and put it on Google sheets for anyone who'd like to have a look:

Rebalancing Bands template

I also linked to it on the wiki. I hope someone might find it useful. There are other good spreadsheets on the wiki page as well:

Rebalancing - Bogleheads wiki
Thank you for sharing your work. Much appreciated. :D
A man is rich in proportion to the number of things he can afford to let alone.
Topic Author
subham
Posts: 168
Joined: Wed Oct 01, 2014 1:01 pm

Re: Spreadsheet for Rebalancing Bands

Post by subham »

hoppy08520 wrote:A forum member asked me about my own rebalancing spreadsheet. I made a generic version and put it on Google sheets for anyone who'd like to have a look:

Rebalancing Bands template

I also linked to it on the wiki. I hope someone might find it useful. There are other good spreadsheets on the wiki page as well:

Rebalancing - Bogleheads wiki
Thanks a lot for this spreadsheet, its a good start to build on.

However looking at column K (Min Amount to Buy/Sell) its not clear how the various amounts are supposed to reconcile. For example it says Stock-Intl needs to be sold for $3500. Where does this money come from? It does not match the Stock-Intl-TISM which requires to be sold for $4450. Similar inconsistencies seen across different aggregations.

It would be nice to see a final summary of buy/sell at tertiary level that would be consistent across different rebalancing rules.

Also how would one add new money into the system in combination of rebalancing bands?

This is where I found it get challenging to come up with a complete spreadsheet.

Do you have any ideas on how to get there?
User avatar
Aptenodytes
Posts: 3786
Joined: Tue Feb 08, 2011 7:39 pm

Re: Spreadsheet for Rebalancing Bands

Post by Aptenodytes »

subham wrote:
hoppy08520 wrote:A forum member asked me about my own rebalancing spreadsheet. I made a generic version and put it on Google sheets for anyone who'd like to have a look:

Rebalancing Bands template

I also linked to it on the wiki. I hope someone might find it useful. There are other good spreadsheets on the wiki page as well:

Rebalancing - Bogleheads wiki
Thanks a lot for this spreadsheet, its a good start to build on.

However looking at column K (Min Amount to Buy/Sell) its not clear how the various amounts are supposed to reconcile. For example it says Stock-Intl needs to be sold for $3500. Where does this money come from? It does not match the Stock-Intl-TISM which requires to be sold for $4450. Similar inconsistencies seen across different aggregations.

It would be nice to see a final summary of buy/sell at tertiary level that would be consistent across different rebalancing rules.

Also how would one add new money into the system in combination of rebalancing bands?

This is where I found it get challenging to come up with a complete spreadsheet.
I think you are investing far too much thought in the spreadsheet, as if your goal were a fully automated, robotic, workflow. If that's what you really want, it may make sense to go with someone like Betterment. But why do you really want that?

With a simple spreadsheet that tallies balances and gaps, your brain can tell you what to do next. E.g. with respect to your question of where the $3500 in international stock should be sold from: First of all, the example has international stocks under-allocated, so they need to be bought, not sold. Second, if you want to know what to sell in order to be able to buy international stock, just look at the assets that are over-allocated. In this example I would sell Bond IT to buy international stocks.

With respect to your question about how to cope with new investments, all you do is direct new funds to the asset categories that have the largest shortfall, taking into account the relationship between categories and sub-categories. E.g. in this example spreadsheet I would put new money in international stocks.

You don't have to hit the bulls eye with each rebalancing transaction. If you move things in the right direction, efficiently, you are OK. Any extra effort to perfectly hit the bulls eye is a waste of time, because the next trading day you'll be off again.
User avatar
swimirvine
Posts: 371
Joined: Tue May 28, 2013 7:51 am

Re: Spreadsheet for Rebalancing Bands

Post by swimirvine »

I tried to create some templates. Each has it's strengths and weaknesses.

http://www.bogleheads.org/wiki/Using_a_ ... _portfolio

look under - Track your portfolio in real-time
The way I invest my money is not the right way to invest, it's the right way for ME to invest.
Topic Author
subham
Posts: 168
Joined: Wed Oct 01, 2014 1:01 pm

Re: Spreadsheet for Rebalancing Bands

Post by subham »

Aptenodytes wrote:
subham wrote: I think you are investing far too much thought in the spreadsheet, as if your goal were a fully automated, robotic, workflow. If that's what you really want, it may make sense to go with someone like Betterment. But why do you really want that?

With a simple spreadsheet that tallies balances and gaps, your brain can tell you what to do next. E.g. with respect to your question of where the $3500 in international stock should be sold from: First of all, the example has international stocks under-allocated, so they need to be bought, not sold. Second, if you want to know what to sell in order to be able to buy international stock, just look at the assets that are over-allocated. In this example I would sell Bond IT to buy international stocks.

With respect to your question about how to cope with new investments, all you do is direct new funds to the asset categories that have the largest shortfall, taking into account the relationship between categories and sub-categories. E.g. in this example spreadsheet I would put new money in international stocks.

You don't have to hit the bulls eye with each rebalancing transaction. If you move things in the right direction, efficiently, you are OK. Any extra effort to perfectly hit the bulls eye is a waste of time, because the next trading day you'll be off again.
W/o re-balancing bands, its obvious what to do. Once you introduce Larry Swedroe's rebalancing across different aggragations, it gets complex. I am trying to simplify it to a spreasheet so that my spouse can handle this w/o having a second thought. I am precisely wanting to avoid what you are suggesting....having to build gut feeling of what to do when the time for re-balancing comes by taking myself and my emotions out of the equations.

Hoppy's spreadhseet is the closest to get there but he leaves it ambiguous by not showing how the final sell/buy definitions at tertiary level.

Regarding new monies, yes what you said makes sense if the dollar amount is small, but if you are DCAing huge chunks (like i am doing) getting the allocation right is essential as this would allow something similar to "value averaging" without all the complexity needed.
User avatar
Aptenodytes
Posts: 3786
Joined: Tue Feb 08, 2011 7:39 pm

Re: Spreadsheet for Rebalancing Bands

Post by Aptenodytes »

subham wrote: W/o re-balancing bands, its obvious what to do. Once you introduce Larry Swedroe's rebalancing across different aggragations, it gets complex. I am trying to simplify it to a spreasheet so that my spouse can handle this w/o having a second thought. I am precisely wanting to avoid what you are suggesting....having to build gut feeling of what to do when the time for re-balancing comes by taking myself and my emotions out of the equations.

Hoppy's spreadhseet is the closest to get there but he leaves it ambiguous by not showing how the final sell/buy definitions at tertiary level.

Regarding new monies, yes what you said makes sense if the dollar amount is small, but if you are DCAing huge chunks (like i am doing) getting the allocation right is essential as this would allow something similar to "value averaging" without all the complexity needed.
You are talking about a use case that is so complex and case-specific that I can't imagine any solution other than to build your own tools.

I don't see how DCAing large chunks complicates things. I would think it would simplify it, because if your DCA chunks are large then all you have to do is make your new investments mirror your desired AA. What could be easier?

I tend to see two types of people -- those that like lots of precision and customization in their rebalancing, and those that don't care for it. The first group tends to also have a high comfort level with brewing their own tools; many actually get positive value out of the task. You seem to be in a kind of off-diagonal. You want the precision and customizability, but you don't seem to want to do the tinkering needed to achieve it. My advice is either do the tinkering on your own or scale back your demand for precision and customizing.

Personally, I would caution against making any assumptions about transferring a complex spreadsheet to a spouse post-mortem. There are so many ways that could go wrong. And no matter how easy you make the spreadsheet operating it will be a burden and source of stress. My read-when-I'm-gone note just says to put everything into a target-date or life-strategy fund.
User avatar
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: Spreadsheet for Rebalancing Bands

Post by hoppy08520 »

subham wrote:
hoppy08520 wrote:A forum member asked me about my own rebalancing spreadsheet. I made a generic version and put it on Google sheets for anyone who'd like to have a look:

Rebalancing Bands template

I also linked to it on the wiki. I hope someone might find it useful. There are other good spreadsheets on the wiki page as well:

Rebalancing - Bogleheads wiki
Thanks a lot for this spreadsheet, its a good start to build on.

However looking at column K (Min Amount to Buy/Sell) its not clear how the various amounts are supposed to reconcile. For example it says Stock-Intl needs to be sold for $3500. Where does this money come from? It does not match the Stock-Intl-TISM which requires to be sold for $4450. Similar inconsistencies seen across different aggregations.

It would be nice to see a final summary of buy/sell at tertiary level that would be consistent across different rebalancing rules.

Also how would one add new money into the system in combination of rebalancing bands?

This is where I found it get challenging to come up with a complete spreadsheet.

Do you have any ideas on how to get there?
Good feedback, thanks.

There are two important columns:

Over/Under Target (J)
Min Amount to Buy/Sell (K)

"Over/Under Target" (J) tells you what you need to buy and sell to get back to exact allocation. All of these buy/sell numbers will sum up to $0 if you sum up just the "tertiary" asset classes in Rows 9 - 18.

Where it can be a bit confusing is that "Min Amount to Buy/Sell" (K) does not zero out, because it only tells you what you need to buy/sell to get back within the far end of one of your bands. I would look a column (K) as guidance if you don't want to do a full rebalance, but instead want to just get closer gradually by redirecting new contributions. This column can also tell you how badly out of balance you are, along with "Rebalance Range Gauge" (H) which gives you a relative number saying how far out you are.

As a starting point, if you were to think about how to DCA, then I'd look at "Min Amount to Buy/Sell" (K) and at least buy the ones that are most negative. I'm sure there's more you can do with a spreadsheet but I think it gets pretty complicated. I just use these numbers as a general guide. Also, I don't feel the need to be totally in balance all the time. I just want to stay close enough.
Aptenodytes wrote:Personally, I would caution against making any assumptions about transferring a complex spreadsheet to a spouse post-mortem. There are so many ways that could go wrong. And no matter how easy you make the spreadsheet operating it will be a burden and source of stress. My read-when-I'm-gone note just says to put everything into a target-date or life-strategy fund.
I agree 100% with this both in theory and in practice.
Topic Author
subham
Posts: 168
Joined: Wed Oct 01, 2014 1:01 pm

Re: Spreadsheet for Rebalancing Bands

Post by subham »

Thank you Aptenodytes for your feedback on various aspects. I will keep that in mind as I finalize the IPS. I got mine almost working and may be one day will post it for review and others benefit.

Hoppy08520 one suggestion is I think the re-balancing bands serve as a "trigger" and it makes sense to actual re-balancing has to be done towards the target so that in a momentum drift the band edge is not triggered again. If you re-balance to the middle of the band, then you slow down when the next re-balance occurs.
Calsaver
Posts: 67
Joined: Tue May 13, 2014 9:27 am

Re: Spreadsheet for Rebalancing Bands

Post by Calsaver »

Very helpful info here from many. Thanks.

It's curious to me that there isn't software for this. Or is there? Surely the financial media has spun up investors, day traders and whoever to want all sorts of financial software, useful and not. You'd think that a rebalancing/tracking program would be fairly simple to write, but have a few extra features from a spreadsheet and maybe have a more streamlined appearance?

The spreadsheet works, just that it'd be nice that when I want to change something I didn't have to dink around on excel for 4 hours to get the feature I want.
Topic Author
subham
Posts: 168
Joined: Wed Oct 01, 2014 1:01 pm

Re: Spreadsheet for Rebalancing Bands

Post by subham »

Aptenodytes wrote: Personally, I would caution against making any assumptions about transferring a complex spreadsheet to a spouse post-mortem. There are so many ways that could go wrong. And no matter how easy you make the spreadsheet operating it will be a burden and source of stress. My read-when-I'm-gone note just says to put everything into a target-date or life-strategy fund.
Wouldn't that trigger a huge taxable event? Or are you counting on step up in tax bassis that world allow the spouse to start a more simple AA from scratch with out any tax worries?
pascalwager
Posts: 2327
Joined: Mon Oct 31, 2011 8:36 pm

Re: Spreadsheet for Rebalancing Bands

Post by pascalwager »

If you should happen to use a 50/50 stock allocation (e.g., TSM/TISM) and no bonds, the 5% band is all you need to consider. Just set up your spreadsheet to show the actual percentages for TSM and TISM and rebalance when TSM (TISM) is above 55% or below 45%. I've done this for several years and still haven't needed to rebalance.

Personally, instead of bonds, I keep a few years worth of checking, savings, CDs, and I-Bonds ($10k added each year). If yields increase and I do begin to add T-Bills, TIPS, etc. by selling stocks, then these bonds still won't be part of a rebalancing portfolio--just a gradual shift to more bonds as I proceed through my seventies.
VT 60% / VFSUX 20% / TIPS 20%
Post Reply