Index Portfolio Planning (with Excel)

In our previous articles we’ve discussed how you can balance your index account using some example portfolio models, and we’ve also discussed the general importance of yearly rebalancing. In order to do this effectively, you will need to keep track of your progress. One easy and efficient way of doing this is to use excel. Excel is an extremely powerful tool and can do a lot of the work for you (which is perfect for couch potato investors).

Here is an example of an excel spreadsheet that I use:

In this example, our new investor has $5,000 to invest in a brand new TD e-series portfolio. 

Add in some equations and your portfolio distributions can be calculated for you. All you need to input (for numerical values) would be your initial investment amount and the % allocations you decide on. I like to include summation operators just to ensure no mistakes have been made before I place any orders. I also use the spreadsheet to keep track of which date I added to my investment as well as the reference number of each transaction.

It becomes very slightly more complicated when it comes to rebalancing, but follows the same logic. Here is an example of a spreadsheet for rebalancing:

To build on the previous example, our investor friend is adding $2,000 to his portfolio (one month later). For simplicity sake, we are assuming his investments have individually broken even over the past month (amounts are unchanged). 

This time you will need to enter your beginning account value. If you are not 100% confident with you excel skills, you can include more steps to reduce the use of longer formulas. Using the spreadsheets above I can easily add to my investments and have one master file that holds all this information for me. That way I know exactly when money was added, how my percentage allocations have changed over time, how much money was contributed, etc. It's simply good practice and not very complicated to do. I also recommend you save your file in a Dropbox or some other location that allows you to have a constant backup incase your computer fails.

Hopefully this article gives you some ideas on how you can better keep track of your investments as well as simplifying rebalancing. If you have any questions, comment below!

Thank you for reading and give the blog a follow/share if you enjoy the content!

Here are some of our other articles on similar topics:

Best Index Funds and ETFs (Canada)

Setting Investment Goals with Index Funds

What is Couch Potato Investing

-Yinvestors.