Investing Series: How do I set up an index fund mutual fund portfolio chart?

This is a part of the Investing Series.

————————————————-

If you want to invest in index mutual funds or index ETFs, and you want to track how much you are paying as well as your own personal investing strategy or allocations in each fund, I suggest making the following chart, and updating it once or 4 times a year, each time you decide to rebalance.

Read: What does re-balancing a portfolio mean?

IN DETAIL

Name of Index Mutual Fund or ETF

Pretty self-explanatory. It can be redundant for people who have memorized the symbol or ticker, but I like having a name put to it.

Symbol/Ticker

This is helpful so that you know WHICH one you’re buying. There may be variations of the same mutual fund, held in different currencies (e.g. CAD versus USD), or have a slight twist to them (e.g. being currency neutral).

This lets you know exactly which one it is.

Portfolio %

This is your personal allocation to each index mutual fund or ETF.

I put these generic averages up there, but you might want to put less in International, and more in your own country, etc etc.

Listed MER %

You can find this on the website, of what the Listed MER % is for the fund or ETF you are buying. Just look for something that says “MER”, and that percentage is what you should enter.

Weighted MER %

This is the Listed MER % multiplied by your portfolio %, and it lets you know how much you’re really paying, no matter what the listed MER says.

Note: It is tricky to figure out MERs because they may change on a daily basis, and you need to do a rather complicated calculation of the assets of the fund each day, multiplied by the MER, and then trickling down to what you actually have to pay as an investor. This is why I like having a rough idea of what I’ll pay, but it may not necessarily be very accurate.

More details can be read here: What makes up an MER?

Effective MER %

This tells you how much you are paying in dollars (a rough amount), based on where your portfolio is allocated, and how much MER is being charged.

You can get to the same number without having to break it down by each mutual fund, but I like seeing more detail than is necessary.

I’d like to give a big thanks to Canadian Couch Potato for having made many charts similar to this one, which I have since used as my model, with a very small tweak.

THE CHART’S FORMULAS IN DETAIL

STEPS AND CALCULATIONS THAT WENT INTO THE CHART

( 1 ) CREATE YOUR PORTFOLIO PERCENTAGE

Choose your strategy of where you want your money to be, this is the most time-consuming part because you have to do your research to find ALL the index funds offered by your bank/brokerage, and then choose which ones you want to buy.

I really suggest not putting all your eggs into any one basket.

Few things to consider:

• Stick to holding either all index mutual funds at a bank, or all ETFs; to have an efficient portfolio
• Invest in your own country (mine is Canada, hence the Canadian Index fund)
• Invest in another stable trading market and/or major trading partner (U.S. is a global benchmark)
• Don’t forget the world – Europe is an international index to consider
• International indexes can also be emerging markets (BRIC*) but are considered riskier
• Allocation of bonds depends on age and how close you are to retirement.

*BRIC = Brazil, Russia, India, China; all generally considered “emerging markets”, that aren’t quite stable for various reasons, but have a strong potential for future growth.

( 2 ) FIND ALL THE LISTED MERS FOR YOUR CHOSEN FUNDS

Now that you’ve chosen your funds and what percentages you want them to be, go to their pages, read through them thoroughly and find the MER %.

It is usually on the first page of most mutual fund or ETF pages, and may be pictured in a box that looks like this:

 

Or sometimes on each individual fund, it looks like this:

If you are using Excel, do not make the mistake of typing in “.33”, and expecting it to be a percentage.

To be accurate, you have to format the cell as a % first with the Excel % button, and then type in “0.33″ in the cell.

( 3 ) WEIGHTED MER & ( 6 ) TOTAL WEIGHTED MER

For each mutual fund, multiply the numbers from ( 1 ) Portfolio % x ( 2 ) Listed MER %

Calculations from the above chart:

• TD Canadian Index e-: 30% x 0.33% = 0.10%
• TD U.S. Index -e: 30% x 0.35% = 0.11%
• TD International Index -e: 30% x 0.51% = 0.15%
• TD Canadian Bond Index -e: 10% x 0.05% = 0.01%

The total weighted MER is just a sum of each individual weighted MER.

( 4 ) INDIVIDUAL PORTFOLIO AMOUNT & ( 7 ) TOTAL PORTFOLIO AMOUNT

I do this backwards, going from the total up to the individual.

