Simple Portfolio Rebalancing Spreadsheet Template (Google Drive)

My Money Blog has partnered with CardRatings and may receive a commission from card issuers. Some or all of the card offers that appear on this site are from advertisers and may impact how and where card products appear on the site. MyMoneyBlog.com does not include all card companies or all available card offers. All opinions expressed are the author’s alone.

gsheetsUpdated. Automated portfolio management services like Wealthfront and Betterment will help you manage a diversified portfolio of low-cost index funds for a fee. While I understand their appeal for those that wish to outsource that task, I choose to maintain my own diversified portfolio of low-cost index funds. I enjoy having full control of all investment decisions, and I like saving the management fee (and adding that money to my snowball).

An important part of this DIY portfolio management is staying close to your target asset allocation. I use a very simple Google Spreadsheet to track my portfolio. Here is the direct link and it is also embedded below. Yellow cells are those meant to be edited.

(Download a free copy: I am sharing this spreadsheet online – free of charge – in read-only format. However, please make a copy of it using the menu option File > Make a copy or download it as an Excel file using option 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.)

 

Here are some guidance on how to use the spreadsheet:

1. Decide on a target asset allocation. Don’t use the generic one I put above. There is no perfect portfolio. You can find plenty that look great based on history at this moment, but that will not be the perfect portfolio 5, 10, 25 years down the line. The best portfolio is the one that you can stick through even after your fanciest asset classes have negative returns for 5+ years.

Here are a few model portfolios to get you started. Below is what I have settled on for myself. Details here. You only have to enter this once as long as your target asset allocation stays the same.

2. Enter your total balances for each asset class. The easiest way to grab my holdings from multiple brokerage accounts is to use a aggregation service like Personal Capital (review). If you don’t have that many accounts, simply log into each individual website and add up your totals by asset class.

You could solely rely upon a service like Personal Capital to manage your portfolio, but I tend to use some specific asset classes like “US Small Value” or “Emerging Markets Value” which Personal Capital does not recognize. I do enjoy the fact that it pulls in all of my holdings and balances automatically into one screen and is always updated.

3. Check out the actual breakdown vs. your target breakdown. The spreadsheet shows the current actual percentage breakdown vs. your target breakdown, as well as the dollar amounts of any differences. A positive number means you need to buy more to reach your theoretical target (negative means sell). In the fictitious example shown, I might 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.

3. Rebalance with new cashflow, dividends, and interest. Choose your frequency of “forced” rebalancing. By using this spreadsheet, you can see which asset classes should be invested in currently to bring you back towards your target asset allocation. This is where you should invest any new cashflow (i.e. paycheck, dividends, rental income, or interest that your portfolio generates).

In addition, you can rebalance by selling some asset classes and then buying another. I try not to sell too often as to avoid capital gains taxes. You can do this 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 of +/- 5%. 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 at most once a year. If you have sizable taxable holding, you could also attempt some tax-loss harvesting during these check-ins.

Recap. If you are managing your own portfolio, it is important not to stray too far from your target asset allocation. In order to know where you should invest new funds, I track my portfolio in two ways. First, I use Personal Capital for a real-time, daily snapshot of my holdings. Second, I manually update this spreadsheet each quarter and print out a copy for my permanent, physical records. This takes about 15 minutes every 3 months. Using these two methods, I maintain complete control over my portfolio and I don’t have to pay any management fees to a robo-advisor.

My Money Blog has partnered with CardRatings and may receive a commission from card issuers. Some or all of the card offers that appear on this site are from advertisers and may impact how and where card products appear on the site. MyMoneyBlog.com does not include all card companies or all available card offers. All opinions expressed are the author’s alone, and has not been provided nor approved by any of the companies mentioned.

MyMoneyBlog.com is also a member of the Amazon Associate Program, and if you click through to Amazon and make a purchase, I may earn a small commission. Thank you for your support.


