In my previous post, we examined the differences between time-weighted and money-weighted rates of return and how the latter can be significantly affected by the size and timing of cash flows.
It’s also, therefore, likely to be the most accurate method to calculate your actual personal rate of return as we are highly likely to be contributing (or withdrawing) money into/out of our investments accounts over time as part of a FIRE strategy.
In this short post, I’ll lay out how we can use a spreadsheet to calculate this important measure with just a few bits of data.
The XIRR formula calculates your rate of return on a specific investment, taking into account the size and timing of cash flows – i.e. essentially working out the money-weighted rate of return or personal rate of return.
As we spoke about in the previous post, people often refer to the returns shown by their fund provider on websites or factsheets, which will show the trailing returns for a fund (e.g. 1 year, 3 years, 5 years, etc) and use these as a way to measure their own progress.
However, those are the fund returns based on time-weighted rates of return, rather than money-weighted rates of return. They assume a single investment at the start is held to the end of the period. The impact of cash flows can take you a meaningful distance away (positive or negative) from these quoted rates of returns so it’s important to get a handle on what’s really going on in your accounts.
If you have ever used the Internal Rate of Return (IRR) formula, you’ll know that its limitations lie in that it can only handle money in/money out that occurs at regular intervals. This may well be okay if you pay a fixed amount every month into a savings account for example, but with XIRR, money in and out that is irregular is easy peasy. It also, of course, handles, regular money in and out too, so it’s really a one-stop solution for this type of analysis.
Okay, so what do you need to do this?
Using your spreadsheet application of choice, you need to create two columns of data:
- Dates of cash flows and valuations entered using the ‘=DATE’ function in Excel or Google Sheets
- The value of those cash flows and/or valuations
The XIRR formula only needs these two data points to calculate, but you may find it useful to also list the type of transaction, such as initial value, contribution, withdrawal and final value.
Let’s look at a real example:
My good friend Beth made an initial investment of £15,000 into her Hargreaves Lansdown account back on 1st January 2015. Over the next few years, she made a number of contributions and some withdrawals at irregular intervals and with differing amounts. By the end of the October 2018, Beth noted that the current value of her investment account was now £25,000.
However, she was struggling to really understand what her rate of return had been. She specifically wants to know the annualised rate of return so that she can compare the returns of this account with others in a more meaningful way.
Having mentioned the XIRR function in passing, we agreed to sit down and create a table that broke down all of her transactions to date as follows:
“Note that the DATE function will show dates in U.S. format – this can be edited if desired”
With all her dates and corresponding cash flows logged in the table, together with the last known value of her account, it was then just a simple case of entering the XIRR formula into cell D15 as follows:
- D5:D14 captures the cash flow amounts
- C5:C14 captures the cash flow dates
In the above, example, the personal rate of return Beth achieved (accounting for the size and timings of her cashflows) was 13.36% as shown in cell D15 (the cell we entered the XIRR formula)
“It’s important to mention this is her annualised rate of return. Periods of less than 1-year will be annualised up.”
Armed, with this knowledge, Beth then went away and calculated her XIRR returns for all of her other accounts. She now monitors them in this way, updating the figures monthly as part of her net worth calculations.
It is important that money in and money out are treated differently and consistently when using this formula. Simply put, one group of cashflows needs to be either positive or negative in the calculation with the other group being the opposite of that choice.
In the example above, money sent towards Beth’s investment account (her initial investment and further contributions) were treated as outflows from her personal account and shown as negative values. Conversely, money withdrawn from her investment account (i.e. flowing back to Beth) were shown as positive values, as was the final, last known valuation.
Which way round you choose is entirely your choice – as long as money in and money out are treated differently and consistently, you’ll get the same end result. Just to demonstrate this, here is Beth’s table again with the positive and negative values reversed, yielding the same 13.36%.
Also, while it’s obviously more logical to the transactions listed in date order, the XIRR formula can handle dates in any order as long as they are entered using the DATE function.
So there we have it – a quick and easy way to work out your personal rate of return for a given investment or other savings/retirement account.
So, why don’t you grab your transactions for an account of your choosing and plug-in your dates and cash flows to see what your actual, personal rates of return have been over time? You might find there is a material difference to the quoted fund returns you may have been relying on previously.
If you’d like a copy of the Google Sheet used in this example, you can grab a copy here
Until next time, thanks for reading!