Retirement Planning Model

Xl_whizz

After 40+ years of working within British Manufacturing I started to transition towards retirement a couple of years ago, now working 2-3 days a week, whilst starting to draw on my pension pot.

Like many, I found myself on a steep learning curve as I crunched the numbers to see what was, and wasn’t, possible with my various pension pots, learning in the process the pros and cons of drawdown, annuities, etc.

Whilst a financial (and pensions) novice, I’m reasonably adept at building spreadsheet models/macros and over the past couple of years I’ve developed a retirement planning tool that I frequently go back to in order to (re)validate my assumptions. Its not perfect, but (I hope and believe) its good enough.

The model I have developed allows me to plug in current and future income sources, including the state pension when I turn 67. Current savings and pension pots are factored in, together with growth assumptions for both.

My wife and I have what I’d describe as a more than comfortable standard of living which we know we could cut back on should the need arise, and/or as we get older. We also have a good idea as to the level of monthly income above which we can usually save. This is important in determining future income expectations after retirement. The model allows me to build these expectations, whilst also taking into account inflation. For example, a net income today of £2500/month would need to be £3620 in 15 years time assuming a 2.5% annual inflation rate over this period.

As with all budgets the model allows me to see where I need to be each year, for as many years as I choose to project forward. My assumptions are conservative, hence there should be the opportunity to keep on the right side of the budget – for example, I’ve factored in 3% growth on savings but all my savings are currently invested at better rates.

The modelling can be tailored to reflect an individuals choice when it comes to risk, ranging from ‘safer’ assumptions that annuities provide to adopting higher risk investment strategies which could see greater erosion of pension pots if markets collapse.

The example attached, for illustration purposes, is based on a 60 year old with a household income of £50K PA who is eligible for state pension at 67 and who has a current pension pot of £400K and savings of £15K. The model assumes they retire at 64 and that the pension pot will grow by 2% PA up until that point (plus future contributions). A net income (ie after tax/NI etc) of £2800 per month is possible scaling down to £2300 per month when they reach 80, whilst still leaving £18K in savings.

Clearly the choice of what age range to model is down to individual choice and may depend on health or other factors such as the potential of future equity release (through sale of property) in later life to supplement and lengthen Pension income. In the example above, retiring before 64 would mean that funds would run out sooner, or that the budgeted monthly income would need to reduce.

I’m keen to get some feedback on the model from those with greater financial nouse in order that I can further develop the model to make it as realistic as possible. I’m happy to model/share some scenarios via the forum to further test out the model, whilst adding the caveat that I’m not here to provide financial advice. My skills are in Excel, not pensions!!

Bostonerimus1

It's a great exercise to come up with a spreadsheet that models spending along with constant growth and inflation with various income streams coming on line, and doing that is probably far more than most people do so you are already ahead of the curve. You might want to include variable rates of growth and inflation by looking at the statistical distribution of things like growth and inflation and choosing from that distribution. You could stress test by adjusting the mean of say a Gaussian distribution with a certain standard deviation or just run your simulation multiple times in a sort of Monte-Carlo approach. However, there are plenty of online resources that use actual data for a Monte Carlo approach so I think those along with your current spreadsheet will work fine.

Linton

I found this type of modelling to be an essential part of retirement planning both before after stopping work. Until recently I updated my spreadsheet with reality every year. It was rather more complex dealing with his & hers taxation including SP, taxable pension drawdown, non taxable income from S&S ISAs, major one off expenditures.


Some comments...
1) Are the growth and inflation figures the ones you are actually using for your modelling?  Assuming that they are:
a) Are you assuming a 2% real or £ based return on your pension pot? 2% in £ terms seems unrealistically low.
b) Why does it stop growing when you retire?  Ongoing growth from a pension pot is surely the most important source of investment income. In particular you need a reasmable return on your pension pot to deal with inflation. I assumed 4% in £ terms
c) In my view 2.5% inflation is rather too optimistic.  I used 3%.
d) I assume you stop at age 80 because of the width of the screen.  It would make sense to go up to age 95 or more, well above average life expectancy.

2) You may find it useful for your results to be given both in current real value terms and in £ terms.

3)  It is helpful to have the key factors separately set for each year possibly defaulting to standard values rather than including them in the calculations.

This is only part of the picture.  Assuming your pension pot does benefit from investment returns then you must accept that they can be highly variable.  Your retirement plan should be able to cope with a number of years of zero or negative growth.

Xl_whizz

Thanks Linton for the feedback - really helpful. The modelling is just illustrative and not my own scenario. I can extend beyond the age of 80 - again, just capped it there for the purpose of the example.

af1963

Looks pretty well organised and clear.

The main potential issue that isn't addressed is the variability of investment performance. It can make a big difference if there are large falls in the pension value early in the retirement period - "sequence of returns risk" - and a model that assumes a constant level of return, even a fairly conservative one, won't capture that risk.

I use(d) a spreadsheet very like yours, but also looked at online calculators based on historical data, which show a range of likely outcomes which include historically good and bad years for starting retirement. Mostly US based but can be used in £ as well as $. A few include ... 

