Portfolio Live - Investment Portfolio Rebalancing Spreadsheet

Portfolio Live v1p3

The objective of this spreadsheet is to allow you to re-balance your investment portfolio while reducing purchasing costs and maximize tax efficiency. It does not spread the funds (or stocks) across all accounts, but tries to keep all of each fund in just 1 or 2 accounts. 

Updated to version 1.3 on October 15th, 2015 to fix a minor bug. Updated to version 1.2 on August 28th, 2015 to add new functionality to allow you to balance the portfolio based on Asset Classes instead of just by each Asset (Fund or Stock). In the spreadsheet there are two separate sheets, one to do the calculation by Asset and the other by Asset Class. 

Version 1.1 added new functionality to allow you to force funds not to be sold from any of the accounts. This feature can be used to allow you to balance the portfolio as best as possible while not selling funds with unrealized capital gains in the Taxable account. 

Here's a link to the spreadsheet on Google Sheets. After you open this please make a copy to your own Google Drive.  

Here's a link to an Excel version. Note that in this version you need to enter stock, ETF and mutual fund prices by hand, or use this Google Sheet - Portfolio Prices - to pull prices from Google Finance and then paste them into the Excel spreadsheet. 

The spreadsheet logic follows the strategy of having certain funds in certain account types. For example you might want to put your funds with the highest interest income in your tax advantaged accounts. 

For each account you set the priority order of all the funds in your portfolio. Your overall Target percent by fund is spread over the different accounts by assigning funds to accounts starting with the account listed first (on the left). The funds are then assigned one at a time starting with priority 1, then 2 etc. How much of each fund in each account depends both on the share of holdings across each account and your percentage of holdings specified for each fund. 

The inputs to the spreadsheet are shown below.

 Spreadsheet Inputs

• Enter the symbol for the funds in your portfolio. 
• The symbol must match the Google Finance symbol names.
• TSX stocks and ETFs will be prefixed with TSE:  US stocks and mutual funds have no prefix.
• Enter target % for each fund.  
• If the fund is priced in US$ then put a y in the USD? column.
• You may change the account names (RRSP, TFSA etc) by changing the yellow cells. 
• Under each account enter the cash holdings (or the amount of cash you are going to invest) and the number of shares (units) you already hold for each fund in each account.
• Enter the fund rank in order for each of the accounts. The fund is allocated to the account based on this order and starting with the account on the left, then the other accounts as listed left to right.
• If you want to force there to be on sales of funds from any of the accounts, then puta 1 under the "Force No Sales" section for one or more accounts. This is most useful for the Taxable account if you don't want to invoke any capital gains. 

The inputs to the version of the sheet that allows you to balance by Asset Class is shown below. The List of Funds and Stocks are entered the same as the version above with number of units. A List of Asset Classes is input and Target % and ranking of Classes by account is input. The Funds and Stocks are assigned to each of the Asset Classes by changing the selection in the drop down boxes under "Select Class".

The spreadsheet pulls the current prices from Google Finance to calculate the value of your holdings.  

The logic in the spreadsheet determines the percentage of each fund in each account based on both your specified target percentage and the percentage of total holdings in each account.  You can see how the percentage of funds are placed into the accounts in the example below. 

In this example, using the total value of the accounts, the RRSP has 63%, the TFSA has 23% and the Taxable account the remainder of 14%.  First funds are assigned to the RRSP account.  XBB was priority 1 so all 25% is put here.  XIN was priority 2 so all 15% is assigned here.  That leaves 22.6% room in the RRSP which takes 22.6% portion of the available 30% of the XSP which was priority 3. 

Next the TFSA account has funds assigned.  The highest remaining priority is #3 for XSP.  There is 7.4% of this left over so it is all assigned here.  That leaves 15.7% room in TFSA so the next priority #4 XIU is used to fill that.  The remaining 14.3% of XIU is assigned to the Taxable account. 

The portfolio value is calculated for each fund in each account.  In order to make your portfolio achieve the Target %, the value of each fund to buy or sell is determined.  The number of units to buy or sell is also calculated. 

The spreadsheet as you would download it contains the following example strategy:

• The fund with the highest tax payable will be placed in the tax advantaged accounts. The Bond fund, which has the highest interest income of the funds will be placed in the RRSP first.  
• Order the accounts with the most tax advantaged on the left.  Left to right is RRSP, TFSA and then Taxable.
• The priority for the accounts is based on descending amount of taxable yields, with the most yield ranked as #1.
• Rank 1 is XBB has 3% yield and is fully taxable.  
• Rank 2 is XIN has 2.5% yield and is fully taxable as income.
• Rank 3 is XSP has 1.5% yield and is fully taxable as income.
• Rank 4 is XIU has 2.8% yield as Canadian dividend and is only taxed about half as much as income.

Here's an example of using the "Force No Sales" flag. In the inputs below, which uses the same portfolio percentages and fund ranks as the example above, there are existing XIN ETF and CVE stock in the Taxable account. These are outside the portfolio percentages (not supposed to be there) so without the force to not sell these, the spreadsheet would recommend to sell these. The taxable account is set so that there will be no sales from that account. 

The Target % by account are calculated to allow the XIN and CVE to remain in the Taxable account. 

Now the buy/sell has no sells for the XIN and CVE in the Taxable. 

If we turn off the "Force No Sales" by setting this value to 0 for the Taxable account, then the spreadsheet will recommend sales of XIN and CVE from the Taxable account.

See my blog post - Financial Basics 10 - Investing and Re-balancing for more examples of how to use the spreadsheet.


Disclaimer:  These posts are not fully comprehensive financial advice.  You should seek your own qualified investment, tax and legal advice.