User Generated Content Disclosure: Comments and/or responses are not provided or commissioned by any advertiser. Comments and/or responses have not been reviewed, approved or otherwise endorsed by any advertiser. It is not any advertiser's responsibility to ensure all posts and/or questions are answered.

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

  35. Can someone help me add extra asset classes to the template? I’m a luddite with these kind of things.

    Thanks in advance.
    Will

  36. Warren Buffet says

    Thank you for this! I was trying to rebuild one I saw on youtube, but couldnt get the formulas right for the rebalnce numbers. I downloaded this and BAM! It’s everything I needed and now I know the correct formulas to use if I wanted to build my own.

  37. This is super helpful. I love the graphics. Thanks for sharing!

  38. Independent says

    Why is the article dated April 17, 2017 while all the comments are in the past? Is there a bug?

  39. Your spreadsheet is exactly what I was looking for and it helped me a lot.

    Without reading Harry Brown, I have also developed a portfolio consisting of an investment portion and a speculative portion.

    The speculative portion will return at least 100% pa and then I will be happy with the investment portion returning 6-8% pa. Excess returns form the spec portfolio will be funneled into the investment portfolio, just to keep it topped up.

    I tend to agree with Matt above, that if you are prepared to put in some work then the returns can be amazing, but being prudent about risk it is best to only speculate with money that is non essential.

    I love finding those hidden gems in the stock market announcements and so researching spec stocks is a hobby to me. Over the past 2.5 years this weekend research has netted me over $200 per hour and that is why I am giving up my day job and semi-retiring after Christmas.

    In response to some comments above, you are re-balancing sectors not specific stocks and it is important from a risk management perspective to do this.

  40. All sorts of reasons. Markets change, and what wasn’t working might rally soon. You might want a certain cocktail of exposure to different asset classes, or risk profiles. You might even have rules like a minimum cash position. See https://www.portfolio-modelling.com as a tool that takes care of those kinds of rules while rebalancing assets in your portfolio.

  41. I made following service as my personal work.
    This service can rebalance your portfolio easily.
    https://rebalancer.yuuniworks.com/

  42. David Di Sipio says

    This is great! Thanks a lot for creating this.
    I think I might be missing something, why do your stocks add up to 100% and your bonds also add up to 100%? I was under the impression that the entire portfolio (including bonds) needs to equate to 100%
    Thanks
    David

  43. I know I am replying to myself, but I wanted to keep the thread intact and provide an update on progress given that I am at the end of my financial year.

    In the first instance I was probably buoyed by early success in the Year 1 and Year 2 of my venture and in fact my speculative portfolio did not continue to return 100% per year.

    But now four years in, my strategy has produced an average of just under 50% per year, which is a compounded rate of 3.4% per month. Not quite the 100% per year, but I am quite pleased with it, given the constraints of working and travelling.

    I hope you don’t mind me sharing some numbers.
    After four years my original $68,000 float is now worth $330,000 and I will fully retire on 12th July 2019 to pursue my passion of stock picking and travelling.

    One thing I realised is that with a full time Corporate role I could not spend sufficient time to monitor my investments and by working I was actually losing money by not selling at the optimum time. When I look back on my trades I see losses that should have been gains, hopefully this will be addressed by having the time to manage things better.

    For those of you that are number orientated here are my stats:

    Average Win: $5161
    Average Loss: $2190
    Avg Return/Trade: $1798
    Win Rate: 54%
    Trades / Week: 0.74
    Total Trades: 146
    Avg Trade Size: $5206
    Expectancy: 0.8
    Win/Loss Ratio: 2.34

    You will see that my losses are far to large, this is a consequence of my failure to monitor positions closely due to working. So in retirement I am targeting to get this very much lower.

  44. At the end of July 2019 I ended up with a four year annual return of 54.4% per annum and a portfolio of $386735.

    Average Win: $5173
    Average Loss: $2322
    Avg Return/Trade: $2123
    Win Rate: 59%
    Trades / Week: 0.75
    Total Trades: 150
    Avg Trade Size: $5173
    Expectancy: 0.91
    Win/Loss Ratio: 2.23

    Next 12-month target is $597,227, however I will be satisfied with $500,000

Leave a Reply to Warren Buffet Cancel reply

*