Firecalc: https://www.firecalc.com/
FICalc:  https://ficalc.app/
and Rich Broke or Dead https://engaging-data.com/will-money-last-retire-early/  which helps concentrate the mind on spending it before falling into the "dead" zone.

Or you could just be ultra-cautious and assume the pension pot will halve in a stock market crash the day after retirement ... 

OldScientist

As a sense check, a quick 'back of the envelope' calculation can be useful.

Assumptions
1) All assets grow at 0% real (historically not unrealistic over 20 to 30 year periods for a mixed portfolio)
2) A gross income requirement of £36k and a final pension pot (inc further contributions) of £460k.
3) All calculations are inflation adjusted and in today's money

The example in your spreadsheet can be calculated by finding out how much money is needed to support from 64 to 67 and then from 67 onwards.

There are roughly 3 years where the assets will have to support all the required income, so 3*36=£108k

And potentially 30 years (i.e., to 97yo) of 30*(36k-11k)=£750k

In other words, £858k is needed so there aren't enough resources to support that amount of income for a long life (the pot will run out about 14 years after SP age, i.e., around 81yo - similar to what the example spreadsheet indicates).

I also note that if the spreadsheet is in nominal figures, then the SP should be uprated for assumed inflation between 60 and 67 (assuming a full state pension is expected in that year).

Moonwolf

My spreadsheet looks very similar except I only project forward from the point of retirement.  I have simplified it this year as I am retiring at the end of March at 59, but for the last 10 years I have had a tab for retire at 57,58,59 and so on to 65.  I have uplifted these each year with the new figures for my DB pensions, state pension and the size of my DB pots.  Obviously now I have deleted all tabs except retire at 59.

Apart from bringing the figures up to date, I ignore inflation entirely.  I just assume my DB pensions and state pension will rise roughly in line with my personal inflation rate.   I think predicting inflation is no more accurate than this, there will be years with 0% inflation and years with 5% or even 10% inflation during my retirement  but I have no idea which they will be so I might as well be wrong in simple terms.

I do allow for for investments to grow at an average rate above inflation for my retirement, I read from a cell so I can change this figure, I use 2.5% over inflation as a default but put in anything between 1% and 3.5% to see how different it looks.  As I have a lot of DB pensions I can risk having most of my DC pension in equities so 2.5% is probably conservative.

(I also plan to carry on paying £240 a month into my pension after I retire to take advantage of the tax, so grow my DC pot by £3600 after I have taken money out and applied growth, until I reach 75)

I do the reverse with cash and multiply what is left in savings by .98 each year, so assume that even with interest it shrinks on average with respect to inflation.

As @af1963 says you also need to think about sequence of returns, what would happen if there was a crash just before you retire. I use https://www.guiide.co.uk/ to do this, it tells you how your plan would fair given different previous stock market scenarios hit at point of retirement (monte carlo).  That now tells me I might fail in 1% of scenarios and run out of money too early, but that assumes I don't change my spending in those scenarios.

cfw1994

Interesting.
My spreadsheet has the axes the other way round….years as you scroll down (indeed, I am now using months!), with income streams along the top. 

First chunk are pensions or passive (eg, we still get solar FIT quarterly).  DB, state pension - those fixed ones.

Next is the all-important sum we would like each month 👀
After that we have possible sources to draw from: DC pots, ISA, “cash” savings.

I used to do this with a row per year, but as we started “shuffling” where we might draw from, and also recognising things generally start in a particular month, I moved to monthly.  

Early days - only started on the monthly format last Sept - but it feels like it is working well 👍

For past months, the numbers will be actual, as I put them in.  They nicely illustrate how things have gone (which might also indicate when things might be heading for a fall….)

For the future, numbers will increase by what I deem to be a broad expectation.   Eg, income to rise 5% pa, DC pot to rise 4%, cash funds 1%.  I try to be deliberately negative with expectations - in reality, I would broadly expect my DC pot to perhaps rise 7% on average, but I realise things could drop 30% (or more!).  That is the part to play with - drop the DC pot 40% next month to see whether we will still be solvent in 20 years 🤪

Biggest challenge now is what day of the month to document 🧐  Our income sources kick in at different times in the month: maybe I need a row per day 🫣🤣

optoutDB

Most models I see online are horribly non-reactive. Eg they think they are doing clever analysis by using historic market data, but then they have a non-reactive retiree who continues spending at the same rate after a 30% stock market loss. Surprise, surprise they run out of money.  I really don't think this level of analysis is necessary for my situation, but if I did it would have an algorithmic discretionary spend based on current position (wealth, years to go)

My model is based around varying all the big variables. Which for me are: when do I draw my DB pension, how much will I earn over the years 55 to 67 ( 0 to £80k pa), how much I will inherit (0 to £200k), how much will TSLA shares go up in 5 years( 100% to 1000%) , what will bitcoin do over 10 years (-50% to +200%).

The purpose of my spreadsheet was originally to see if I needed to start earning for the next 10 years, but it looks like I'm OK if I don't, so I can continue with the lifestyle/hobby business pursuits.