There was some discussion recently in the MMM forum. The
context was tracking the cost base of share parcels for capital gains tax. I
promised to share how I go about this and monitoring overall financials with my
spreadsheet. Sorry, I’m not going to provide a template, just a description of
how I do it. I believe in DIY and encourage people to take ownership by
building their own tools. Furthermore, I’m not even sure if what I’m doing is a
best practice worth sharing, it’s just what I’m doing.
My spreadsheet has evolved over time. Initially the purpose
was Net Worth tracking. This extended to calculating the Asset Allocation. Then
I added Transaction and Dividend’s history, as well as several other adhoc
worksheets which I will not mention here, seeing as they are “adhoc”.
Net Worth worksheet
This is basically a
balance sheet for those who have done any accounting. List out all your assets
and liabilities. I use the following headings : Property, Shares, Cash/Term
Deposit/Fixed Interest, Superannuation, Tax. Under each heading, include
separate rows to list specific assets or sub categories. E.g. Under property
you might have PPOR (Primary Place of residence), IP1 (Investment property 1),
IP2, ..... Sub categories for shares might be – Australian Direct holdings,
Australia ETF, Global ex US, US, Global hedged. Cash/TD/FI and Super are self
explanatory. Under Tax I include known liabilities, e.g. Capital Gains Tax on
IP’s that I intend to sell one day. You might also have Income Tax liabilities
accruing. I don’t include potential CGT on shares since I intend to hold the
shares “forever”. Then the columns are Asset value, debt owing,
Net income, Yield. Note - The last two are not “balance sheet” items, but I
also use this to calculate my passive income so they are needed.
After completing the resultant table, you should be able to
calculate Net Worth, which is the sum of all the asset values less the debt
owing on those assets. For my purposes, I don’t include personal assets (e.g.
contents, cars) in this.
As mentioned earlier, this was the original intent of the
spreadsheet. To keep a track of NW as the basic goal post for our progress
towards FI. However over time it evolved and I also wanted to add in Asset
Allocation. To the right of the NW balance sheet, I added a table for Asset
Allocation. This breaks down the assets into the various asset classes : Growth
(Aus Shares, Global shares, Property) and Defensive (Cash, FI, TD). You will
need to include your superannuation asset allocation, to translate the super
balance into these asset class categories. I also subtract my “emergency fund”
from the Cash, to keep this outside the Asset Allocation. I construct the table
to show my AA inside and outside Super, as well as the overall/combined AA.
Then I include my target AA and the delta between Actual and Target. I have
some conditional formatting to highlight in green or red those positive and
negative deltas (for those inclined to add such bells and whistles). This is
how I manage my investments to try and keep in line with the plan. As we are
still making regular contributions, I stay balanced by investing the next chunk
in the asset class that is below target. This avoids making sales and incurring
extra transaction costs and incurring capital gains/losses.
Trades Log worksheet
This is where I track of all my transactions. Everytime I
transact any shares, ETF’s, managed funds or super contributions, I keep a log
of it here. I also include and shares bought under DRP’s (Dividend Reinvestment
Plans). This requires some discipline to update the spreadsheet everytime, but
I think it’s a simple and quick task and helps me cross check to ensure nothing
goes missing. The columns are date, name, category (e.g. Shares, ETF, Mgd Fund,
Super), $, Units, Price, Brokerage, Other Costs, Tax deferred income (each year
a new column), Cost Base.
The further functions of this sheet are :
CG cost base : Calculate the cost base for CGT for each
parcel. For ETF’s with tax deferred income, this is updated at the end of each
tax year to show the reduction in cost base due to tax deferred income in these
funds.
Cross link to NW worksheet : Every time I add purchases to
my core portfolio (which is basically four ETFs – VAS IOZ VTS and VEU) , this
is automatically updated in the NW Worksheet using a sumif() formula that cross
links to the Trades Log. That makes it very easy and effortless to see the
latest impact on Asset Allocation as soon as new trades are entered and the
current ETF prices are input. It can also be used to do “what if” analysis by
adding in future purchases and see how much of each ETF is needed to achieve
the desired AA.
Dividends worksheet
This worksheet is
used to track dividends. Each line is a dividend or distribution. The columns
record the details, i.e. Record date, Payment date, c/share, Franked Dividend,
Unfranked Dividend, Franking credit, Withholding tax, FX rate (if applicable), etc.
Again it requires a small effort. Everytime I receive an electronic dividend
statement, at the same time as e-filing this away, I just enter the data as a
new row in this worksheet. It comes in handy at tax time to cross check
whatever is pre-filled in your eTax. It can also help monitor your expected
dividend income, in case you want to take any actions to optimise your tax
(e.g. making voluntary after tax super contributions which might be deductible
if you are self-employed or unemployed).
Other useful features
One big bonus is the auto-filter function in Excel. I use it
on both the Trades Log and Dividends worksheet. That way I can easily sort data
to answer questions such as – How much have I invested in the past few months ?
How many transactions did I make in the last financial year ? How much have I
contributed to super (after tax) versus the concessional limit? What is my
total purchase cost for VAS ? How much dividends have I received for VAS ? etc.
There is much more you can do with structured data using pivot tables, but I
haven’t found the need as yet.
