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 do 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 »

Progress against my 2016 goals in Q4

My goals in 2016 for each quarter were as follows:

  1. For my net loan to shrink by £10k per quarter, without any margin calls.
  2. Maintain investment income of at least £Xk
  3. Closely track my target asset allocation

Goal 1: For my net loan to shrink by £10k/qtr

Read the rest of this entry »

2016’s amazing performance

A year ago I was scrabbling for funds to buy a house, the market was down about 5 points in a month, and Brexit seemed like a tail risk. What a difference a year makes.

My investment portfolio finished 2016 up 24%.  A record year.  Am I a genius?  Was I lucky?  Was this normal for stock market investors?

I will wager that most investors, even the sophisticated risk-friendly readers of this blog, returned less than 20% annual gain last year. Feel free to let me know your returns in the comments below as I’d be delighted to hear there are hundreds of similar ‘achievements’ out there but somehow I doubt it (1).

What’s been going on?  Well FTSE-100 reached a record high.  It’s the red line (‘UKX’) in my graph below.  It was in fact up about 14% on the year, plus dividends.  So a purely UK equity investor should have been well into double digits.

Bonds had an amazing year too.  Despite entering 2016 at ‘unsustainably high levels’, they carried on climbing.   At one point in August UK corporate bonds (purple, SLXX) were up 18% in the year.  They finished up about 10%.  Very few investors would be purely fixed income let alone purely corporate bonds.  But a balanced portfolio of, say, 60% equity 40% bonds would have returned about 13%.

If your portfolio returned less than 13% then you have materially underperformed.  Which is quite a statement.

Of course as my readers will know I invest much more widely than just the UK.  The UK accounts for about 6% of the world’s stock market.  The USA is about 50% of it.  How has the USA done?  Well its bonds (purple, AGG, in the graph below) have not moved in the year, unlike the UK’s (actually they did move *in* the year but they ended up where they started).

2016 returns by asset graph.png

Read the rest of this entry »