Rebalancing Spreadsheet

April 4th, 2014 by Potato

Canadian Capitalist and Squawkfox have created rebalancing spreadsheets to help you when it comes time to rebalance your portfolio. They are somewhat simplistic and hard-coded with the funds — this is a good thing if you’re following the Sleepy Portfolio or one of the Couch portfolios: just enter the current value, the money you have to add, and see how to split your new purchase up.

I wanted one with a bit more flexibility: one that would allow for a few broad categories of investments, with sub-investments. For instance, if I had small bits of cash left over at various points through the year I might throw them into a TD e-series fund, and as long as my overall Canadian/US/International split was ok I wouldn’t worry about rebalancing the e-series versus ETF splits. Or similarly if I had several sub-products to make up one sector, like counting BRK.B and VTI together for US exposure, but not caring too much whether that split was 50/50 or 60/40. Also rather than just entering the current value, the sheet lets you enter the price and the number of units in your various accounts. With the units entered, half the work is done for the next time you want to rebalance (just update the unit prices).

The sheet calculates and displays the variances (how much you are off by), and then also calculates how many units of each fund you need to buy to get back into balance. Note that you should generally round down so that you don’t over-spend the funds in your brokerage account. In the top row you can enter how much new cash you have to invest, or enter a negative value to withdraw cash from your portfolio.

The spreadsheet is available here in Excel, or through Google Docs* here. Enjoy!

* – Google can try to call it Drive, but Docs has stuck with me.

Comments are closed.