For shares, ETFs, and other investments
You can now get the same spreadsheet I use to track my portfolio, net worth, and to know instantly which ETF to purchase.
To get your free share portfolio tracker, enter your email into the box below.
Quick Links
Why track your holdings?
Setting up your free share portfolio tracker
When you are ready to make a purchase
Extras
How often should I buy shares?
Are there any times I should avoid buying ETFs?
Should I use market or limit orders?
How often should I rebalance?
Why track your holdings?
Capital gains tax
One day you will begin to sell those holdings. It may be a very long time from now, but it will happen. To calculate the capital gains tax owed, you will need to know the purchase price of each parcel you bought along with the associated buying costs (brokerage).
Additionally, when you sell shares, you want to sell the parcel of shares with the least embedded capital gains to continue to compound more of your unrealised capital gains. Unrealised gains are the basis for the tax you owe on capital gains but do not pay until you sell. Meanwhile, it continues to earn money for you. Think of it as an interest-free loan from the government until you sell. When it is time to sell down some shares, you can calculate the capital gains owed on each parcel (including the 50% CGT discount for those held over 12 months) and select the ones with the least capital gains to pay.
Rebalancing
Each time you purchase ETFs, you pay a fixed brokerage fee. So rather than making a purchase each month of all your ETFs (which, for 3 ETFs will mean 3 times the brokerage each month), it is better to alternate and just buy the one furthest below your target. Over time it will naturally tend back towards your target percentages while lowering the cost of purchases.
For instance, let’s say at some point in time, your shares are worth this:
| Previous amounts | Now | |
|---|---|---|
| VAS | $30,000 (30%) | $24,000 (22%) |
| VGS | $50,000 (50%) | $65,000 (59%) |
| VAF | $20,000 (20%) | $21,000 (19%) |
| $100,000 | $110,000 |
In this example, VAS is most below the target (it is 22%, which is furthest below the target of 30%). So, in this case, you would add the entire purchase to VAS. This will keep brokerage costs down, so you lose less money on the way in.
Even if you had $8,000 in this example, which puts it over, you would still add just one purchase to VAS, and next time you would add to whatever was most below your target then.
The only exception is if you had so much that you could split it and brokerage would still be very low. For instance, if you had $20,000, you could split it into 3 or 4 if you wanted because the brokerage is still very cheap when buying $5,000 or more each time.
I use the spreadsheet to enter all my trades, and it automatically shows the following for each ETF:
| • Total shares | tallied up from your purchases entered | ||
| • Current price | automatically updated by Excel | ||
| • Current dollar value | |||
| • Current allocation | percentage relative to all your ETFs | ||
| • Target allocation | you enter this | ||
| • Deviation from target | in dollar terms | ||
| • Rebalancing is required |
Tracking your net worth
There is also a net worth tracking sheet in there. It is a good idea to set a date each month to note down your entire wealth. It really helps motivate you to see it is growing. I have an auto-reminder email sent to myself to do it on the last day of each month.
Setting up your free share portfolio tracker
1. Changing/Adding/Removing ETFs
Changing the existing ETFs
- Go to the stocks sheet.
- Click on the first cell of a row that has an ETF that you don’t use. We will use the example of the first row showing VAS. In this case, click on the cell to the left of the ticker symbol VAS. So J3 (not K3).
- Hit the delete key.
- Type in your ETF ticker name prefixed with the market identifier code and a colon.
For the Australian stock exchange, that is XASX: (e.g. for A200, type “XASX:A200” without the quotes).
Codes for other exchanges can be found here. - Hit enter.
- Click back on that cell.
- Click the ‘Data‘ menu group at the top of excel, and then the click the Stocks icon in Data types box.
- Right click the little icon that appears to the right of the cell and click “Name”.
Adding new ETFs
- Select an existing row from column J through S.
- Right click, select Insert and select shift cells down.
- In column J of that row, type XASX:AAA but replace AAA with the ticker symbol of your ETF.
If you are buying on other exchanges besdies the ASX, prefix codes can be found here. - Select cells K to S in the above row for another fund and at the bottom right of the last cell, click on the small square (called a ‘fill handle’) and drag it down to copy it to the new row.
Removing ETFs
- Select the row that you want to remove. But make sure to select only columns J through S.
- Right-click and select Delete and then Shift cells up.
2. Set your target allocation
In the target allocation column, set your percentages so they add up to 100%.
These resources will help you decide on an asset allocation:
- The risk-reward spectrum
- Asset allocation and your risk tolerance
- Index funds
- Equity funds
- Bond funds
3. Remove the example trades and add your own
Remove the trades on the left and replace them with your actual trades.
When you are ready to make a purchase
When you are ready to add money, just look at column R, pick the most negative and purchase that.
After you have made the purchase, add the purchase information to the left side of the sheet (image below).
Use ctrl semi-colon to automatically add the current date into the first cell. Then the ticker symbol, number of shares (the cost will automatically be calculated), and the brokerage cost.
When you get dividends, add a row on the left also. But only add the date in the first column and the amount in the Div column, and leave the rest blank.
That’s it!
Extras
How often should I buy shares?
To buy ETFs, you need to buy through a broker, and there is usually a minimum brokerage cost per purchase, so you don’t want to buy too frequently or you will lose too much before your money gets invested. However, waiting too long before investing also means you miss out on market gains.
A good rule of thumb is to save up until your brokerage cost is no more than 0.5% of your purchase, and ideally no more than 0.3%.
For example, if your brokerage fee is $10, you will want to buy in parcels of at least $10/0.5% = $2,000 and preferably at least $10/0.3% = $3,300. So your frequency will be dictated by how long it takes to save up that amount before making a purchase.
If you want to get a more mathematically accurate frequency to buy, this calculator is more precise.
Are there any times I should avoid buying ETFs?
Yes! With ETFs, the price needs to update constantly throughout the day, and the difference between the net asset value (NAV) and the ETF share price can be more volatile during the start and end of the trading day when price discovery is being established by the market.
You should
- Never trade in the first or last 15 minutes of the trading day;
- Never place an order during off-market hours expecting it to be filled at the market open.
Should I use market or limit orders?
Following from the issue with ETFs NAV and share price volatility, you should always use limit orders. It’s just a matter of eliminating tail risk (those couple of times market makers have suddenly disappeared and spreads have blown out).
When you make a purchase, ignore the last price because, for lower liquidity ETFs, the last price could have been hours of trading earlier or even the day before and no longer reflect the price that people are willing to buy and sell at.
Just place your order a few cents below the bid if selling or a few cents above the ask if buying. It will act just like a market order, but you’ll never have any big surprises.
Being tricky and trying to price it lower when buying and higher when selling doesn’t actually help.
Vanguard suggests this for ETFs and calls it placing marketable limit orders.
More info
Does It Matter When You Buy Or Sell An ETF?
The two times during market day when investors should never trade
How often should I rebalance (sell overperformers and buy underperformers)?
For the first several years, you shouldn’t need to rebalance except through contributions (also called rebalancing through “inflows“), as explained above. It is only once you have a sizable portfolio that you need to decide if you will rebalance by selling down or letting your portfolio deviate. Selling down requires realising capital gains, which makes it less tax efficient. So while you can still rebalance with inflows, that is by far the best way.
Later on, you probably want to rebalance between stocks and bonds to maintain your portfolio’s level of risk to your risk tolerance, but I wouldn’t be too worried by letting the stock portions get a little out of whack between each other (e.g. VAS vs VGS).
So if you had $100,000 in there and there was a significant market decline resulting in your stocks falling by 35% (like March 2020), you would rebalance by selling bonds and add to the stock ETF most below its target. But if VAS was 10k over VGS, you could just leave it and keep adding to VGS even though it is below its target.
Or if you had a sizeable portfolio and stocks soared (like they have done recently), and you want to maintain your risk profile of stocks to bonds, you would take from the stocks that are most over their target and sell those to buy bonds.