Simple Portfolio Rebalancing Spreadsheet Template

Since I’ve been reviewing a bunch of portfolio management services, all which are intended to be cheap and use index funds, I thought I’d refresh an old post on how I do basically the same thing myself. I rebalance my portfolio using this very simple Google Docs spreadsheet, which is embedded below*. Yellow cells are those meant to be edited.

1. You have to decide on a desired asset allocation. I personally don’t think there is one perfect portfolio, here are several model portfolios. Below is what I have settled on for now. Details here. You only have to enter this once as long as your target asset allocation stays the same.

2. Choose how often to rebalance. You can do it on a set calendar basis such as annually on your birthday or quarterly. Another method is to only rebalance once your percentages are off by a certain amount, like a tolerance band. I personally check in quarterly to see where I should invest any new cashflows, and if things are really off then I rebalance by selling something.

3. Manually enter your total balances for each asset class. I grab my holdings either from logging in to each individual website (less than 5 for me) or by using an aggregation service like Mint.com. I only hold a limited number of index funds so it’s easy to determine the appropriate asset class for each.

4. Check out the actual breakdown vs. your target breakdown. The spreadsheet shows the actual percentage breakdown vs. the actual breakdown, as well as the dollar amounts of any differences. In the fictitious example shown, I’d feel that I was close enough that I wouldn’t really bother with any rebalancing. If things were really off, I could buy/sell as needed.

I would say this method takes me about 20 minutes each quarter, and I like that it keeps me buying low and selling high. It definitely made the rebound from 2009 pay off more than simply doing nothing or worse, panicking.

(* Note! I am sharing this online in read-only format. If you wish to customize or add your own values, you must make a copy of it (File > Make a copy) over to your own Google Spreasheets account (log in first) or download it as an Excel file (File > Download as). Any requests for edit access to the original public spreadsheet will be denied, because you would be changing the appearance for everyone.)

