Stress-testing my retirement spreadsheet

Apodemus

Having finally got a spreadsheet where I can vary all the potential elements of my future retirement income, I want to stress-test it by modelling various worst-case scenarios.

Obviously, for DC pension, SIPP and unwrapped investments, worst-case is that the underlying investments become completely worthless. However, that level of total market collapse seems pretty unlikely, so I wonder what level of downside risk others would use to stress-test their assumptions?

At present my figures look ok when I model a 40% market drop on eve of retirement and no recovery in the thirty years of the model. Is that pessimistic enough?

Aegis

Apodemus wrote: »

Having finally got a spreadsheet where I can vary all the potential elements of my future retirement income, I want to stress-test it by modelling various worst-case scenarios.

Obviously, for DC pension, SIPP and unwrapped investments, worst-case is that the underlying investments become completely worthless. However, that level of total market collapse seems pretty unlikely, so I wonder what level of downside risk others would use to stress-test their assumptions?

At present my figures look ok when I model a 40% market drop on eve of retirement and no recovery in the thirty years of the model. Is that pessimistic enough?

That's very pessimistic indeed, and will factor in the worst possible timing of that one single event (though depending on your risk exposure, potentially not the limit of the actual maximum likely loss). The only other thing you could feasibly do is model a series of drops and recoveries to bring in the sequence of return risk, but to be honest, a loss of 40% is probably enough.


You might also want to look into adding care home costs onto the end of the model, just in case.

Apodemus

Aegis wrote: »

That's very pessimistic indeed, and will factor in the worst possible timing of that one single event (though depending on your risk exposure, potentially not the limit of the actual maximum likely loss). The only other thing you could feasibly do is model a series of drops and recoveries to bring in the sequence of return risk, but to be honest, a loss of 40% is probably enough.


You might also want to look into adding care home costs onto the end of the model, just in case.

Thanks, while I’ve not explicitly modelled care home in the spreadsheet, I judge a successful outcome as a balance at the end that would cover 2-4 years of the additional expenditure that care home residence would represent.

Sea_Shell

Have you factored in high inflation too!!

10%?

Apodemus

Sea_Shell wrote: »

Have you factored in high inflation too!!

10%?

Yes and no! Since I am old enough to have been doing economics exams in 1976 and 1977 when UK inflation had briefly reached about 25%, inflation risk ranks highly in my concerns.

However, I tend to feel that inflation is the opposite side of the coin from the market collapse risk, which is almost certainly deflationary. So I am assuming that in a high inflation scenario, asset values (and hence investment values and returns) are likely to rise. There may still be market underperformance but I would think that the total effect would be less than the equivalent downside risk from a 40% market collapse?

The 1970s comparison would show that through the high inflation years, there were sporadic, dramatic market drops (73% fall in the FT30 at one point!), but these were short-lived and accompanied by large, rapid rebounds.

Problem is that to protect against market drops one would want to be holding more cash, which would be at greater risk from inflation. My scenario planning involves holding a two-year buffer that is fed from investments.

Audaxer

Apodemus wrote: »

At present my figures look ok when I model a 40% market drop on eve of retirement and no recovery in the thirty years of the model. Is that pessimistic enough?

So if I understand it correctly, under that worst case scenario, say you had £200k invested which dropped to £160k on the eve of your retirement, and markets didn't recover for 30 years, you would still have enough income from the £160k to last for the 30 years? If you can survive comfortably on that amount, would you not have been in better position just keeping the £200k in cash savings and drawing from that rather than £160k in investments?

Albermarle

So if I understand it correctly, under that worst case scenario, say you had £200k invested which dropped to £160K

£200K minus 40% is actually £120K .
Although that in fact makes the point even more strongly of staying in cash instead.

Audaxer

Albermarle wrote: »

£200K minus 40% is actually £120K .

Oops, I must have had a senior moment when doing the maths.

Although that in fact makes the point even more strongly of staying in cash instead.

I recently read one investment guru (I can't remember who) saying that occasionally the best advice he has given a client is there is no need for them to invest at all in that they already had enough to be set up for life.

Apodemus

Audaxer wrote: »

So if I understand it correctly, under that worst case scenario, say you had £200k invested which dropped to £160k on the eve of your retirement, and markets didn't recover for 30 years, you would still have enough income from the £160k to last for the 30 years? If you can survive comfortably on that amount, would you not have been in better position just keeping the £200k in cash savings and drawing from that rather than £160k in investments?

Well, yes... but in that case I would possibly be better going for an annuity.

In practice there is a big difference between the figures being Ok to survive on under a worst case scenario and being as comfortable as I might like under a reasonable scenario. And then there is always the hope that everything exceeds my wildest projections and I can live a life of luxury!

Audaxer

Apodemus wrote: »

In practice there is a big difference between the figures being Ok to survive on under a worst case scenario and being as comfortable as I might like under a reasonable scenario. And then there is always the hope that everything exceeds my wildest projections and I can live a life of luxury!

That's true if it's only going to allow you to scrape by. If however someone had other secure pension income like DB and State Pensions and maybe only required another thousand or so a year to live very comfortably, then just keeping the £200k as cash savings, maybe would be a better option that taking any unnecessary investment risk.