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.
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).
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).
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)
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.
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.
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.
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!]
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.
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