Over on the excellent Monevator site the following question was posed by Gregory today:
I suggested that firstly Gregory hadn’t really given us enough information:
I however went on to detail an Excel sheet to do this with Tyro responding:
So for Tyro (and hopefully others) here it is with formulas displayed so you can reconstruct the spreadsheet:
This is then what the spreadsheet would actually look like with a bit of formatting:
The cells in yellow are the variables you would enter. The Sell/(Buy) column details the action that needs to be taken to rebalance. A positive number means you are selling while a negative number means you are buying.
I hope readers find it useful. If you spot any errors do let me know and I’ll correct. My pre-prepared excuse is it’s a busy weekend so I’ve pulled this together in just a few minutes rather than a few hours.
As always DYOR.
“You are an early retiree and have a portfolio of £875,000. You don’t follow the 5/25 rule but withdraw Your inflation adjusted money and rebalance Your portfolio once a year for example on Your birthday. On Your birthday You want to withdraw £27200. The current (£;%) and target asset allocations:
- UK equities: £70000; 8% vs. 6% target
- Developed world ex-UK equities: £350000; 40% vs. 38% target
- Global small cap equities: £70000; 8% vs. 7% target
- Emerging market equities: £96250 ; 11% vs. 10% target
- Global property: £35000; 4% vs. 7% target
- UK gilts: £192500 22% vs. 26% target
- UK index-linked gilts: £61250 7% vs. 6% target
How would You withdraw and rebalance?”
I suggested that firstly Gregory hadn’t really given us enough information:
- You say you don’t want to use the 5/25 Rule but don’t detail what rule you are using. Surely you’re not going to rebalance every fund no matter how far from nominal you are as that would incur trading costs that might not be economically sensible. I’m going to assume you’ll rebalance if an asset class deviates more than £4,000 which means in this instance you’re going to be buying/selling every asset class this time around.
- You don’t say if your assets are held in Inc or Acc products. Given you have no cash anywhere I’ll assume Acc. Inc would have made this easier during both the accrual and drawdown phases IMHO but let’s move on.
I however went on to detail an Excel sheet to do this with Tyro responding:
“The spreadsheet looks like it might be very useful to more of us than solely Gregory, but I can’t visualize how to put it together (some of us have very primitive Excel skills, yes, my head is hanging), especially the combination of cells and percentages. And I don’t understand Column E or what ‘fill down’ means. Any chance you could rustle up a template that could be downloaded by Monevatoristas?
Worth a try ….”
So for Tyro (and hopefully others) here it is with formulas displayed so you can reconstruct the spreadsheet:
Click to enlarge, Annual Excel rebalancing calculator with formulas displayed
This is then what the spreadsheet would actually look like with a bit of formatting:
Click to enlarge, Annual Excel rebalancing calculator
I hope readers find it useful. If you spot any errors do let me know and I’ll correct. My pre-prepared excuse is it’s a busy weekend so I’ve pulled this together in just a few minutes rather than a few hours.
As always DYOR.
Hi RIT, a couple of suggestions perhaps - the current %age could be a calc (current fund value divided by total in cell B13), to reduce inputs, and a total of target %age might be useful to ensure 100%.
ReplyDeleteI'm generally a 'lurker', so I'll just use this opportunity whilst commenting to say that I've really enjoyed your blog and seeing your progress over the years. Congratulations, and good luck for the move. Thanks
Spreadsheet? Really? It's only just harder than the sort of mental arithmetic test we used to do at primary school. Thus start with the sales:
ReplyDeleteUK equity: to go from 8% to 6% you must reduce by a quarter therefore sell £17.5k.
Followed by: sell £17.5k, sell £9k, sell £9k and lastly ILGs sell £9k. Add 'em up: total sales £62k.
Buys: Global Prop £27k, FIGs £35k, total purchase £62k. Which checks. Since the divisions were all done mentally I'd be quite happy if the totals checked to within a thousand or two, which is all the accuracy that's needed in practice anyway.
I withdraw my comment. I'd missed the point about withdrawing cash. That would call for a second pass through the arithmetic or a different approach from the beginning. That would probably make it worth reaching for a pocket calculator, in which case maybe it would indeed be worth having a spreadsheet instead. The penalty of looking at blogs in the small hours, eh? Off to bed.
ReplyDeleteI've got it! I'd need to modify my method only by having an extra entry "cash" which has to grow from zero (or near zero) to the desired sum. Then the other current%s would be suitably changed and my simple procedure would be applied. So it's still largely mental arithmetic. Hurray! Time for lunch as a reward.
DeleteThanks RIT, very handy for those who like their asset allocation to be precise.
ReplyDeletePersonally though, I'm fairly relaxed about the rebalancing I do in my son's Junior ISA. As long as the allocations are approximately right that's good enough for me, especially given the level of uncertainty about future returns from each asset class.
However, I'm not quite as relaxed about rebalancing as Jack Bogle. He think it's largely a waste of time...
2 tips
ReplyDeleteDon't take out the pension cash on your birthday, do it in March else HMRC will assume you are going to do it every month until the end of the tax year, and impose high tax which you'll have to claim back. March is good for ISA decisions too.
If your funds are not tax-protected, you'll need a CGT spreadsheet for those parts too.