My investment tracking spreadsheet

I’ve been tracking my portfolio rigorously for over four years now.  One thing I’ve been asked for quite a few times is a copy of the spreadsheet I use to monitor it.  So here goes.

I face three key challenges in tracking my portfolio’s returns:

  1. Unitising the portfolio.  This means correcting for ins/outs – withdrawals or deposits. Just because a £1m portfolio gained £100k doesn’t mean it’s delivered a return of 10%; if £30k of that gain was fresh contributions, for instance.
  2. Evaluating the portfolio’s exposure.  By exposure I mean allocation by geography and allocation by asset type (equities, bonds, etc).  Some platforms let you ‘X-ray’ your holdings but each has a proprietary way of doing it, and many platforms don’t offer any such feature.
  3. Integrating my holdings across multiple accounts.  I have accounts with several brokerages and platforms.  Each has a different way of doing it.  They don’t even use the same tickers for the same underlying assets.  I want a way of pooling all the portfolios into one consistent spreadsheet.

My template spreadsheet is available as a Google Sheet here.  It’s read only, but you can make a copy (either download a copy in Excel, or make a copy in Google Sheets) to edit yourself.  All appropriate disclaimers apply – use at your own risk.

Read the rest of this entry »

Useful ETFs

Exchange Traded Funds (ETFs) make up a large portion of my portfolio (though not all – a subject for a future post). I often struggle to lay my hands on a simple list of the key ETFs when I need it, despite useful resources like Monevator’s Low cost index tracker page.

I’ve just created a reference page listing the useful ETFs I look at. Points to note:

  • It’s laid out the way I look at my portfolio. Equities vs Fixed income. And the geographies are UK/USA/Australia/other.
  • I focus on physical ETFs, not synthetic ones.
  • I prefer the lowest cost ETFs, which are listed first in each category.
  • Everything on this list has a ticker and is easily tradable using an online broker near you.
  • My investment biases/prejudices are visible. I’m barely investing in government bonds right now, given their record low yields, but I do like high yield corporate bonds. I use dividend / value ETFS, but not growth or sector ones.

I’d love any comments/corrections/suggestions.