Have you got a spreadsheet to plan your retirement forecast and income?

Triumph13

brewerdave wrote: »

I've got several spreadsheets running

I must admit, my immediate reaction when I first saw the title of this thread was "What do you mean, a spreadsheet.."

Lois_and_CK

7% ... I'd never retire! I like ex-pat's more optimistic 2% :-D

ex-pat_scot

Lois_and_CK wrote: »

7% ... I'd never retire! I like ex-pat's more optimistic 2% :-D

Frankly I'm not overly concerned about the absolute level of inflation; rather the gap between investment return and inflation. That's what delivers real returns.

So in principle I am indifferent between inflation at 2% with investment return 7%, and 4% with investment return at 9%.

My critical long term modelling assumptions are that:

- wage inflation 1% (I am rather overpaid for my role, and future pay rises are unlikely)
- real investment returns of 4-5% (ie net of inflation).
- 1% increase in Annual allowance and lifetime limit

These are the same for accumulation and decumulation phases.

My expectation is that:
- 100% equity returns will be around 5%
- 50:50 equity:bond returns will be nearer 4%
- Safe Withdrawal rate is roughly 4.25% for ER at 55 and SP (+spouse SP) at 68
- 2 x SP gives an element of income certainty if my SWR plans are knocked by serious market timing challenges in early drawdown.

My spreadsheet has multiple tabs (perhaps that counts as multiple spreadsheets!)

I am in accumulation phase at the moment, so it is focused on growth.
I model in today's money terms.
i show contributions each month.
I have annualised investment return models showing expected (4% real) growth and scenarios with different returns - this gives total pot value and then the translation of that into a monthly gross and net income figure under a 4% withdrawal rate (ie on 1.1.2021 pot value =£500,000, which would provide a monthly gross and net of £2,000 /£1,700) (numbers are purely representative).
It also allows me to play around with contribution levels, to see how that varies the outcome.

That then allows me to see at what point my predicted pot will reach a certain value ("the Number"), and how that date will move around depending on changes in investment return.

It also allows me to see where I am - whether I am ahead or behind the predicted performance, based on current actual pot value. [I am approx 7 months ahead of prediction at the moment, after Brexit vote boosted my overseas-weighted equities spectacularly]

brewerdave

The main drivers for my pessimistic personal 7% cost of living projection are my predictions for utility price increases (8% pa), petrol(10% pa), car/medical /dental insurance costs (10% pa), council tax(5% pa -in Wales!) and communication costs (8%). No longer have debts/mortgage to service so my cost base is relatively low but might grow frighteningly quickly.
At the same time a significant chunk of my DB pension is GMP -so no growth in pre 88 bit and for the last 2 years nothing on post 88 GMP.:(

Triumph13

ex-pat_scot wrote: »

My critical long term modelling assumptions are that:

- wage inflation 1% (I am rather overpaid for my role, and future pay rises are unlikely)
- real investment returns of 4-5% (ie net of inflation).
- 1% increase in Annual allowance and lifetime limit

These are the same for accumulation and decumulation phases.

Personally I use slightly more optimistic investment returns for the accumulation phase than for the decumulation. This is for the simple reason that if the former turns out to be over-optimistic I just have to work another year or two. If the latter is over-optimistic I'm stuffed!
I use 4% net of inflation and charges in accumulation and am planning a 3.6% fixed-%-of-the-pot withdrawal strategy. Almost wholly equities in both phases as happy to accept fluctuating income thanks to a reasonable DB / SP underpin.

LHW99

Problem is if you go back to the 1970's / 1980's, inflation was truly horrendous
http://www.whatsthecost.com/historic.cpi.aspx
A few years of inflation over 10% can do a lot of damage, and I doubt the triple lock would stay in place if the government had any clue that those sort of rates were in the offing.

ex-pat_scot_2

Triumph13 wrote: »

Personally I use slightly more optimistic investment returns for the accumulation phase than for the decumulation. This is for the simple reason that if the former turns out to be over-optimistic I just have to work another year or two. If the latter is over-optimistic I'm stuffed!
I use 4% net of inflation and charges in accumulation and am planning a 3.6% fixed-%-of-the-pot withdrawal strategy. Almost wholly equities in both phases as happy to accept fluctuating income thanks to a reasonable DB / SP underpin.

Agreed - i'm at least 7.5 years away from the decumulation phase, so I'm not yet firm on my strategy.
As I noted earlier, my modelling shows how long I will need to work until I reach "The Number", and how it varies depending on actual rate of return.


We have the underpin of 2*SP, plus maybe £5,000 DB kicking in at some point, so like you I can be a little cavalier in my equity percentage.
I do think that we can be rather flexible in drawdown - I suspect that I will be looking to generate a decent high yield portfolio (depending on changes to tax and divi approaches), and don't necessarily view the decision to "retire" as irrevocable if early inv rtns /market conditions prove dire. Plan B would be part time consultancy to keep finances level, with some reduction in spend and potential downsize if things got really sticky. As you note, taking a % of pot, rather than "% of initial pot", flexes to actual conditions.


Who knows? Certainly things won't pan out as I expect, but I do plan to roll with whatever comes and make the best of it.

chiram2015

Triumph13 wrote: »

Personally I use slightly more optimistic investment returns for the accumulation phase than for the decumulation. This is for the simple reason that if the former turns out to be over-optimistic I just have to work another year or two. If the latter is over-optimistic I'm stuffed!
I use 4% net of inflation and charges in accumulation and am planning a 3.6% fixed-%-of-the-pot withdrawal strategy. Almost wholly equities in both phases as happy to accept fluctuating income thanks to a reasonable DB / SP underpin.

Hi Triumph

What do you mean by the accumulation and decumulation phases - is this the date before and after you stop working?

Thanks

Chris

jamesd

Accumulation is when you're in your main working life and paying money in to increase the value of the pot.

Decumulation is when you're taking money out, even if you're still paying money in and still working.

Your risk profile changes between the two phases. During pure accumulation in the start and core of your working life you have other income so the consequences of trouble can be things like later than desired retirement.

During decumulation you may have limited new or alternative work prospects so limited potential to replace losses out of income. Even if your investment risk tolerance remains the same, your capacity for loss has probably reduced, since you can't replace the loss so easily.

This sort of thing is why variation of investment returns and sequence of returns risk are so important to those considering early retirement. Fail to take those into account and you can easily end up taking more income than is really sustainable. It's not as simple as just saying 5% investment return expected so I can take out 5%. Sequence of return issues would mean that say a 7-8%plus inflation return would be cut to around 406% income level, depending on drawdown strategy used and planning horizon (planned potential life expectancy including sufficient safety margin beyond average).

Part of the reason why I set up the Drawdown: safe withdrawal rates topic was to give people an introduction to a range of planning issues that shouldn't be ignored.

Stubod

Hi, I am now in major spreadsheet mode as "the final day" approaches. I recently posted a question asking if there is a link between inflation and interest. Over the years there is no real pattern. Sometimes inflation higher than interest rates, sometime lower.

I use a spreadsheet that mainly uses inflation as the key driver, but then has interest rates and state pension rates as a percentage of that. ie worst case is based on Inflation at 5% per annum, savings and investment interest rates as 50% of this (ie 2.5%), and state pension interest rates as 80% of this...OK its all guesswork but at least it gives a "worst case (hopefully), indication.

Although I am now also looking at "firecalc" as has been previously recommended and is probably a bit more scientific!!