Monevator: The UK's leading website for private investors

Portfolio tracking: how to track your investments using a money-weighted return

by The Accumulator on November 3, 2020

A graph showing the rollercoaster journey of portfolio returns.

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 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 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 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. 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 using Google Sheets or Excel.

Set up your columns and rows like this:

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:

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 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 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.

Morningstar’s Portfolio Manager 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 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, 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

{ 54 comments }

Weekend reading: Triage

by The Investor on October 31, 2020

I reluctantly updated a lot of the technology that runs Monevator this week. And of course things broke.

For instance, pagination and numbering has come unstuck on the longer comment threads. For now we’re either showing up to 200 comments at once on (nearly all posts) or else the most recent remainder (the broker comparison table, and a couple of old Brexit ding-dongs).

If you spot any other bugs please do let us know.

Also, as detailed below I’m going to experiment with keeping these Weekend Reading comments Covid-free.

We’ve had a great discussion here over the past few months for those who’ve wanted it. But more than a few readers feel it’s now crowding out the investing chit-chat, which is the primary reason anyone comes to Monevator.

Ideally us diehards will continue debating, but only on this older thread please.

Be warned: Given the state of travel with the pandemic and the economy I’ll likely do a dedicated virus post again at some point over the next few weeks. It’s just too important to our finances to leave off this site entirely.

But for now I’m going to have to delete Covid-related comments on this post. Apologies in advance. Please comment (even if about this new comment policy) on the other thread please.

Have a great weekend!

[continue reading…]

{ 39 comments }

The facts of investing life

Updated by The Accumulator on October 27, 2020

Which is more important in your world: knowledge or skills? This question is the fulcrum of an ideological battle being fought over our education system, and it’s a debate that also has ramifications for DIY investors.

The champions of knowledge believe we need to command certain basic facts to operate effectively in society – the essentials of science, literature, history, and geography. Without these fundamentals, you’re like a driver who can’t read road signs or markings.

The advocates of skill argue that a robotic recall of verb conjugations and the Kings of England should take a back seat. They favour building prowess in problem-solving, debating, hypothesising, and so on.

While the obvious answer to my opening question is “Can I have both, please?”, I believe that knowledge is far more important to a DIY investor than skill.

Licence to skill

I spent a long time at the beginning of my investment journey furrowing my brow over the fact that I didn’t know how to read company accounts, or work out the discount rate of future cash flows, or have the seemingly God-given ability of my peers to declare the market ‘fair value’.

Such skills seemed like concealed weapons. You’d only glimpse a flash, enough to let you know ‘they’ could slip through the investing maze like ninjas. But the application of these powers was as shadowy as their utterances of success.

Now I just don’t worry about it.

Even talking heads in the fund management industry are happy to go on record to say that skill is so abundant in their field that the winners and losers are mostly separated by luck.

And that suits me. Because whereas skill might let you play in the deep end of the pool, knowledge enables you to stay safe in the shallows.

It’s the difference between taking a couple of martial arts lessons and walking in to the wrong pub shouting “I’ll take you all on”, and knowing enough to avoid staring at the shaven-headed gentleman with a spider-web tattoo on his face.

The basic facts are much easier to acquire for an amateur than superior skills. Knowledge is an all-you-can-eat buffet in the digital age. You can stuff your face with it via blogs and books.

The only question is what you choose to swallow.

Knowledge Base Alpha

My research quickly taught me to stop asking the question that all my non-investing friends ask:

“What’s hot?”

It doesn’t matter. By the time an asset class is hot, it’s probably already too late to make big money.

I don’t worry about tips from commentators or newspapers. Even if they throw the dart in the right direction, the smart money has already moved on.

The market is the consensus of expectations on the future of a stock. Only unexpected events can shift the price. How can you predict the unexpected? The short answer is: you can’t.

I know that diversification is the only free lunch going. I load up on all the useful asset classes while resisting the siren call of ‘the next big thing’.

I understand how asset classes work. I know that equities are volatile and that nothing is truly safe. If the market crashes I won’t panic because I know it will probably recover. I won’t hunker down in cash, refusing to believe in inflation.

BRICs? Gold? Hedge funds? The Fear Index? Low vol ETFs? High yield funds? I know that keeping up with fashion and labels will cost me dear.

Behavioural finance tells me that I’m a pitiful collection of psychological defects, about as capable of self-discipline as a bonobo at a swinger’s party.

The more we learn, the less we know

In the face of all the evidence that I can’t market-time, pick winners or even trust myself, passive investing using index funds is the only strategy I feel confident enough to stake my financial future on.

Sure, I love reading yarns about the prospects for gold, timber, or Lego bricks, but I now know that’s no basis for a portfolio.

My best bet is to invest in a diversified portfolio of low-cost trackers and sit tight.

Doing very little sounds too easy. In some ways it is. Portfolio maintenance costs me less than an hour a month.

But it’s simultaneously the hardest part of passive investing.

Surely I should be doing something? Anything? Surely I’ve got enough skill by now to test my mettle in the market with more active investment strategies?

No. I’d be better off composing a passive investor’s prayer that wards off temptation.

Staying on the straight and narrow is hard, but knowledge and education are the best way to keep on track.

Take it steady,

The Accumulator

{ 26 comments }

Weekend reading: Here’s one we made earlier

by The Investor on October 24, 2020

What caught my eye this week.

Rummaging around to select an old post from the Monevator ‘archive-ator’ each week can be bittersweet.

On the one hand, it seems I used to be smarter, wittier, and more productive.

But a compensation for these memento mori moments is coming across those little nuggets that have aged rather better than me.

Take these comments on tech stocks from December 2010, in a post about what I called the investor sentiment cycle:

For a contrasting unloved sector, consider technology companies.

It’s hard to remember a time when half the office owned shares in nonsense companies like Baltimore, Webvan, and NTX. Yet it was only a brief decade ago that the Dotcom stocks were doubling in a month on a good press release and a name change.

Today roughly nobody except institutional investors bothers with individual technology shares.

Yet the Nasdaq tech market in the US has been quietly beating the Dow and the S&P 500 for months.

Maybe the seeds are being planted for a new boom in technology share investing:

The first shoots will be obscure magazine articles on the Nasdaq’s recovery.

Then you’ll discover a friend or a bulletin board poster who has tripled his money betting on cloud computing micro-caps.

Perhaps Facebook or Twitter will float for what will seem a crazy valuation, but will look positively modest a few years later.

…and so on.

It was very hard for most people to care much about tech stocks in 2010. That was why I used them as my illustration.

Yet by the end of 2019, the tech sector had proven the best place to have been invested for a decade.

And 2020 has only kept that up with knobs on!

I don’t bring this up (entirely) to blow my own trumpet. Digging through the archives also reveals plenty of howlers. (London residential property is massively over-priced in 2007, anyone? Oops.)

Rather, it’s fun to see that Monevator is now so old we’ve actually been around to see some of these cycles play out.

Tech’s unexpected recovery since 2009 proves the point I was making. Investing and the economy are cyclical, and investor sentiment can be downright faddish.

Never expect the status quo in the markets to hold forever.

It won’t.

Working from home poll

Exciting additional news: the results are in from last week’s poll. Over 2,000 of you voted to say you would prefer to work from home:

So two-thirds of Monevator readers would like to work from home most or all of the time. Interesting!

I guess the remaining 4% are brain surgeons worried about the carpets.

Have a great weekend.

[continue reading…]

{ 45 comments }

Next Posts Previous Posts