- Let’s Go Exploring: Cusco and the Lost City of Machu Picchu - May 29, 2023
- Reader Case: Can I Move to Spain? - May 15, 2023
- Big Tech Is Laying Off Workers: Is Your Job Next? - May 1, 2023
Right now, sitting in my inbox, are about a hundred emails all asking the same question: How do you figure out how to rebalance your portfolio?
And I will freely admit, I haven’t been the best at answering this question. For the longest time, we’ve been calculating our buy orders manually using my trusty calculator, with the intention of eventually putting it all into a spreadsheet that other people can use.
Well, I finally got off my lazy ass and did it!
OK so here’s how it works. The Rebalancer is a simple Excel spreadsheet. When you open it, you may get the following warning:
This is normal, just click “Enable macros.” I have some simple VBA code in there that makes sure the PivotCharts get updated whenever you make any changes.
When we open up our spreadsheet, in the Main tab you see this.
This is where you input your portfolio information.
Once you do that, the Rebalancer figures out what your current portfolio allocation is, what it needs to be, and the buy/sell orders required to get you back on track. I’ve actually been using this tool behind the scenes for the last few weeks to figure out our bi-weekly Workshop Portfolio buys, but now you can play with it too!
Let’s give it a go, shall we?
Rebalancing our Canadian Portfolio
For our Canadian portfolio, we start by putting our ETFs into the Rebalancer. Any cash you have, put the number of dollars you have in the last row.
Once you do this, the other tabs update which contain charts that tell you stuff about your portfolio. The first tab “Current Allocation” is what our current portfolio looks like…
The second tab “Target Allocation” is what we want it to look like…
And the last tab “Model vs Reality” is how far each asset is from target…
So in order to get there, our Rebalancer sheet tells us what to do on the main page…
And after rebalancing, our portfolio will look like this…
| Asset | Units | Price | Market Value | Allocation |
|---|---|---|---|---|
| Cash | 12 | 1 | $12.00 | 0.1% |
| VAB | 190 | 25.28 | $4803.20 | 39.9% |
| VCN | 74 | 32.55 | $2408.70 | 20.0% |
| VUN | 52 | 46.42 | $2413.84 | 20.0% |
| XEF | 62 | 31.08 | $1926.96 | 16.0% |
| XEC | 17 | 28.19 | $479.23 | 4.0% |
Rebalancing our American Portfolio
And on the American side, we start by putting our portfolio into the Rebalancer’s main tab…
On the “Current Allocation” tab, we can see what what our current portfolio allocation looks like…
And this is what we want it to look like…
So in order to rebalance, our Rebalancer tells what we need to do on the Main tab…
And that’s it!
And we’re done! So that’s the Rebalancing calculator you guys/gals have been asking for. Give it a whirl, and let me know if you run into any problems in the comments, or over email.
Here’s the download link again.
Oh and one more thing. When you download the spreadsheet, it’s in “Protected” mode, meaning you can’t see any of the formulas and a lot of the columns are hidden, but if you go to Review -> Unprotect Sheet, you can unlock and see everything in the very likely chance you’re an Excel geek.
That’s it! Peace out!
Continue onto the next article!
WORKSHOP TOOLS
How much does it cost to participate in the Investment Workshop? NOTHING. Because that's how we roll. All we ask is that you sign-up using the following affiliate links to keep it free forever:
For Canadians:
For Americans:
Or, prefer to use a Robo Advisor? Check out Wealthsimple, and get your first $10,000 managed for free!
Disclaimer: The views expressed is provided as a general source of information only and should not be considered to be personal investment advice or solicitation to buy or sell securities. Investors considering any investment should consult with their investment advisor to ensure that it is suitable for the investor’s circumstances and risk tolerance before making any investment decisions. The information contained in this blog was obtained from sources believe to be reliable, however, we cannot represent that it is accurate or complete.
The tool is amazing. I used it today. There are few bugs fixes. I’ll try to compile a list of bugs and send it over to you guys.
One question I have is how did you guys calculate the units of CASH. I am not sure why did you put 602 units for cash. The price makes which should always be $1.00 ?
Because that’s how much cash we had in the portfolio. We added $500 and there was $102 in there from before.
MathShitUp-Inator doesn’t exactly roll off the tongue.
OK fine. What should I call it? The MR Toolkit?
Pretty Effing Cool! Very accurate as compare to my excel sheet, and cool charts too! Worthy to be bookmarked! =)
Thanks guys!
Slick tool! I, too, am just rocking my Excel spreadsheet but next August, when we do our annual rebalance, I’m going to use your spiffy calculator.
Very cool tool: this will be a resource that’ll help a lot of folks, I’m sure of it.
Does this mean then that retiring with $1 million is not sufficient and that you need to supplement through other income streams? The reason why I ask is that I’ve been wondering how much you really need to have set aside to ensure that you never dig into the principal portion of your portfolio. My goal would be to never touch the principal (I’d rather leave that to grateful spawn instead so they can do the same as me). If I have an annual return of 7.5% on my portfolio, the goal would be to take 3.75% (50% of the 7.5%) as income and reinvest the other 3.75% to ensure the portfolio always stays ahead of inflation. On $1 million starting point, that would mean I would have $37,500 to live on. I’ll admit that’s a bit tight for what I have planned and would prefer to start drawing on my portfolio when it hits $1.3 million instead which would give me just under $50,000.
Considering your situation and loss of outside income like affiliates, do you still think $1 million is enough? I’m single by the way so no better half to income split the portfolio income. I just hit the $1 million portfolio mark with $0 debt but live in Toronto so contemplating when is the best time to actually pull the trigger and quit the job (ie: indentured servitude). I don’t plan on blogging to supplement income – I prefer to just read the smart ones like yours instead. I’m a reader, not a writer (lover not a fighter – LOL) Can you advise?
Thanks in advance
Congrats! I think $1M is more than enough to retire, especially if you don’t lock yourself to a high-cost area like Toronto. Like we said, living in places like eastern Europe and SE Asia causes your living expenses to plummet below $30k. And we don’t need any blog income to make our numbers work, any $ we make (and it’s not much by any means) is just bonus. I’m actually going to need to do a portfolio withdrawal for the first time in December, and I’ll write a detailed post about it when I do.
Thanks for the response. I appreciate. I look forward to reading that post when it comes out.
For the $1.3 million target, that’s specifically to handle a HCOL area so that I hopefully never feel forced to chose a LCOL area versus a HCOL area. It’s that safety net that will tell me that I can weather most any financial storm (should be done within 3 years so not too long, fingers crossed). I’m sure after the fact, I’ll look back and say to myself “you dumbass, you wasted 3 years in a cubicle for nothing when you could have been having fun”. Sigh… oh well.
Hi Stone,
I think that you know what you want. Do what you feel it’s right. It will never go wrong.
Ben
Thanks Ben.
I did a lot of binge reading of your blog. Thank you for sharing your knowledge.
The one thing I haven’t seen you write about (unless I missed it) is how do we rebalance during a down market. I know you should do it once a year and also again when you injectnew cash. However do you have any rules for when to do it when the market goes down or goes up?
Also if I have a cash cushion of 70K..in a down market how do I know how I should DCA this cushion? You don’t know how long the bear market will be so if you use the 70k up too soon then you miss out. I’d love to know what your thoughts are.
Thanks again
Let’s say you have a 60 equity/40 bonds asset allocation. Do I have a rule for yourself to rebalance when it shifts by 5%?
Further to this.. within your equity allocation (cdn, USA, international, emerging) do you have an amount for when to rebalance that?
Now that’s just speaking about value drops.. what about value increase – when it goes up?
Hope that makes sense.
Congrats on reaching $1M. My husband and I are also from Toronto and 2017 is our first year on our journey to FI. We are targeting to be FI in 10 years. Hope someday I will have your ‘problem’ on when to pull the trigger. 🙂
It’s an interesting tool for sure BUT Gosh, buy and SELL orders? Most of us rebalance our portfolios by adding fresh money to it and rebalance things on the buy side only !!! (buying more units of one and less units of other(s))
You guys have to build a tool like this instead and then I’ll use: http://optimalrebalancing.tk/index.html
Well if you add cash into the portfolio, the tool will tell you to “sell” the cash and buy the ETFs in the right amounts. Same thing that you’re describing. I haven’t actually sold any units yet either.
Sorry guys but this name really doesn’t come out easily and it might not stick ! some are already calling it Spiffy (simpler guys, simpler)
OK fine. I’ll come up with a better name.
I was not able to get the tool work in IEv11. I will go home and give another try on other browsers.
Good to know. I’ll look into that.
Pretty sweet tool! The only “complaint” I would have is that you need to fill it in each time you use it (as far as I can see). If there was a way to save your portfolio so when you come back the information is there (except market price) that would be good. Otherwise, using an excel template remains a little easier for me.
Perhaps there is potential to create your own little portfolio manager, where you can input buys into the tool each month so it automatically knows your portfolio, all you need to do is update the current market price. That might be getting a little complicated though, but it would be neat!
Hey VancouverBrit! You don’t actually have to fill out the tool each time. You can save it by clicking the “Save” button in the “Save Your Results” section at the bottom. This will bring up a link to save it as a file, or just save the url so you don’t have to refill your information.
Thanks for the feedback!
This is great! Thanks so much for creating this and sharing it.
Hi Firecracker-
Love the site! You are inspiration! Rebalancing is very much conventional wisdom. However, there is plenty of evidence that it doesn’t do a whole lot.
See the writing of Nobel prize winner bill sharpe https://web.stanford.edu/~wfsharpe/aaap/wfsaaap.pdf and Jack Bogle. http://www.morningstar.com/cover/videocenter.aspx?id=615379
The only way I “rebalance” is by taking dividends and investing in areas that my percentage is lower than where I want it to be. It has worked very well for me.
Just my 2 cents. Continue being awesome.
Rob
Very good read, when I run some numbers and approach the subject of balance, you come to the conclusion, that too much balancing is a bad thing. I like to “let the dogs run” which means if a market is doing well, don’t touch it. But use Robs approach to adding to your portfolio, buy the asset that is out of your target range, therefore still adjusting your balance, just not as aggressively. I think the key to investing is leaving your money in Equities for as long as you can stand it… Long gone are the days of Bonds returning 7-10% annually. But I do not believe in 100% Equity portfolios, with no cash in hand. Not at my age, I need to balance risk/reward, a key fundamental of investing.
cheers
Just do it once a year. Nice and easy.
Thanks guys, awesome tool! I apologize for the other complaints about the name! haha (“don’t look a gift horse in the mouth” people!) (also “that which we call a rose by any other name would smell as sweet”)
I’m also a developer and have been wanting to look into Questrade’s API to see if it would be possible to develop something that would let me rebalance with one click (or at least avoid having to look up the market price and enter it). Have you considered that?
I’m new to investing and was surprised at how much of a repetitive chore it is to figure out your buys (not to mention error-prone) when it seems like such an automatable task.
Thanks! And once someone suggests a better name, I will take it. I’m a coder. I suck at naming things.
Awesome! One thing – inputing units for me doesn’t work 🙁
Really? What happens?
I kinda like the name, and love the tool, its slick, kind of the stuff I want to do in retirement which is approx 4-5 years away. So Wanderer, what tools do you use to generate your code, I am struggling with PHP, PERL, and, SQL.
Those are the ones I use. Plus HTML5, JS, and CSS.
thanks, I want to embed a program into my own blog, ya me a blogger too now.. OMG…
see my Gravatar? Anyway… would you suggest perl, or C as the source code?
can you even use a compiled C program on most platforms? I haven’t tried it yet.
Looks spiffy indeed. Any thoughts on how to divide assets between accounts? That was kind of tricky when I wrote software to do this (and made students make changes to that software as part of their class).
I treat the entire portfolio as a whole, figure out my transactions, and then map it to real-world accounts.
Top-down. That’s the way to do it.
I simply can’t input any units. Weird. Is it just me?
Using Safari btw
Hi! Great site. I’m a newbie…just starting my FI journey later in life (wish I was 10 years younger, lol). Do you have any info or anyone who’s doing it in the UK?
This is absolutely incredible, you guys are legends! Been looking for something like this forever.
How many times a year do you rebalance your portfolio when you are still saving for retirement? What months of the year do you do it?
When you’re still accumulating, you effectively rebalance every time you buy by directing your cash at whatever asset is running under target. After retirement, I rebalance maybe twice a year in July and January.
How about a rebalancer tool that makes the portfolio also tax-efficient for Canadians?
I made a spreadsheet in Google Sheets that allows me to input my assets from each account (TFSA, RRSP, Non-Registered, Company Stock Purchase Plan, etc), provide a balance sum for each asset type, and allow me to enter a macro command that saves my portfolio information on a different sheet to help me track my portfolio. It’s handy too in case I forget which ETF to buy based on which account, and if I forget which assets to buy for each account type for tax efficiency.
Any chance the MATHSHITUP-INATOR exists as a repo on Github? I’d love to take a peek at its inner-workings and I bet others in the community might be interested in contributing to it as well.
Interesting idea. We’ll look into it when we have time.
I have using MATHSHITUP-INATOR for a while now. I never had to sell my ETF’s to rebalance but lately the calculator is suggesting to sell one or two units of a specific ETF’s. Now, whenever we sell an ETF on questrade, it charges $5 comission. So I am not sure if it’s worth paying $5 just to sell one or two units. Can you please advise what to do in this scenario ???
Soooo…it’s August 14, 2019 and the stock market is starting to tank. All of the “indicators” are there, apparently. Everyone seems to think there will be a recession started sometime next year. My stocks are falling. Bonds seem to be holding a little more steady. I’m not panicking because you said not to panic.
So my question is; do I simply keep putting money into VTI 30%, VEU 30%, and BND 40%. In other words, do I stay the course and just keep buying, but buying what the Mathshitupinator tells me to buy based on my 60/40 strategy?
We may need a blog post on the front page advising everyone (again) how to “stay the course” during a recession because it looks like one is just around the corner.
Hi I’m so much confuses with the rebalancing. I know is supposed to be once a year but if for example I’m up about 7% and let’s say that 7% up in the stock is $10K should I just sell a little bit of that assets to rebalancing or I have to wait the year? But what happens if by that time the asset drop and now is only 2%.
Should I have a grow target instead of waiting for the time to rebalancing?
Thank you so much for your guidance I really need it.
Is the price the Average or the price of where the units are at the time you are looking at the rebalancer?
Has anyone successfully imported this XLS into Google Sheets? I managed to get the main sheet formulas working, but none of those tasty charts carried forward.
BloomingSooner, you can find something approaching there -> https://docs.google.com/spreadsheets/d/1Jv6Obwwe8kh8u6ADqGmEOCWPawyL-kAeZqpacrZwluI/edit?usp=sharing
Are you seeking effective advertising that actually gets good results? I apologize for sending you this message on your contact form but actually that was kinda the point. We can send your ad message to websites through their contact pages just like you’re reading this ad right now. You can target by keyword or just do bulk blasts to websites in the location of your choice. So let’s say you need to send an ad to all the plumbing companies in the US, we’ll grab websites for just those and post your ad text to them. Providing you’re promoting a product or service that’s relevant to that niche then your business will get an awesome result!
Fire off a quick message to ethan3646hug@gmail.com to get info and prices
AWESOME tool! Thank you, you guys are AWESOME! My only question is, what prices do you input since the prices are different every time you buy. Thanks!
Great tool and resources – thank you so so much for sharing this spreadsheet. I have the exact same question that JP had – what price do we use? Initial cost or the latest cost?
Unless you have reasons to do otherwise, you will usually need to use the prices at the time of the rebalance, because it’s these prices which allow to compute the deviation from your current allocation to the ideal allocation.