What now? Money-weighted? Is this some new humblebrag? Where you make out you’ve got so much money that you have to weigh it rather than count it?
If only.
Tracking your money-weighted return is a commonly accepted method of measuring your investment performance, if you’re an everyday kind of investor.
For that reason, I use money-weighted returns to track the Slow & Steady [1] passive portfolio.
Slow & Steady portfolio tracker download – We’ve had a fair few requests over the years to make the Slow & Steady portfolio tracker spreadsheet [2] publicly available. Ta-dah! There it is at last. I’ve finally cleaned it up so it’s fit for human consumption. Please copy the template and adapt as you like for your own portfolio tracking needs.
Why am I stressing this money-weighted return business?
Because most of the returns we see in fun-time stories like “The FTSE 100 has gone nowhere for 20 years” or similar do not necessarily reflect our personal investment experience.
The financial industry prefers to report time-weighted returns, but there are many other different methods.
Money-weighted returns show a personal return that adjusts for common investor behaviour such as pound-cost averaging [3] or crazy market-timing stunts.
Money-weighted vs time-weighted returns
It’s a clash of the accounting titans:
Time-weighted returns – This is how indices and funds typically report returns. The procedure strips away the impact of cashflows in and out of an investment. It measures how a portfolio performed over a specific length of time – with all time periods weighted equally. This makes sense if you’re a fund manager publishing your annual performance report. You don’t want to be judged on the whims of active investors withdrawing a billion pounds last quarter because Bitcoin had a good month, for example.
Money-weighted returns – This approach captures the effect of contributions and withdrawals on your returns as opposed to eliminating them. Time periods in which you have more money invested have more impact on your overall return. It’s likely to be a better reflection of your investment experience, especially if you pay in £500 a month, say, instead of £6,000 at the beginning of the year.
If you made a big withdrawal to avoid a market crash or bet large on an anticipated surge then you’d get some sense of your ‘skill’ (or good luck) by comparing your money-weighted return versus your time-weighted return.
The Investor wrote a good piece on unitising your portfolio [4]. Doing so enables you to track your time-weighted returns.
You’re now reading the money-weighted sequel to that post and it’s only taken me five years to get around to it. (Luckily The Investor does not demand time-weighted invoices).
XIRR: the money-weighted annualised return formula
XIRR is a formula that enables you to calculate your money-weighted rate of return.
You just need a spreadsheet and a few pieces of information:
- The dates you made any contributions and withdrawals from your portfolio.
- The value of your portfolio and/or holdings.
That’s it.
Tracking your portfolio using XIRR gives you a ‘personal return’ because it’s sensitive to your specific contribution and withdrawal history. Even if you hold a one-fund portfolio, your returns will differ from somebody else with the self-same portfolio due to your different trading histories.
The example below shows you how to use the XIRR formula to produce an annualised return [5] using Google Sheets or Excel.
Set up your columns and rows like this:
[6]
The dates formula is used in every cell of the dates column.
The XIRR formula is used in the annualised return cell that shows 16.87% in the example above.
The final rows in the formula (D17 and C17 in the example) refer to the grey ‘total portfolio value’ row.
XIRR: what counts as cash flow
- Do not include dividends as a positive cashflow. The reason is that we’re measuring total return here. So when you reinvest your dividends, their effect upon performance will show up in your portfolio’s total value.
- Dividends withdrawn from your account do count as negative cashflows because they are a reduction of your total return.
- Do not count platform fees, dealing fees, and other expenses that are taken from your account. You don’t chalk these fees up as a negative cashflow because the dosh you put in to cover them shows in your positive cashflows, while its loss is felt in your portfolio’s lower total value. The money went on some asset manager’s cigars rather than on more assets compounding for you.
- If, however, you pay fees from a separate account then do input those as negative cashflows.
- If you sell an investment to cash and reinvest it then that doesn’t count as cashflow. If you withdraw that cash from your portfolio then of course that’s a negative cashflow.
You can aggregate multiple accounts into one XIRR annualised return figure by pooling all cashflows and values using the layout in the spreadsheet example above.
XIRR: trouble-shooting
Make sure you input your positive and negative signs correctly.
#NUM! error in your XIRR annualised return cell usually means the values in the grey ‘total portfolio value’ row haven’t been changed, or the XIRR formula wasn’t adjusted when the grey row moved down after new cashflow dates were entered.
The error can also mean the estimated return ‘guess’ needs to change. The guess (see the 0.05 number in the XIRR example above) gives the formula a starting point from which it can iterate the result. Try values from -0.1 to 0.1 if you get a #NUM! Error. After that you’ll need to spelunk for an answer on the internet forums – the Bogleheads are very good on XIRR – or try our comments below.
XIRR gives an annualised return. Investments held for a short-time may look Buffett-beatingly good on this view. For example, a new fund that gains 10% in a month will show an annualised return of over 207% using the standard XIRR formula. And sure, that’s what you’ll earn if that momentum is maintained over 12 months.
Back in the real world, you can apply a year-to-date XIRR formula to show how you’re really doing.
Substitute the standard formula for this beauty:
=SUM(((1+XIRR(B1:B4,A1:A4,0.05))^(((DATE(2019,1,31)-(DATE(2018,12,31)))/365))-1))
Here’s the year-to-date formula in action:
[7]
The year-to-date formula is used in the cell that shows 10% in the example above.
The final rows in the formula (B3 and A3 in the example) refer to the grey ‘total portfolio value’ row.
Switch out the year-to-date formula once your investment reaches its first birthday.
There are variant XIRR formulas available. These include ones that let you account for leap years if you need precision like a Swiss watchmaker.
You can get a real rather than nominal return by subtracting average annual inflation for the period from your annualised return. Use an inflation calculator to help you do this.
Remember XIRR is good but it’s not perfect. Google it and you will find some complex forum debates that work through the more outlandish scenarios like Kasparov-level chess problems.
If that’s not your cup of Novichok then know that XIRR is good enough for most people in most situations but other return measures are available.
Here’s one we made earlier
Our Slow & Steady portfolio tracker template [8] shows you how to track your annualised return across multiple funds within a portfolio (see the Cashflows tab).
It’ll also help you see how the XIRR formula works so you can use it to create your own money-weighted returns.
I recommend building your own spreadsheet that’s fully customised for your own brain. It’s a good way of keeping yourself out of mischief on dark winter nights.
Elsewhere:
- The Bogleheads do a good spreadsheet [9] that tracks money-weighted and time-weighted returns.
- FIRE v London put together an absolute humdinger that you can plunder for more portfolio-tracking ideas [10].
- Morningstar’s Portfolio Manager [11] also tracks your returns from multiple angles and sucks in their financial data too.
- Our very own Lars Koijer has put together a YouTube series on building your own [12] spreadsheet.
Finally, my co-blogger The Investor alludes to his own legendary spreadsheet that you’ll sometimes hear talked about in hushed (/skeptical) tones. It’s unitised [4], because The Investor likes to pretend to himself that he woulda, coulda, shoulda been the next Warren Buffett.
Perhaps if we lobby him hard enough he’ll release it into the wild – in five more years.
How do you track your returns? Let us know in the comments below.
Take it steady,
The Accumulator