Instead of going from each mutual fund down to the total, I enter a ( 7 ) Total Portfolio Amount at the bottom (e.g. $10,000), and then do a formula for each mutual fund by multiplying it with the ( 4 ) Portfolio %

( 5 ) EFFECTIVE MER %

Another simple multiplication of : ( 2 ) Listed MER  x ( 4 ) Individual Portfolio Amount.

DO NOT FORGET TO ADD IN THE COST OF TRADES PER YEAR

If you have to pay for buying ETFs, or you have a no load mutual fund, it will cost you money to buy and sell each fund.

(Questrade, I might add, does not charge you commissions for buying ETFs, but they charge you for selling them.)

Add that trading cost to your total MER cost, and you will have a rough idea of how much it costs to hold your portfolio in index funds.

COPY METHOD A. COPY AND PASTE THIS CHART (NO FORMULAS ENTERED)

You can also try and copy and paste this example Excel chart, and put in the formulas listed above (might make things easier).

1. Highlight this entire table.
2. Right-Click and Copy
3. Go into Excel and open any blank sheet
4. Right-Click and Paste as Special
5. Select Text

Name	Symbol	Portfolio %	Listed MER %	Weighted MER %	Portfolio Amount	Effective MER
TD Canadian Index -e	TDB900	30%	0.33%	0.10%	$3000	$9.90
TD U.S. Index -e	TDB952	30%	0.35%	0.11%	$3000	$10.50
TD International Index -e	TDB911	30%	0.51%	0.15%	$3000	$15.30
TD Canadian Bond Index -e	TDB909	10%	0.05%	0.01%	$1000	$0.50
			TOTALS	0.36%	$10000	$36.20

You will see the values appear in their proper columns.

Aside from customizing it for yourself, and you will need to adjust the following columns to add in the multiplication formulas listed above:

• Weighted MER %
• Portfolio Total and Portfolio Amounts
• Effective MER

Still too much work?

Here’s the laziest way possible:

COPY METHOD B. COPY AND PASTE THIS CHART (FORMULAS ALREADY ENTERED)

Copy and paste this table with the formulas in it, into cell A1:

Note: May be a bit tricky to get your cursor right to the edge of the last bottom-right cell.

So I suggest going backwards, and trying to highlight it from the bottom-right cell (just before the X) up to the beginning of Name in the top-right corner.

Name	Symbol	Portfolio %	Listed MER %	Weighted MER %	Portfolio Amount	Effective MER
TD Canadian Index -e	TDB900	30%	0.33%	=D2*C2	=$F$6*C2	=F2*D2
TD U.S. Index -e	TDB952	30%	0.35%	=D3*C3	=$F$6*C3	=F3*D3
TD International Index -e	TDB911	30%	0.51%	=D4*C4	=$F$6*C4	=F4*D4
TD Canadian Bond Index -e	TDB909	10%	0.05%	=D5*C5	=$F$6*C5	=F5*D5
.	.	.	TOTALS	=SUM(E2:E5)	$10000	=SUM(G2:G5)	X

INSTRUCTIONS

You can copy this table with the formulas in them by highlight the entire table above from my blog post (from the X on the botom right-hand side, up to the top left-hand side).

Now right-click on the highlighted section and select Copy.

Now go into Excel, and right-click in the cell A1 (the first upper-left column), and select Paste Special.

Select Text, or else the columns, spacing and formulas won’t work properly, like so:

It should look like this in your Excel when you’re done.

POST-COPYING CLEANUP REQUIRED

1. You may decide you want to format the cells with $ or % formulas (note how the weighted MER is at 0.00099 instead of 0.10%)

(When you do the above for percentages, it will look like 0% because they’re fractions of a percentage. Use the little blue arrows to the left and right to increase or decrease decimal points)

 

2. You will have to delete out the “.” in the bottom row one they’re in there and the “X” (if you copied it by accident), I only needed them as column/text spacers so it would be easier to copy.

3. You will also have to adjust the columns out to fit the words (just double-click on the column lines up by the A, B, C, D, E sections, and it’ll do it automatically.)

4. [Optional] Beautify it with some colours and borders (I’m a freak for pretty charts).

OTHER NOTES

If you buy ETFs, or are not buying no-load mutual funds, don’t forget to add the cost of trading to your final “Effective MER” total, to get the full picture.

Anything that is a flat or static number without any formulas in it, you can change/touch, e.g.:

• Portfolio %
• Listed MER %
• TOTAL Portfolio Amount (at the bottom, the $10,000)

Enjoy!