Spreadsheet planning assumptions

Sunnylifeover50plan

Evening

I'm sure many of us have spreadsheets which we hope provide insight into what the future might look like.

In terms of fair planning assumptions what is good for long term;

Inflation

UK state pension increases

Investment return, appreciate this is tricky but assuming a 3 or 4 out of 7 risk rating - 20 to 40% equities and mixed bonds/ gilts (Vanguard LS20 & 40) net of fund/ platform costs.

I've allowed for SIPP costs out of expenditure.

Hopefully I've framed the question correctly if not happy for guidance.

kuratowski

The most important thing is not to pick a single value and thereafter assume it’ll be what actually occurs. You have to allow for uncertainty somewhere in your spreadsheet. Either formally adopt a low-medium-high range for each input - which will give you a range of outcomes - or at least stress test your inputs with other values to see what difference it would make.

My thoughts are below but others will have different views - which just proves what I said in the 1st paragraph.

Inflation: 2% or 2.5% for the base case. But higher inflation will hurt you so include a stress case in which inflation is higher. (In my model, inflation largely cancels out, but it does have a limited net effect due to certain choices I’ve made. You asked about it so I’m assuming it’s important to your model.)

State pension increase: assume it’s the same as inflation. Politically hard to allow it to fall far behind. I think it would be ultra conservative to assume below inflation increases, but there’s nothing to stop you.

Asset returns: Your portfolio seems to have a relatively low equities allocation. My own preference is to assume this risk allocation will barely beat inflation, i.e., I am currently modelling 0.5% above inflation. It’s so subjective, but I feel more comfortable with a conservative assumption. And then I also have my stress scenario (I believe 9/10 times it will be better than this) where I show what would happen if returns were 1% below inflation.

AlanP_2

I use 2.5% for CPI so SP increase, but have only projected that 5 years as who knows what may happen to triple lock.

Personal inflation I have at 3.5% up until age 70, 3% until 80 then 1.5%.

Investment returns after fees keep up with personal inflation.

Pessimistic assumptions but don't run out so anything better is a bonus.

Still working and contributing and have DB pensions that will cover day to day living costs so investment pots are for holidays etc.

jsinc

There's no correct answer but I use inflation = 2%, state pension increase = inflation, bonds = 3% before inflation and platform/fund fees, equities = 6.5% before inflation and platform/fund fees. Some will think that's too pessimistic, other too optimistic. And I also add some switches that adjust to worse scenarios - higher inflation, zero and negative inflation-adjusted returns.

This series of videos is a worthwhile spreadsheet exercise to go through (inc volatility, multiple scenario simulations): https://www.youtube.com/watch?v=1LUIQa5hgMg

kinger101

For a very basic spreadsheet, you can just consider returns after inflation and charges, which will keep everything at today's prices. Useful for knowing how much you might need to save to have a reasonable chance of reaching a specific target.

Mine is a little more complex, and is updated on a monthly basis.

It started with the pre-tax figure I'd need in retirement (under today's tax system at least) to achieve a very comfortable retirement. I then assume 2.5% annual inflation, and 5% returns after charges - i.e. 2.5% growth after inflation. I then plug in monthly pension contributions, CPI and pension fund values to see how my projections meeting the forecast. I.e, whether I'm ahead or behind.

For pension, I've assumed I'll get maximum SP from 68, and my deferred DB scheme from 65. I've then plugged in withdrawals from 65 assuming growth is the same. I realize by equity/bond allocations might be a bit different later on, but there's a lot I don't know between now and retirement.

You don't say your age, or whether you're planning drawdown, but 20% equities portfolio is going to put a severe constraint on growth, which might need to continue well into retirement.

Thrugelmir

Sunnylifeover50plan wrote: »

Investment return, appreciate this is tricky but assuming a 3 or 4 out of 7 risk rating - 20 to 40% equities and mixed bonds/ gilts (Vanguard LS20 & 40) net of fund/ platform costs.

In an era of low bond yields. Your equity holdings will have some heavy lifting to do to compensate.

sandsy

Mine is similar to kinger101. Spreadsheet is in real terms, using real returns after charges. I target net income and chart how different pensions, including state pension, commencing at different times could achieve the target. So if I flex the ages I access different arrangements, I can get a picture of how long my funds will last.

As I don't have a full NI record, the earlier I retire, the lower my state pension will be, unless I make voluntary contributions. For my deferred DB, I can model with and without tax free cash. Currently modelling regular UFPLS on the DC rather than drawdown as no great need for 25% TFC at outset.

The spreadsheet covers my OH too.

Sunnylifeover50plan

Thrugelmir wrote: »

In an era of low bond yields. Your equity holdings will have some heavy lifting to do to compensate.

Thanks - What do you think would be a fair assumption for average annualised growth for the Vanguard LS20 and LS40 respectively long term (35 years)?

My spreadsheet includes much of what other responders have mentioned and for fund growth I currently have 2%, which matches the assumption I have for inflation and state pension increases, which for us are some time off.

I have allowed for c. £1100 pa for SIPP fees x 2 and associated costs - this is covered in expenditure forecasts which are again indexed to the rate of inflation which I've set at 2% long term.

I can easily flex the; return rate, inflation and projected expenditure to show different scenarios.

The unknown for me, reading the responses, is what to plug in for the estimated conservative long term returns from the two vanguard funds, given each has a slightly different mix.

We are not yet invested in the Vanguard funds.

Thanks

michaels

I think a real returns model is probably more realistic than any combined inflation and returns assumption. State pension I assume matches inflation although at least for this Parliament it will out perform with triple lock.

So I assume with 60:40 I will see real terms post fees return of 2%.

Only fly in the ointment is that when looking at a retirement income number working in today's terms I am also thinking about relative income compared to today's income distribution whereas in reality (historically) average incomes have grown by about 2% pa meaning a number for example median household income might be well behind after 40 years of retirement.

Thrugelmir

30 year UK Gilts are currently yielding 1.165% as a benchmark.

Equities in the longer term will be more correlated to global GDP. Current forecasts for 2020 are in the region of 3.5%.

Personally I opt for prudence. Then anything above is a bonus / pot for a rainy day. No one can forecast events 3 months out let alone 35 years.......

Brilley

..mine is (hopefully) pessimistic. I use a main figure for inflation and link everything else to it as a percentage. I have set up a spreadsheet that I can run various scenarios by just changing the basic inflation rate.
My current "average" calculation assumes inflation at 4%, state pensions are "linked" to this at 90% the inflation rate, (ie if inflation 4% then pensions rise at 3.6%. For savings I use 25% of the inflation figure (ie if 4% inflation, savings 1%), for investments I use 50% of the inflation rate. At the end of each year I adjust the budget based on actual CPI figures over the preceding 12 months...but that's just me, and people have commented before that this is probably too pessimistic.