(Request) Spreadsheet/Model comparing after tax returns of ETF, Life assurance, Directly held shares, non tax relieved pension contribution?

S

SPC100

Registered User
Messages
1,162
Hi,

Does anyone have a good spreadsheet or model comparing the potential after tax returns from the various potential investment mechanisms?

There are at least these set of options available to folks
  • ETF,
  • Life assurance,
  • Directly held shares,
  • non tax relieved pension contribution.
Obviously a lot depends on which shares/markets are being invested in, their future prices and their future dividends, not to mention future taxation policy. But those could be variables in the model.

Thanks!
 
I think that there's a danger of getting bogged down in overanalysis in this context. There's no point in trying to predict the respective gross returns on the different options or try to accurately compare the after charges/tax net returns on hypothetical scenarios. So, better to just recognise that the latter two are more attractive from a tax/charges point of view compared to the first two. Then use that information to choose the appropriate investment route. Once again I'll link to my recent post in a similar vein.

What to do with a million euros?

Personal details Your age: 49 Your spouse's age: 54 Number and age of children: 2 aged 9 and 14 Income and expenditure Annual gross income from employment or profession: €30,000 (state job) Annual gross income of spouse/partner: €60000 (state job) Pension: have maxed AVC’s (should I also...
www.askaboutmoney.com
 
ClubMan said:
There's no point in trying to predict the respective gross returns on the different options or try to accurately compare the after charges/tax net returns on hypothetical scenarios.
Click to expand...

Yes, but that is not what he is asking.

If I decide to invest in shares, then I would like to know which is the most cost-efficient and tax-efficient vehicle.

Brendan
 
If this was tidied up/condensed/added to you might have a solid foundation for the suitability of each option?

The Disclosure Schedule that a client receives on a Life Assurance investment gives a illustrative table of projected benefits and charges (including taxation to date) for years 1-5, 10, 15, 20. It's based on an assumed return of 4.67% pa.

If there was something similar for ETFs and shares?
 
I have a spreadsheet which I put together a few years ago for my own use which looks something like this.



The figures are likely out of date or in some cases were just plain wrong at the time. But I think the structure might still be useful if others were willing to help update the input variables.

I am happy to share this if it is of use...
 
Beyond costs, the other key determinant will be how much tax reduces the compound return.

Let's ignore non tax relieved pension option for a moment. and look at ETF vs direct shares.

If, as per above, we assume that direct shares can equally capture the 'stockmarket gain of an index'. I think the comparison boils down to whether the income tax cost (annual dividends) from holding shares directly is better or worse than the tax drag from the 8 year deemed disposal.

That is clearly sensitive to how much of the return arrives as capital growth vs income.

If the direct shares basket happened to issue no dividends, it seems clear that direct shares would win, as they would then compound tax free.

If the direct shares basket returned all it's gain in dividends, and if that income was taxed at higher rate, and it seems clear ETFs will win, as they get the benefit of tax free compounding for 8 years.

Conclusion: For ETF vs shares modelling, we need to be mindful about how much of the gain we assume is from dividends and how much is from capital growth.
 
jpd said:
You need to explain how you calculate the figures from one year to the next

For example how do you get from 98,500 to 104,804 in the case of Income ETF
Click to expand...

First off - a disclaimer/confession. I did this sheet back in 2018 when I was even more confused about Exit Tax than I am now. So it's probably not perfect but it might help the OP as a starting point.

I can update the sheet based on any comments that are posted here so that it will hopefully be more useful (both for myself and anyone else that wants a copy).

I have already made a few changes to address a few comments and to correct a few errors (sorry!) The revised sheet now looks something like this.

 
SPC100 said:
Conclusion: For ETF vs shares modelling, we need to be mindful about how much of the gain we assume is from dividends and how much is from capital growth.
Click to expand...

Yes - that makes sense. Any suggestions on a reasonable split for capital appreciation vs dividends? I have in my latest post above set these at 3% / 2.5% respectively in response to some comments that my original figures were too high. (I had originally used some figures from my own ETF's which have performed at 7-8% over the last ~10 years or so.)
 

I have assumed that the shares will return 0.5% more than an ETF due to lower charges, but I might be wrong.
 


Why is the capital gain higher in the directly held shares than the ETF? You should assume that they are the same.

And maybe show the investment charges separately? Or would that make it unwieldy?
 
As ever, the caveat about looking at returns over a specific start/end date period of time applies, but...

Backtesting for the European index investor

Backtest by Curvo is the best backtesting simulator for European index investors. Discover the historical performance of your portfolio and compare it to others.
curvo.eu

 
If my calculations are correct, the relative merits of each option is not that sensitive to the total return that you assume. Using either 5% or 8% does change things but only a little.

The relative outcome is much more impacted by the spilt in gain from dividends vs capital growth.
The marginal rate of tax that you expect to pay on distributions and dividends is also a big factor.
 
Gordon Gekko said:
How is it unattainable?

It’s not wildly out of step with long-term returns from global equities.
Click to expand...
most investment funds and individual investors only achieve 4.5% a year on average, therefore using 8% as a benchmark for growth is not what the majority of people and funds will be achieving, its an ideal but not typical . There weren't too many people investing everything in the global msci ETF 16 years ago, I don't think it even existed back then as ETFs were still in their infancy. Many irish people were investing in banks, builders like McInerney , baltimore technologies, Elan etc. Therefore doing comparisons based on this ideal performance is not realistic as the majority of funds and investors will never achieve it
 
3CC said:
I have assumed that the shares will return 0.5% more than an ETF due to lower charges, but I might be wrong.
Click to expand...

I would default to all having same growth performance. And model cost seperately.

If we are trying to model cost, I guess it depends on how good a model we are trying to build. or which lifecycle investment we are trying to model, e.g. lump-sum investor, accumulating investor, drawing down investor etc.,

I think there is potentially a few categories, not sure if we need them all though.

I think there is probably original investment/setup cost, onging investment maintenance cost, re-investment (or additional new investment) cost, drawdown costs

e.g. a buy and hold share investor, has some setup cost, has ~0 ongoing maintenance cost, had some cost to invest new money, and has some cost to drawdown from investment.

Alhough even then, if the dividend income > drawdown, then there would be no drawdown cost!

It might be simplest to assume we are focussed on the investor who has a lump-sum, or a lump-sum and accumulating more.
 

In this old post (by @Brendan Burgess highlighting content from @Steven Barrett ) and commented on by @Gordon Gekko, it was suggested that the split should be roughly 2% (dividend) and the rest (4% or 5%) growth. We probably can find actual data for a given index, but I think that would be good enough to start. @AJAM also features in that thread too
 
Last edited:
Hi Everyone,

Thanks for all those constructive comments. I have updated the sheet now and I think I have addressed everything raised.

The only thing that I have not included is the dealing charges of selling at the end. It's not hard to add this in but it's probably similar across all options so does not have a big influence on the result.



Just one thing I need to verify. Are Income ETFs subject to deemed disposal every 8 years? I had assumed not but now I am questioning that.

Thanks.
 
Yes, Distributing ETFs are subject to deemed disposal

CGT has no effect on ETFs - all distributions (dividends, deemed and actual disposals) are subject to Exit Tax only
 
Reactions: 3CC
You must log in or register to reply here.
Menu
Log in
Register