How I track investment returns

Investment returns tracker

I’ve promised this for a long time. I’ve finally got round to tidying up my investment returns tracking spreadsheet!

Here’s the link to the spreadsheet: https://drive.google.com/open?id=1gi7unt7BQ9QxkudPar3kSy8dufB-vx9IS7kw-nbDo8M

[Note: you need to go to ‘File’ -> ‘Make a copy’ to be able to edit it and use it for yourself, please don’t request access to edit!]

An overview of how it works

A general tip is that anything highlighted in yellow is where you enter data. Everything else is automatic.

Returns

Investment returns tracker

The front page is in the Returns tab. At the top is the money-weighted return (IRR). There is also a time-weighted return, and a unit return (these are effectively the same/very similar, mainly there for my curiosity). These are measured over various periods from 1 month through to 10 years (annualised). Have a read of this if you want to know more about the difference between money and time-weighted returns.

Below these are year-by-year returns.

On the right, we have the overall cost of the portfolio expressed as a percentage. That includes both fund charges and platform costs (more on that in a bit).

Finally, we have some comparisons of our fund to a benchmark. Including the Information Ratio and .

Portfolio

This is where you enter your start date and the names of your accounts (the yellow boxes). In total there are 20 account tabs. You don’t need to use them all (Mrs YFG and I have 9 accounts across a number of brokers).

Allocations

Investment returns allocation

This tab has all the fancy allocation tables and charts which you may have seen in my ‘How I invest my money‘ post. There are a few places to enter data (the yellow boxes).

Portfolio tabs (numbered 1 through 20)

Investment returns portfolio tracker

This is where you enter your portfolio data. In the top left, this is where we enter our investments and details. If you hold listed securities (ETFs, shares, Investment Trusts etc.) then Google Finance will automatically return the prices (blue boxes). This used to work for mutual funds too. However, it seems Google has discontinued the service – you’ll have to enter data manually. There are further boxes where you enter the fund fees, and Class, Geography and Type (these are drop downs linked to the ‘Lookups’ tab, see below).

Many platforms offer a data export tool. In the area below you can paste this data in. If prefered, you can link the table to this if it’s easier.

To the right, we have the contribution/withdrawal table. Each month end you will need to fill this in so that returns are calculated.

On the far right, we have a table for on-going value. This doesn’t link anywhere, so it’s optional. But may be useful to see movements of particular funds/investments over time.

Calculations

This is the engine room of the spreadsheet. No need to enter any data here. I can’t remember exactly, but I think this is based on a Bogleheads spreadsheet.

Benchmarking

Investment returns benchmark

This tab is optional (unless you want benchmarking data). There are two options: “Google Finance” or “Manual”.

If you use Google Finance, you enter the ticker of your index or ETF benchmark (cell I11) and all the calculations flow out.

If you use Manual, you enter the data yourself manually. You’d use this option if you wanted to use a mutual fund as a benchmark. Such as a Vanguard LifeStrategy fund.

Calcs

This brings together all the data from the portfolio tabs so that these are summarised for the Allocation tables and charts.

In here you need to enter the platform cost for each portfolio in the yellow boxes (Column M). [I know it’s not good practice to have to enter data in a summary calculation tab, but I got lazy!]

Lookups

Finally, there is the Lookups tab which is used for creating the various drop-down menus you see throughout the spreadsheet. Add and remove to these lists as you need.

That’s it!

Here again is the link to the spreadsheet: https://drive.google.com/open?id=1gi7unt7BQ9QxkudPar3kSy8dufB-vx9IS7kw-nbDo8M

Please leave a comment or send me an email if you have any questions or spot any errors! Feedback is very welcome, so if you have any suggestions for improving the spreadsheet please let me know.

All the best,

Young FI Guy

