How to calculate your true net worth?

This blog post is the fourth in a series in which I integrate all of my personal financial accounts with LUSID - our open, API-first, developer friendly investment data platform - to get a comprehensive picture of my net worth. In this post I will demonstrate the benefit of having integrated financial data from four different sources including:

  1. A Monzo Current Account
  2. A Vanguard ISA account
  3. An AMEX Credit Card
  4. An Australian Pension Fund (*this blog post)

You may recall last time in Analyse financial information from multiple providers in one place - AMEX I added all the transactions from my AMEX AUD Velocity credit card into LUSID. This allowed me to compare my outstanding credit to the combined assets of my Monzo and Vanguard accounts.

In this blog I’m focusing on adding my account with Australia's largest superannuation and pension fund Australian Super. This means I'll be able to see my pension savings alongside all my other holdings to get a picture of my net wealth.

Getting the Data

Unfortunately there is no API access to my superannuation data so I had to sign in to my account online where I followed the steps below:

I logged in to my Australian Super account and clicked “Transactions” in the pane on the left.AustralianSuperHomeClean

On the transactions page, I went through the statements for each year and clicked "Export Transactions".AustralianSuperTransactionsClean

This gave me a CSV (comma separated values) file containing my transactions. You can see an example of the "Current statement" below.

AustralianSuperSheetClean

Making the data machine friendly

Looking through the exports, I have to congratulate Australian Super. Compared with Vanguard and AMEX these exports are designed to be both human and machine readable. There was no need to muck around removing empty leading and trailing rows and I was able to import and join each statement very simply.

ReadFiles

 This gave me my transaction and balance history since June 2015 for my Super account.

Set my balances

Each statement contained an opening balance as well as a full list of transactions for the year.

Before adding in my transactions I wanted to extract my yearly opening balances and set these in LUSID. This would mean that no matter what transactions I added I would always start each year with the correct opening balance.

LoadHoldings

You can see the effect of this on my Australian Super portfolio in LUSID in the diagram below. For July 1st in each year from 2014 to 2019 I have set the target balance of my account.  This appears as 'Set Holdings' in the chart. 

Behind the scenes LUSID does this by posting an adjustment transaction for each "Set Holding" request. This transaction is dynamic and automatically updates to ensure that these positions are always kept intact no matter what activity takes place between each opening balance. What this means is that I can be confident my balances will always be correct at the start of each year. This is especially valuable when you are not confident that all of your transactions will add up to give you the correct balance. 

SetHoldingsGraphic

 



 

 

 

 

Below you can see a screenshot of the "AdjustmentIncrease" transactions posted by LUSID for each of the "Set Holdings" request.

AdjustmentTransactionsClean

Add my Transactions

With my start of year balances set I could now enrich my account with all of its transactions while still being confident that my start of year balances will always be correct.

LoadTransactions

So that LUSID knows how to process the transactions to generate my positions I added the AusSuper - SuperAccountTransaction as an alias to the Vanguard - CashOnly transaction type as they both result in the same underlying economic movements. This means that whenever LUSID comes across a transaction with the type "SuperAccountTransaction" from the source "AusSuper" it will apply the same effects as the "CashOnly" type from the source "Vanguard". 

This is the first transaction type that I have found so far that actually is shared between different accounts. 

Source Transaction Type Cash Balance Security Balance Properties
AMEX CreditCardTransaction -decrease n/a n/a
Monzo CurrentAccountTransaction +increase n/a pot_name = "current_account"
Monzo DeclinedCurrentAccountTransaction n/a n/a n/a
Monzo CurrentAccountPotTransfer +increase / -decrease n/a

pot_name = "current_account"

Vanguard / AusSuper CashOnly / SuperAccountTransaction +increase n/a n/a
Vanguard InvestmentOnly n/a +increase n/a

 

Generate my holdings

LUSID can now run the entire transaction event history and generate my Super Account balance for every day since July of 2014.  This is incredibly powerful as even when logged into my Australian Super account, I can't get this level of granularity. 

UpsertTransactionsGraphic

Interestingly along with highlighting my lack of contributions over the past 18 months, the transactions actually process perfectly to give me the correct opening balance for each year. This means that the "AdjustmentIncrease" transactions we saw earlier are no longer required and have automatically disappeared for every year except for the initial balance of my account in 2014.

Had the transactions in a particular year not summed to the start of next year's holding, the AdjustmentIncrease would remain in place until such time as I had found the source of the discrepancy. This makes truing up a historic portfolio or set of transactions exceptionally easy to do immediately, with the ability to correct adjustments at a later date.

You can see this in the updated screenshot of my transactions below.

Transactions

With my superannuation account now added in I can start to get a picture of how my net worth has changed over the last couple of years. You can see this in the stacked area chart below.

Pretty cool that despite each of these four accounts being from completely different sources in which the positions and transactions are all represented in differently I am now able to combine them all together in once place, in a single currency to get not just a snapshot, but a time series of how my net wealth has changed over the past couple of years. 

NetWealth

 

 

Get our top content on data strategy for the investment world in your inbox once a month

 
 

Subscribe to our blog and insights.

Sign up to receive our top content on data strategy for the investment world in your inbox once a month