Financials Tracking

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.