Comments

  1. Chuck Fouts says:

    I’d like to see a follow up post to this about when to rebalance. I know that brokers advise you to rebalance every year but that seems like a way to drive up fee revenue. Personally I’ve tried rebalancing only when my investment choices are off by 5% or more in one allocation. It seems a bit more logical to allow a winning allocation to run for a bit before trying to rebalance to get back on plan.

  2. @Chuck – check out this study, this is the method I follow when monitoring and pulling the trigger on a rebalance:

    http://www.tdainstitutional.com/pdf/Opportunistic_Rebalancing_JFP2007_Daryanani.pdf

  3. Great stuff. Rebalancing done in qualified accounts systematically over time can add a big difference. I’m curious, why at such a young age are you invested in bonds? Are the “bond monies” for a shorter term goal?

  4. @Chuck Fouts, yeah I don’t see why you would want to rebalance if you aren’t off by more than 5%. I’m with you on that.

  5. @AR, long term return histories have shown that 80/20 stock to bond ratio actually out performs 100% stocks and has less risk.

    There are ways you can re-balance without it costing too much in fees by using zero commission etfs through scwhab or vanguard, otherwise I wouldn’t re-balance unless the fee for executing the trade was less than 1% of what I was re-balancing.

  6. Chuck and Chris, when you say “off by 5%” do you mean 5 percentage points, or 5% of the target allocation? For REIT, 5 points would mean rebalancing when it reaches 5% or 15% of the total, while 5% would mean rebalancing when it gets to 9.5% or 10.5% of your total. Significant difference.

  7. @AR – 20% bonds isn’t high for his age. Most would recommend somewhere between 10% – 30%, so he’s right in the middle.

  8. I dont see the point of rebalancing. Why do you want to buy more of what wasnt working for you and sell what has been? In a taxible account this would be a yearly tax nightmare.

    Why not just research good companies ie individual stocks and buy the ones with the most potential for growth.

    If you owned 5 good fast food restaurants that had grown like crazy for one year would you sell two of them and buy two of a slower growing competitor to “rebalance” and be “diversified”??? Stupid logic here and with investing.

    This strategy makes no sense for an investor. For a broker its a great money making marketing tool and has been used for decades to soak money out of the under educated.

  9. Andrew Weinberg says:

    @Chuck. I tend to add money to my portfolio semi-regularly (about quarterly). When I do so, I automatically rebalance by placing a bit less than my overall allocation into recent winners and a bit more into recent losers. I do this through Excel, and while a bit more complex than the spreadsheet above, it can be done by most people who have at least some familiarity with Excel.

  10. gold? silver?

  11. Steve Bonds says:

    This simple spreadsheet is great as a simple example. It also breaks down in the same way all the other simple examples do. What do you do if you have multiple accounts? Money can’t be transferred freely between an IRA and a 401(k) account.

    Similarly, what do you do when you hit minimum investment amounts?

    I suspect people who decry rebalancing don’t understand the point of the underlying investment strategy. We’re not trying to pick the winners– we’re picking everybody and riding the “sure thing” to market average performance over time.

    — Steve

  12. @ Steve

    I suspect those who re-balance don’t understand that the point of investing is to take the least amount of risk to make the most amount of money. Owning everything good and bad purchased at thousands of price points is risky. Your buying good stocks, bad stocks, stocks at their 52 week high, their 52 week low, stocks with increasing debt, decreasing EPS, decreasing cash flow, etc is much riskier then spending a little bit of time figuring out what company is best. It always amazes me how hard people work for their money but then they are satisfied with “average” returns and put little or zero time into researching and studying the investments. Are you kidding me?

    How did the last 10 years of being average with the “sure thing” do for index investors. Im sure re-balancing in 2001 and 2007 worked out well as part of the investment strategy.

    Re-balancing serves no purpose. You essentially are buying more of the companies that are poor performers and provided inferior returns. Take 30 min do a little research and leave all the nonsense behind.

    Diversification for the sake of diversification is more risky then owning quality individual stocks at attractive prices. Your risking inferior returns with your money as you own downright horrible companies as well as good companies in similar quantities. Not only that but everyone owns the same stuff over and over and over again. If you invest in mutual funds you likely own the same company 10 times over in as many accounts. Whats the sense?

    Also bonds in anyone under the age of 40 is nonsense. Why own bonds? Just for the sake of it? Because it sounds safer? Again you are risking further return for the sake of “safety”. Again ignorance feels better when you own a lot of everything and its been pounded into your head that bonds are “safer”.

  13. A great intro to the concept that chasing returns is usually futile can be found in the book “The Random Walk Guide to Investing.” (Follow the link on my name to preview a relevant section on page 118. That whole section is good.)

    For an even more detailed discussion, see “The Only Guide to a Winning Investment Strategy You’ll Ever Need.” Chapter 1 dives right into the misplaced allure of stock picking and the book gets very detailed.

    Here’s a good quote from Robert Stovall quoted in the Wall Street Journal (Oct 4 1999):

    “It’s just not true that you can’t beat the market. Every year about one third of the fund managers do it.” … “Of course, each year it is a different group.”

    Worse than even odds of making just the average when I could be hitting it 100% of the time? Sign me up for the sure bet!

    I do agree about owning the same stuff repeatedly. To really “own the whole market” requires a close eye on what you’re getting and more small and mid-cap stocks than you might think. The Morningstar X-Ray helps a bunch there both for an initial purchase and to rebalance.

    — Steve

  14. I like the Google Docs spreadsheet, but I can’t get it to work with my Mac/AppleWorks. Any suggestions?

    Matt,
    I don’t know where you’re getting your info, but I suggest you read Larry Swedroe’s “Wise Investing Made Simple”.

  15. Matt–

    Bonds are indeed safer for a number of reasons. Their coupon payments are contractually paid ahead of dividends to common shares. Likewise they are senior to equities in the capital structure of a company and, depending on where they rank in the capital stack, may be secured by specific assets. Yes, bonds can lose value, but in a restructuring they always rank ahead of common stock, which is in the first loss position. You may argue that there is no risk of a bankruptcy in the best companies, but I would remind you that if you honestly avoid hindsight you will admit that just a few years ago both Lehman Brothers and AIG were considered some of the best companies in their industries. Lehman shareholders were wiped out, and AIG’s essentially so, whereas the bondholders have recovered varying degrees of value.

    And yes, divesification into bonds or cash would have been very helpful from 1996-2000, when one would have been selling off overvalued stocks and stashing money into bonds ahead of the correction, and from 2000-2003, when you would have been moving back out of bonds and into stocks during the bear market at lower valuations, and doing the same things from 03-07, and then buying back into stocks beginning in March of 09.

    You feel you are able to find the best stocks with low risk in 30 minutes. I do not have the same confidence in my abilities so I take a different approach to mitigating risk that has historically resulted in returns that are acceptable to me.

  16. Re-balancing serves no purpose. You essentially are buying more of the companies that are poor performers and provided inferior returns. Take 30 min do a little research and leave all the nonsense behind.

    I have to say I agree. I did a little research, bought some blue-chip stocks, and have so far been very happy with the performance of my investments in Bear Stearns, Merrill, Lehman Bros., Citigroup, Eastman Kodak, Sprint, Sun Microsystems and AIG. Far better to have owend these solid performers than trying to spread my risk around…oh. Wait. Never mind.

  17. @Matt

    What you say does make sense. However, your average investor does not have the time or knowledge to buy individual stocks. It is better for your average person to be a well balanced and diversified buy and hold investor.

    Numerous studies have shown this to be the case, otherwise one bad company can or bad timing due to average human emotions can really screw up long term returns.

    If you have 10 hours a week to pour over research and the guts to withstand big swings in individual companies, than buying individual stocks will be much more profitable. But I still believe that it is only for a minority.

  18. The pie chart did not show up when I downloaded the spreadsheet to Excel. Was it supposed it? I like it.

    Thanks,
    Craig

  19. If you have 10 hours a week to pour over research and the guts to withstand big swings in individual companies, than buying individual stocks will be much more profitable. But I still believe that it is only for a minority.

    If you have 10 hours a week to pore over research, over and above your job and family obligations, remember also that the big banks and funds have thousands of analysts who have 80 hours a week each to do that same research. If you think you can outsmart them, fine. If you think you can exploit information that they somehow haven’t picked up on, OK. But I seriously doubt that you’re the one guy who’s good enough to do this.

  20. Re-balancing serves no purpose. You essentially are buying more of the companies that are poor performers and provided inferior returns.

    This is otherwise known as selling high and buying low. You, on the the other hand, seem to be advocating buying high. But by the time the stocks have provided superior returns, it’s already too late to buy them.

  21. @Stefan,
    Unfortunately I can see what kind of battle Im fighting here. Not to drag this out here but your responses are enjoyable in that they seeth ignorance regarding the subject.

    If you owned mutual funds you owned all of those stocks anyway. If you were an index investor you owned all of them until they left the index at minimal listing requirements or as they went bankrupt as is the case with bear stearns and lehman. Again take a little time and realize what the heck your money was invested in and get out of those companies. Not terribly hard.

    10 hours a week? Yeah why not. Most people work 40+, lose nearly half to taxes and then blindly dump it into a fund. My hope is that your research would allow you to eventually work less. Unfortunately yourself and many others do not see that a desirable. Whatever, your wasting your time, no one elses.

    And believe it or not I understand the whole buy low sell high thing, I do not need a lecture. I suggest you spend a little time learning more about what I am talking about as its obvious you dont have a clue. Its quite obvious in the depth and simple reasoning you use in your replies.

  22. the problem with just doing Portfolio Rebalancing is you are never going to be super super rich. if you are comfortable with retiring between 1-5 million dollars, by all means just do Portfolio Rebalancing. However, I am in the boat that we have to at least strive for more than that, so I have a portion of in my portfolio that I do anything I want.

  23. Jonathan, could you please explain how you set up and maintain the 80/20 and specific percentages for each category (US, Intl, emerging, etc) while having multiple accounts (Roth, 401K, etc) with set amounts of money in each to work with? Do you have a breakdown of exactly how much is in each fund within each account that you could show us? We are trying to figure this out so that we can rebalance our own portfolio but we’re finding it difficult to figure out how to spread it out over 4 accounts and still maintain the 80/20 along with fixed percentages for each of the categories. Thanks!!

  24. @Matt

    You’re new around here aren’t you… ; )

    I work with institutional investors all day. These people have staffs filled with CFAs and MBAs and spend not 30 minutes picking stocks, but closer to 60 hours a week…

    Due to the frictional costs, the majority of these professional managers under perform the markets over time. Professional money managers can outperform, but it is a difficult and time consuming task to do it consistently.

    I don’t doubt you’ve probably had a good run, but one would need to take a serious look to determine if your recent returns are based on skill more than luck. Additionally, even if it was skill (say in picking restaurant stocks) there is no guarantee that a sector you have expertise in will perform well in the future.

    Best of luck to you, and while I agree that bonds shouldn’t be a large part of a younger person’s portfolio, having dry powder to purchase stocks through rebalancing if a correction hits is a prudent, not foolish approach.

  25. @Matt,

    What you say seem to be correct in theory – invest in stocks that are priced below their potential. Warren Buffet did that and is reasonably well off. But doing what he did (investing in right comapnies consistently for decades) is very very hard.

    If you are confident of picking the winners, great! All the best. Please share your wisdom with the rest of us so that we can get rich too :)

  26. Diversification doesn’t seem to be that important. Watch the market day to day. Everything goes up or down together. Timing and luck are everything.

  27. Cabron James says:

    Jon or others,

    I use a google spreadsheet I made, that is similar to Jon’s included here. My spreadsheet cell B3 that uses nested =IF( statements. If my portfolio meets the relevant rebalancing threshold, based on an asset type getting too much or too low a percentage of the entire port, it well tell me the relevant action, such as “sell stock”. If I don’t need to rebalance, the cell B3 is blank, because it’s the “” option in the nested IF statement.

    What I want, is a way to get an email or text message, should 1 of my rebalancing thresholds get met.

    I like Google Spreadsheet for port monitoring, because the spreadsheet automatically updates the asset prices, apparently close to real-time, with the =GoogleFinance(“VWO”,”price”) type formulas.

    Is there a way to do this with google spreadsheet? Perhaps by using something like Tools>Script or Tools>Notification Rules?

  28. Jenna, Adaptu Community Manager says:

    Thanks for sharing your spreadsheet with us!

  29. Cabron James says:

    Some thoughts on this asset allocation debate in the comments.

    Harry Browne of the Permanent Portfolio had an interesting way of looking at investing, which could be useful generally even to Jon or others that use a non-PP asset alloc.

    Assume you have 2 portfolios, what I’ll call
    1 an Investing Port, that is based on an asset allocation, using index funds & avoiding active funds as much as possible, & rebalancing rule scheme that you decide on beforehand, & that you follow/execute against. (HB called this the Permanent Port). Jonathan’s asset allocation & plan to review quarterly his port to see if rebalancing is needed, is an example of an Investing Port. This port needs to have 0 leverage/margin, & use no leveraged &/or inverse ETFs.

    2 a Speculation Port, ala what Steve advocates: randomly speculating on different investments, be it Apple stock, German Sovereign bonds, silver, a Rental property you own, etc.

    At any time You may take part or all of the Speculation Port to fund the Investment Port, but you NEVER take from the Investment Port to fund the Speculation Port. Any new $ added to the Speculation Port would have to come from income, & then if & only if the Investment Port is sufficiently funded.

    The Investment Port is “money that you can’t afford to lose”, that you rely on for retirement, or in an emergency like a medical emergency like Cancer if (when?) your health insurance screws you out of covering the costs it seems are on your policy (thanks Obamney aka the US doesn’t have a civilized health system like Canada/every other rich nation).

    The Speculation Port is money, that if you lost 100% of it, you would still be OK, it’s money you CAN afford to lose. Because you don’t want Speculation port losses to take from your Investing port, you shouldn’t use margin/leverage in the Speculation port.

    Unless you are a multimillionaire/Obamney/plutocrat/0.1%er where if you lost 90% of your nest egg, you would still be a millionaire aka rich enough to never worry about dire situations (such as homelessness, death due to failure to pay health costs, etc) the Speculation port should probably be like 0-20% of your total nest egg, perhaps at very most 50%. Personally I am 100% Investment Port & have no Speculation Port. It sounds like Jonathan is near 100% Investment Port.

    For those doing a Speculation Port, factor in the extra time & worry factor as a qualitative cost. Eg. a real CPI-adjusted return of 5% in the Investment Port is superior to the same 5% return in a Spec Port, because of the extra time & worry factor a Spec Port entails. At least factor the extra incremental hours you spend researching the Spec Port at at least $10/hr.

  30. Cabron James says:

    btw, forgot to mention what I’m calling the Spec Port is the “Variable Port” in Harry Browne lingo.

  31. I hope Matt is a fund manager, because if not, he’s wasting his (purported) talents.

  32. First of all, wow this is an old thread! Still gets people fired up. Get people out of their comfort zone, make them think a little for one second that what they are doing isnt the greatest and, woolah you get a discussion going!

    @ Michael. I do manage a small fund of my friends and families money. Its at nearly 2 million in assets now, started in early 2009, up nearly 300%. The S&P is up 100% also so Ive tripled it. Pick nearly any individual stock from this time period and you are up more then 100%. Some 8-900% Not exactly rocket science.

    @ All of those who like indexing, or buying mutual funds. Im not going to change your mind, obviously. Some like to manage money, some don’t. Whatever, no need to argue. There is literature supporting both sides. My only advice is that you are serious about investing, do you own thing and make more money. If you want to be part of the crowd so be it. Those who go against it make money.

    Zig when others Zag and you will do quite well. Buy the companies do one wants for transient reasons. Those asking for stock advice look for companies with little debt, excellent strong, increasing free cash flow. Increasing dividends, and a steady growing EPS.

    • AnonymousJohn says:

      Stumbled onto this discussion today 2 yrs after most recent post. MATT – if you’re still linked to new posts, I appreciate your advice on sound fundamentals. Question I have is about market cap – if your gains are truly a significant multiple over the pros, are you generally finding your selections to be microcaps/smallcaps overlooked by the pros? Or are you more a momentum player riding along with the big boys? Cheer$

  33. Thanks for the spreadsheet! Rebalancing is a great tool used in a long-term portfolio to help you keep your asset mix at the right allocation for you. If you don’t rebalance, you risk being overly invested in stocks, adding unneeded risk to your portfolio, or being too risk adverse and not earning the return you expected.

  34. Could you tell us which ETF you would use for the bonds part of the allocation?

    Thanks,
    Mark

Speak Your Mind

*