16 thoughts on “How I track investment returns

  1. Hi. Thanks for this. It looks very useful. Can you explain how the links to Google Finance work for UK shares v US Shares. For example if I put FCPT into the portfolio it returns Four Corners Property Trust rather than F&C Commercial Property Trust. If I put LON: as a prefix doesn’t seen to help either. Have you come across this and found a solution?

    1. Hi Jon, Google Finance can be a bit fiddly. I’ve just had a go for you. If you type: “LON:FCPT” (no space), it seems to work. You’ll need to adjust the price though as it’s quoted in pence (again Google Finance struggles with that too). Let me know if you’re still not having any luck.

  2. Hi, thanks for sharing. Do you also find that Google Finance will sometimes return an error value for a particular stock or ETF for no obvious reason? Of my holdings, VEUR in particular seems to go down pretty regularly, but then after a day or two it’ll mysteriously start returning value again.

    Has anyone else had this problem? and if so, is there a solution (other than just updating it manually until Google Finance starts playing along again)?

    1. Hi Jo, yes Google Finance can play up a bit. Generally speaking, it is ok, but there are times when it has brain-farts. I’ve not come across any ‘fix’, from what I can tell it’s a data issue. (p.s. I hold VEUR too)

  3. YFG, I don’t do spreadsheets for my finances for Reasons (Oh, OK, it’s because a) I’m lazy and b) because I spent too many years at the start of my career as doing financial modelling and I am too aware of how spreadsheets can give unwarranted confidence in what they say in the wrong hands). But this is beautiful and you’re making it available to everyone! Great work. It deserves to have a wide audience!

  4. This looks nice, thanks for sharing I’m going to take a closer look when I’m not on my phone. I do sheets with live prices for my portfolios and link them together in a master sheet but nowhere as organised as this! I use a price unit column to solve the problem of Google Finance returning pounds or pence – so it will be 1.0 or 100.0 depending on the ticker. I too find that sometimes Google Finance loses a ticker and everything is messed up – for some of the Vanguard ETFs especially and sometimes you just need to drop the LON: prefix – weird stuff. For some mutual funds it is possible to import a price lookup table if they have it on a web site using an HTML import sheets function. For my L&G funds I do this with IMPORTHTML(“http://www.legalandgeneral.com/investment/ifaUnitTrustPrices_index.jsp”,”table”,5) for example. This is OK until they revamp their site with occasional updates. So if your fund manager has a table online like this, it may work. You can then use a VLOOKUP sheet function in another sheet using the fund name to get prices from the imported table. It takes some trial and error but it works for me and saves doing manual entry.

    1. Thanks TI. There’s a joy in creating your own spreadsheets. With their idiosyncrasies. There’s no such thing as a perfect spreadsheet. (OK, that’s enough excel geeking.)

  5. Very nice spreadsheet, thanks for sharing. I recently worked on making one for me and a particularly troubling part was the asset allocation. I wanted to see the exposure across geographies and asset class, but with a detailed top-down breakdown. For example if I own a global equity fund, I wanted the end allocation to be broken down by region, as opposed to show a separate allocation for global equities. So I created a master list of all assets I invest and there I have percentages for exposure to different regions, so MSCI World for example shows as 62% North America, 6% UK, 14% Dev Europe ex UK, 9% Japan, 9% Asia Pacific. This is manually inputted, once per fund. Same for asset class, which is usually 100% of one asset class, but sometimes there could be hybrids. It took a lot of work and although it works ok, I’m not perfectly happy with it because it’s not easy to change. For example now I want to break down the bonds to two separate categories, corporate and government, and it’s very difficult to do. I still prefer it to having each asset belong to a single category though, because then aggregating them doesn’t give you a good overview of exposure. For example if you own ETFs that are global equities, global equities ex-UK, global equities ex-US, developed markets equities, developed markets ex-US/ex-UK, Developed Europe including/excluding UK, etc. etc., it’s not practical to have each of them a separate category. I prefer my approach in this regard but your spreadsheet is miles better in pretty much every other aspect. I wonder if you could incorporate my idea in yours and share it back. I can send a stripped down version of it if you are interested.

    1. Hi theta, thanks for sharing. The best bet for that level of granularity is using the x-ray (or portfolio) tools on your broker platform. It’d be a nightmare to get it work for all funds etc.

Have some thoughts or comments? Please share!

This site uses Akismet to reduce spam. Learn how your comment data is processed.