Use your regular contributions to rebalance your investment accounts — this is how I do it.

Using self-directed accounts to manage your investments requires a bit of maintenance, but it can be successfully done with a few useful tools at hand.

Various parts of markets grow and decline at a different pace. So occasionally you need to rebalance, or bring your portfolio back to your desired ratio of stocks vs bonds, and between different ETFs within those categories. If you are regularly contributing money to a self-directed account, you can use this money for regular rebalancing without selling any of your holdings as they get out of whack in terms of their share of portfolio. Selling your holdings to rebalance solidifies your gain or loss related to these holdings. Specifically it matters for non-tax-advantaged (non-registered) accounts where the sale might create a taxable capital gain.

I invest regularly and use additional cash to help rebalance the portfolio. I also do not have automatic dividend reinvestment feature activated, so extra cash sometimes comes from the dividends and sits in the account. In other words, the question I need answered is: how do I use excess cash to correct the deviations of my portfolio from my target. I developed a simple template in Excel to direct the allocation of additionally invested funds in my own portfolio. This and other templates are available at my store:

These are some of the features of my portfolio:

1) It is a tax-advantaged account, so the cash that I have in the account is all I can work this (i.e. I don’t borrow to make trades).

2) I use a “Ridiculous” strategy from “Canadian Portfolio Manager” blog

3) I use Questrade to manage my account

My template can be used for different portfolios and brokerages too, but my explanations are based on the features above. The same template can be used for non-registered accounts.

This is the portfolio I am using: 10% bonds, 90% stocks, recommended for RRSP, RRIF, LIRA and LIF accounts.

This is how this portfolio looks in Questrade (when logged in, go to Accounts → Summary → click on the name of the account you are working with):

And this is how it looks in the template for additional funds allocation:

Notice that ETFs in Excel are sorted alphabetically because this is the way Questrade presents them and it is easier for updating the information.

If any of your ETFs are valued in USD, add FX rate from the page header in Questrade:

Adding ETF expenses to the template is not necessary but a useful feature to be able to calculate your weighted average expense ratio for the whole portfolio. You can obtain ETF expenses from EFT datasheets. Questrade provides them when you purchase ETFs r you can simply Google the information.

Now that the setup is done, on to the actual allocation of the available cash.

From Questrade (or other brokerage) you will need current market values of your ETFs (when logged in, go to Accounts → Summary → click on the name of the account you are working with). Market values of your ETFs will be in the column Mkt val at the bottom of the page.

Cash amount is also displayed on the Investment summary page:

Type them into the template. It should look something like this:

The template will identify instances of allocation with negative variance against target. The message “Price needed” will be displayed for those ETFs.

Next step is to provide the current ask price so that the formula calculates the number of ETF units to be purchased with the available cash. To obtain prices go to Trading → Buy/Sell in your Questrade account. No worries, you are not actually purchasing yet, only checking the prices.

Type the ETF symbol in the order window and copy Ask price to the template. Proceed with all ETF symbols requiring prices.

Formula will propose quantities of ETF units to be purchased to bring the allocation in your portfolio closer to the target.

Now you are ready to purchase the ETF units. Please note that the actual purchase price can deviate from the price you have looked up earlier, if you are working on the template while the market is open, therefore while the prices change.

Type in the amount of commission charges for each purchase to bring you to the correct cash balance at the end.

And you are done until the next time you have available cash in your account:

This and other templates are available at my store:

Disclaimer: I am not a certified financial planner and this article does not constitute financial advice. Please do your own research before acting on the material of this article.



Power of math and forecasting for financial independence

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store