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
“crazy market-timing stunts”, i’ve been called some things in my time but…. yep im guilty
great article thanks
Thanks for that timely post, just as I was trying to work out how to calculate my returns from platforms that, unlike Vanguard, do not give “proper” numbers, just some raw gain (/loss).
Actually, for one Vanguard account, I briefly showed a positive IRR despite having lost actual £££, so I probably still don’t understand IRR. Still, as noted, a dark winter beckons.
I calculate the cost as actual cost including stamp duty and commission – sales including stamp duty and commission and then do the same with the number of shares.
Then no of shares * share price today – actual cost = profit. Then divide from there.
Probably a long way of going around it.
HL have a very different way of doing it, they take the weighted average cost price – weighted average sale price then multiple by the number of shares. etc
Really interesting article. The images showing the formulae would be easier to follow if you included the row numbers as well as the column letters.
This makes my brain melt.
But – if you are paying fees from a separate account, don’t you need to add these as a *positive* and not a *negative* cashflow? They are essentially an extra investment to achieve the same final balance, are they not?
If you enter them as a negative cash flow, it looks as though the fund has paid _you_ (which would obviously be nice).
How do I track my returns?
I don’t. I add up the total value of my investments about once or twice a year. That’s it.
I’m not really sure what action I would take if I did try to calculate returns in more detail?
I try to minimise costs and my risk profile isn’t going to change based on that information.
I track my monthly transactions across multiple accounts, funds and ETFs, it is reassuring to see my actual returns aligning with my expected returns over the last 5+ years I’ve been tracking.
It has also nudged me into making decisions about abandoning certain classes as my risk profile changes, steadily working from a starting 80/20 equities/fixed income to a longer term 60/40 eventually. When emerging markets small capital dropped below 5% of my allocation, it wasn’t such a hard decision to drop it out completely looking at my historical returns and realizing that even a 10 year run like US equities have had would have little impact on my overall return, since they are such a tiny part of my portfolio.
Great work TA, thanks for sharing.
@ Miner – 🙂
@ Madflier – you could do it that way too. Or you could mark a negative cashflow like that in red and not count it in whatever system you use to tot up divis. Either is viable.
@ Vanguardfan – in many ways I agree. All I cared about latterly was hitting my FI number. The annualised return ceased to matter, especially as I knew my portfolio was right for me and I wasn’t going to change it because UK equities were having a horrorshow half-decade, or whatever. Still, I have found tracking the numbers endlessly humbling, especially when contrasted with prevailing opinions from ten years ago:
https://monevator.com/10-year-retrospective-what-a-decade-of-returns-tells-us-about-passive-investing/
Very useful article, thanks. (I’ve asked about this topic in a comment in the past, so it’s great to see it all set out.)
I use my own spreadsheet based on the same principles as above, calculating the money-weighted CAGR since the beginning of time for each fund, and the year-to-date return for the total. The results are similar (but not identical) to XIRR using a lot more columns, but I like knowing exactly what it’s doing rather than using a black box. Probably not perfect, but works well enough – and for me it was as much about solving a puzzle as about tracking anything. (Plus I didn’t know about XIRR back then…)
In terms of how things are going: My own ISA is along similar lines to the slow&steady (but more UK heavy), while my other half’s is LS60. They have different histories, but the overall returns are pretty much the same, give or take the background noise. Interesting to see how that overall return balances out when the components parts vary so much: US being way out in front, FTSE100 way behind.
@TA Thanks for the link – and the ‘humdinger’ epithet! You’ve linked to my original post – which gives the best intro – but my latest spreadsheet, linked to in blue in that post, uses Google Sheets and does pull in real time stock prices for auto updating. It also has a rudimentary/unreliable way of pulling in Fund prices from Hargreaves/Fidelity etc. It also has several hundred securities categorised into geography / asset type, with (often dated) data about yield, fees, etc. Hopefully this will be helpful to some of your readers.
Would you get a similar number just by mean averaging your profit?
Interesting article, not sure I’d use it, it’s mostly for proving one’s active credentials, although I suppose you could ascertain how much better lump sum is usually vs drip feeding to ascertain whether it is worth breaking your back (borrowing/dipping into cash reserves/overtime) to make a lump sum
Could have some meaning for tax planning in deaccumulation
Thanks for another informative article.
As usual, in and of itself, the returns information is pretty useless – it is what you do with it that matters.
Interesting, but not sure I would use it myself. (Can’t work out what happens if you put in a lump sum but don’t invest it straight away, and then distribute it across a range of funds and / or then trade within the pf.) May be useful for those nearer the beginning of their investing time horizon, where new contributions have a bigger impact, but I prefer unitising and time-weighted performance tracking, having been inspired by TI some years ago.
From the US – thank your for the morning after election distraction (seriously) and headache as I wrap my head around your math. Winter is here and this will keep me busy for days
@StellaR – The Slow & Steady portfolio tracker link shows you how to distribute across funds. Uninvested cash is treated as explained above. It comes in as a positive cashflow. If you left it in cash and followed the formula, adding a paltry sum of interest to the total value then you’d see a pretty poor annualised return. Anyway, I get you’re more of a unitisation person.
@ Blaine – no worries. I’m finding it difficult to tear myself away from the election too. Hope you’re doing OK. It’s nerve-wracking to say the least.
Is it pure coincidence that I asked this very question on the FIREUK Reddit forum a few days ago, wondering how to apply Monevator’s unitization method, and concluding that maybe I could just do XIRR instead: https://www.reddit.com/r/FIREUK/comments/jmmv5d/monevator_method_to_unitize_portfolio_help/?utm_medium=android_app&utm_source=share
Not mathematically or computer gifted so……
Run Quicken 2004 for day to day expenses etc-has useful graphics etc for Investment portfolio too as well as normal expenditures-absolute figures
Does take into account withdrawals /additions as all financial inputs and outputs are entered(currently I am retired 17 years)
Run a dummy portfolio at Trustnet of Portfolio Investments only-I have 3 Index Tracker Funds only so fairly simple
Gives me enough of a handle on my investments to inform me of my SWR which is what interests me
xxd09
@xxd09:
Re: “Gives me enough of a handle on my investments to inform me of my SWR ….”
A very nice real world example of what you might do with such information – see #12 above.
All very true
But you can do nothing without out data
You are just shooting blind with out it
Needless to say all my spending goes on good works-me?
xxd09
@xxdo9 – that’s interesting to know. I guess you have to use Quicken 2004 because support was abandoned for it, or later versions don’t have the same feature set? Is it increasingly harder to use or is there a good community that somehow keeps it going?
I wonder what other software others use to track their portfolios?
Internal rate of return is very useful in some circumstances, such as calculating the yield to maturity of a bond or set of future cash flows from a project, but I have never really seen the point of calculating it for my own portfolio. After a few years accumulating (or decumulating) IRR is largely driven by investment returns anyway, unless you are regularly swinging large amounts in and out of your portfolio. But if you are doing that you can run into the problem of uniqueness (multiple IRRs satisfying a given set of cashflows) that was discussed in the comments section of the Monevator article on unitisation.
If I get an IRR of 7% (or indeed a unit performance of 7%) since some point in the past, what use is that knowledge to me?
@naeclue:
re “…. what use is that knowledge to me?”
In and of itself the return information (in your example, of 7%) is pretty useless. However, were you to be following a plan that required an IRR of, for example, 4% then you could feel you were currently ahead of plan whereas if your plan required say 10% then I would start to look at my options for some form of course correction.
So, perhaps a bit like the current heading on a compass at sea.
I have always visualised XIRR as answering the question ;
“If all my deposits and withdrawals went into/came from a fixed rate savings account, what interest rate would that account have to pay for me to end up with my current portfolio value?”
i.e. in the example, if you had funded a FIXED RATE savings account at 16.87% on the dates given, and made two withdrawals on the dates given, you would have ended up with £10,640
This may be slightly, or utterly, wrong. Please let me know.
Accumulator-Quicken 2004 continues to work without support
Will not work on Win10
Does not update Fund prices-I take these from the dummy Trustnet portfolio as required and feed them in.
Admittedly I do no trading-one sale of funds pa to replenish cash living expenses account
Only having 3 funds to track makes life very simple(cheap)
I have bought a second hand dell laptop with Win XP installed as a backup-Quicken 2004 will go forever-certainty see me out
xxd09
@Al Cam, if saving towards a goal, I still don’t see how what the IRR has been is going to help you. If I can save 20k per year over 20 years with a target of reaching 1m, I need an IRR of about 5.1%. If after 5 years, I only got 4%, then I now there will be a shortfall unless I get more than 5.1% or make other adjustments. But that is really all the 4% can tell you. It cannot tell you what IRR you need now. To do that you start with the amount you have now, which you don’t need to calculate.
@BerkshirePat, yes that is one way of thinking about internal rate of return. Essentially, if you discount every cashflow (including the initial and final) by a rate r and the sum of those discounted flows comes to zero, then r is the internal rate of return. So to spin that round, if you have a fixed rate savings account as you describe, then the fixed rate would equal r.
For pedantics out there, I should call r AN internal rate of return rather than THE internal rate of return as in some situations it is possible to have more than one r that can work.
Interesting article, but …
Why use US date format? Internally a date is just a number with a weird display format applied. XIRR uses the number.
Why use the DATE function for a list of literal (not calculated) dates? My spreadsheet allows date literals to be input in the familiar dd/mm/yyyy format (or just dd/mm for the current year).
Apart from that, an interesting article, and maybe I’ll use XIRR alongside unitisation.
@naeclue:
Yup, given that scenario I agree a shortfall seems likely, and the option set would be something like:
save more PA;
save for longer;
aim for a higher return – possibly by taking more risk;
accept shortfall is likely – and check again to see if this is really the “need”;
wait & see / hope;
some combination of the above
@ Naeclue – in the first half of my investing journey – I found it very useful to understand how my investments were performing versus expectations.
Psychologically it was comforting to know that things were on track.
It was educational to see bonds behave like bonds, emerging markets like emerging markets and so on.
It helped give me a feel for how my investments were behaving.
And let’s face it, a certain type of human just loves measuring things.
@EcoMiser, yes I didn’t follow the date bit – when I do this I just copy and paste dates into the relevant column and Excel is quite happy! It all looks like 44140 or whatever to Excel
And we need line numbers 🙂
A very good article and the understanding of your numbers to F.I.R.E and beyond is critical. Your FIRE fund is second only to the health and well-being of your family. It will dwarf your mortgage of anything else in your life so treat it as such. It’s a truly beautiful thing in that it allows you to opt out of the stress of the modern working life and allows you to do exactly what you want to Regard it as potentially a 30 or 40 year school holiday if you are lucky and you have put in the work.
I was very fortunate to go on an advance Excel course with work many years ago and used it to create a complex way to track my route to freedom complete with graphs and formulas. Why do you need to know month by month what the headwinds were with the economy and whether the Argentinian economy had crashed or there was an oil price reversal. Simple, the spread sheet is your plan to freedom where you use it as a learning tool. It’s your history of how you did it and is a learning tool of what is critical and has saved me from following the crowd as it exposes poor performances. Better this than being in a class action against an underperforming fund manager!
I could put it this way. If you are young and new to FIRE then the route to get there is unbelievably complex. You have a mortgage, a few children who you need to feed and put them possibly through university. You may have a partner who wants exotic holidays (do your remember those!). You then will need to save a substantial amount against a background of other stresses on your finances such as losing a job or two. To me the only way is to have a plan with goals and understand your numbers deeply. What your portfolio is made up of (SIPP, ISA pension etc) and what tax will you pay, what risk sectors you are exposed to.
There are three variables to investing and two of them are linked. The first is CAPITAL and the amount of monies that you put away each month. The next is TIME to fire and is affected by by capital outlay and the third critical factor and that is rate of RETURN. Invest in low performing zombie funds you may as well not bother. So the question how do you know what poor is other than tracking what you do have and use it to make switching decisions and try to remove emotion from the process. The economy has crashed before and when you can visibly see this in your reporting graphs in previous years it has a calming effect. You can see when things are doing well and you can reduce your positions here with any sector or funds that are over performing.
A few weeks ago I had a pub meeting of ‘six’ with some old colleagues. One of them asked me if I am still doing my investing and why was I wasting time and not using an financial investor. I used to sit next to him and try and enlighten him as to why not to buy UK commercial property via the company pension. He is now in his fifties and has been made unemployed and is wondering if he will ever get another job on even half the salary. I did not have the nerve to tell him this is why I worked a spreadsheet and invested my time in Excel.
@Jonathan#31 et al
Not wishing to “teach my granny how to suck eggs” but for those using Excel or any other spreadsheet for their monitoring, e.g. I still happily “chug along” with Microsoft Works, it is supremely important that you have a full back-up. Personally I use two separate USB sticks, which I religiously save my S/S to after every entry session, be it a single data entry item or many, and I store each of these sticks in a separate location within my home.
I just typed it in and get the same result with manually-entered UK format dates
I have no idea why Excel Help says you have to use the date() function. And US date format – why ? It’s just a representation of the underlying sequential number that Excel uses (41201 etc)
I can’t paste a snapshot but here is text ‘proof’..
31/12/2018 3000 16.8714%
18/03/2019 2500
03/09/2019 -600
28/12/2019 1550
15/02/2020 2900
06/05/2020 -770
24/10/2020 -10640
Thanks. Agree with the points regarding date formats above. Just to also add that you could use a MIN in the ‘year to date’ formula to cap the effect of the adjustment to one year if required, i.e.
=((1+XIRR(B2:B3,A2:A3))^(MIN(365,A3-A2)/365))-1
Might be useful to someone
=SUM(((1+XIRR(B2:B3,A2:A3,0.05))^(((B3-B2))/365))-1))
Ooops, please ignore the 2nd forum;a at the bottom of the comment as it shouldn’t be there (/is nonsense… 🙁 )
* formula, not formu;a…
I think I’ll go and have another coffee….
PS, I will just say thanks again to this site and the people that run it – it’s fantastic
Silly question time. As I have stopped adding to my investment pot, nor withdrawing from it, I’ve no need for money weighted return calcs?
Thank you.
@Jonathan
As someone who achieved FIRE 13 years ago and likes a spreadsheet, as a challenge/project. I had great fun in replicating a Quicken type setup in Excel using arrays, when recalculation times got to around 20 seconds using a high end Mac and you have around 100 worksheets you know it has got out of hand…
I concentrate now on knowing the inflation adjusted value of my portfolio at the beginning of each tax year, how much was spent the previous year. Fundamentally this is all I need to know, (I know I have 10 years expenses alongside a large equity portfolio).
I can get time weighted returns from the components of the portfolio without calculating it myself from product providers. I can get my IRR from the year end values and knowing what I spent each year.
The previous return is just history and whilst it’s interesting, you must concentrate on where you are now.
Data is important but don’t lose sight of the wood for the trees…
“But why were tech stocks rallying the most? Um, that was because the US Federal Reserve would have to keep rates lower for longer in an uncertain and undecided America.”
No, it was because, with a gridlocked Washington and a democrat in The White House, big tech firms are much less likely to get their monopolies broken up.
@Hariseldon
Yup, yesterday is gone.
A great article on this – comparing retirement to Markov chains – is given at
http://www.theretirementcafe.com/2016/04/a-random-walk-sequential-game-part-3.html
IMO, Dirk , as usual, gives a very clear explanation.
@ Gary – it’s up to you. If you have no need to know and don’t care then great. If you’re asking which method to choose in your situation then it doesn’t matter.
Funnily enough, I had a conversation with a close relation who doesn’t need to fret about the performance of their portfolio – they have enough in pensions to cover them no matter what.
Except they worry about performance constantly, they want their money to ‘do well’.
Tracking performance isn’t doing their peace of mind any good.
@Gary, in your case future IRR will be the same as CAGR.
@Malcolm
I can’t speak for Quicken 2004 but can for Quicken 2000. I’ve been using Quicken for some 25 years and Quicken 2000 for 20 years. When I installed Windows 10 on my 11 year old Dell desktop PC I managed to get Quicken 2000 running on it with no problems.
My old Dell is now so slow that I am in the process of decommissioning it in favour of a an Acer Chromebook 314 (the full HD and touchscreen version of the 314) purchased in the summer and which I’m really pleased with. I will update Quicken at the end of 2020 before running and exporting numerous reports so I have a record of how well/badly I did over my 30 (?) years of investing. Quicken will then disappear from my life when the PC goes in January.
I now use Google Sheets to keep track of what is in my 3 SIPPs and 3 ISAs. I have now been retired for 5 years and, from January, will no longer be investing new money in my name but will continue adding to index trackers in my 3 adult children’s SIPPs instead. Thanks to a decent final salary pension, plus full state pension from the end of next year, I also have no need to withdraw capital or dividends from any of my portfolios. I have been wondering for a while about whether to set up time-weighted or money-weighted calculations in a spreadsheet to take over the performance reporting duties from Quicken. However, given the above, and the fact that I am now fully into index-trackers (Woodford was the final straw that persuaded me to sell my final batch of active funds), I’m not sure there is any point. A simple closing valuation vs opening valuation once a year will probably suffice.
Sean-I bought a second hand Dell laptop running XP from Amazon last year and have nothing but Quicken 2004 on it-nothing else-No wi fi etc
Cost £150.00
Therefore very fast!
I would miss it -my right hand!
Should do the business to the finish!
xxd09
That sounds good. I might well have looked at following your lead except that I think I now only want to track my (cash) income and expenditure and keep a record of the values of each of my funds. The latter is easier now since I hold far fewer funds than I used to. The Google Sheets file I set up on the Chromebook, and which is also accessible from other devices, is working fine for that. Given that my intention now is to not add or withdraw anything from my ISAs and SIPPs I think I can live without all the fancy stuff I’ve been getting from Quicken for 25 years of so. It was nice to have but I think I’ve weaned myself off it over the last 2 or 3 months.
@Sean:
Sounds to me like you have successfully accumulated enough and then safely navigated the Gap from when you retired until you will be fully pensionised with all your foreseeable needs and wants covered! If I have understood this correctly, from here on your SIPP’s and ISA’s are risk reserves and/or rainy day/gifting/legacy funds. A nice place to get to. And, therefore your proposed future monitoring approach sounds very appropriate to me. I assume you will track against some form of benchmarks?
If I can be very nosy: how did your SIPPs and ISA’s do during your Gap – ie did they shrink, stay flat, or even grow and how does this compare with your expectation for them when you retired?
@Al Cam
You’re correct about me considering my SIPPs and ISAs as legacy funds. I consider myself to have been very fortunate in having a very decent final salary pension, as well as high rainy day cash savings, which is why I (hopefully) won’t need to drawdown from my investments. My adult children will be much less fortunate than I was so I would like share some of my good fortune with them by contributing to their SIPPs and leaving mine untouched for them to benefit from upon my demise.
I took voluntary redundancy (with 2 years pay, most of which I put into a SIPP) 8 years before my DB pension started at 60 but then did contract work for the next 7 years, mostly part time, so I only had a one year gap in which to live off savings. That year was 2015, which I believe was pretty much a flat year for markets. Until recently I was mostly in index trackers, albeit heavily overweight UK, with some active funds, all of which performed well apart from Woodford. Overall performance is therefore normally better than the UK market but worse than the world index. With hindsight I wish I had put more into global trackers and less into FTSE100 and FTSE250 trackers. I don’t want to sell low and buy high to rectify this though so I now content myself with reinvesting the dividends from UK trackers (all are INC) into my existing International ex UK (ACC) funds. The UK proportion is therefore slowly reducing due both to that and to the lower UK growth.
@Sean:
Thanks for the additional info.
I agree DB pensions are/were great!
Having said that, DC/SIPP’s are [now] much more flexible than DB’s.
So a mix (as in your case) is definitely not a bad choice; along with ISA’s for early access/tax management issues and then, if feasible (ie you are lucky enough to have enough dosh), GIA’s become helpful too. Beyond that, as I understand things, it gets more complicated.
Hello
Is it more accurate to use the date of contributions or the date of fund purchases. This changes the XIIR as I normally do a contribution to my SIPP but then wait for the tax relief to be credited before I do a purchase. Thank you
Just a note that you have an error in your YTD formula where the copyable text is wrong and does not match the image (which is right).
“Substitute the standard formula for this beauty:
=SUM(((1+XIRR(B1:B4,A1:A4,0.05))^(((DATE(2019,12,28)-(DATE(2020,6,29)))/365))-1))”
Should be instead:
“Substitute the standard formula for this beauty:
=SUM(((1+XIRR(B1:B4,A1:A4,0.05))^(((DATE(2019,01,31)-(DATE(2018,12,31)))/365))-1))”
Keep up the great work. From my modelling it turns out that EM and Small Cap have been on a tearaway in 2021 so far. One to keep an eye on for rebalancing 🙂
Thanks for the blog, only just discovered it as I was running through my options on TWR and MWR. Unitization is a great idea and I’m struggling to figure out why my unparalleled knowledge and genius in absolutely everything under the sun didn’t think of this. I used to monitor weekly returns for individual positions and for the portfolio as a whole, which clearly matters as I want to know how each holding is performing. This has been a rollercoaster but it has taught me to manage my emotions – or is teaching me. And the emotional risk is ever present and I get that the moment you think you can manage it is when you are at your most vulnerable. I was using a simple basic price gain to monitor and then moved on to HPR, which is of course the basis for TWR. What I have done is subtracted any inward cash flows from the totals so that cash inputs do not distort the gains. This is clearly not viable long term and starts to create havoc and my monitoring graph has blown up! Unitization and the use of both TWR and MWR seems to be the best option and once set up is relatively straightforward to maintain. Joy…err…So, thanks again. Good to know others are out there working through the same things.
Another comment on the XIRR formula. What’s the purpose of starting =SUM? You aren’t summing anything and I’ve found it works without.
Thanks for all the work and encouragement you give. Been following for years. Cheers.
So I’ve taken the plunge and decided to make my own financial spreadsheet (that very closely resembles the slow and steady template for some bizarre reason!) but I do have a technical question to ask. When it comes to MWR and recording cash flows how do I account for funds that I have in-part sold?
For example, in 2017 I buy £3000 of an EM fund, I don’t contribute any more to it and in 2021 it’s grown to £4000, I then switch (sell) £1200 into 2 new funds. Does that count as negative cashflow in the original EM fund and positive in the 2 new funds?
@ Matthew Powell – thank you for spotting that. Corrected. Monevator’s comment notification system is so creaky it’s only just told me people have been commenting on this thread!
@ Adam – yes, you’re spot on. Hope the spreadsheet gives you many years of faithful service 🙂