Pension drawdown forecast spreadsheet

Justso65

Hi

I've been playing around with a spreadsheet to do some projections on my pension drawdown.  It's pretty simplistic at the moment and I'd be interested to hear any one's opinions on the outcome. 

What I do is take my DC pension pot and draw an income from it.  Then after 1 year I apply an increment to the remainder to simulate share growth and carry the process on.

So for example I start with a DC pot of £500K and decide I want to have £50k per year.  I've not built inflation into the withdraw nor SIPP costs.  As I said it's rather simplistic at the moment.  After 1 year I apply an increment to the remaining £450k and keep going.  Myself and my wife have DB pensions that will kick in at various ages so I add them at the appropriate age and reduce my DC pot take accordingly to keep the total pension per year at £50k.  Then when state pension kicks in a for both of us at different times I reduce that from the DC take.

By 71 my wife and I would have a DB/State pension of £51K at today's rates without taking into account the DB/State pension rises between now and then.

What jumps out at me from this spreadsheet is that at one point the DC pension stops reducing and starts increases again.  My initial increment was 5% per year but I then reduced it to a measly 2% that my SIPP provider says they would pay on cash in the SIPP.

At 2% increment then at the point where my wife and I get all our DB/State pensions the DC take has gone to zero and it starts incrementing again.  At a 5% increment each year the DC pot starts to increment a year earlier then gradually increases each year to more than I started with at age 79.  Even at 1% increment the turnaround happens around 70 and increases gradually from there.

I know this is a pretty simplistic setup but based on how it is applied and with a 2% worst case increment is this a viable outcome to expect?

As usual, thanks in advance for all your expert opinions.

JS

tacpot12

Your method is basically sound and is producing an expected result, but there are a number of points that need to be made. 

1) You have assumed that you can get 2% return on your investments, but the SIPP Provider is only offering 2% on cash because interest rate are higher than this. And interest rates are higher than that because inflation is higher. A couple of years ago when interest rates were 0.5% and inflation was 2%, the SIPP providers were paying 0.1% on cash. So expecting cash to keep pace with inflation isn't going to work. So you are probably going to have to remain invested in equities and bonds to some large degree. History has shown that equity markets beat most other options and are the best chance of keeping pace with inflation, but do not guarantee it. You need a good deal of slack in hand to be sure of success. 

2) Your method ignores the impact of sequence of return risk. Actually this form if risk is harder to factor in that the effect of inflation, but the result of this risk is that you need more slack. 

3) You don't need to worry about factoring in costs to much - it is easy to do so by reducing the expected rate of return. e.g. you think you can get a return of 3% if you  invest in equity funds, but the funds charge 0.5% pa, just deduct the fund charges from the return and use 2.5% as the rate of return. (You can do the same with any platform charges). 

4) It will give you more confidence and insight if you build in the effect of inflation and any limits on what your DC pensions will pay by way of increments. Often the scheme rules will say that annual increments are limited to CPI or 5% which ever is the lower. I built a similar spreadsheet to your own and used it to model my retirement income. 
 
5) Your DB pensions and State Pension give you a valuable safety net. They mean that you can easily consider withdrawing more than 4% of your DC pot per year. In the US, 4% is seen as the upper limit, but in the UK although our investment returns are typically a bit lower, the State Pension makes such a big difference that withdrawing more than 4% pa before your state pension and DB pensions start paying is perfectly reasonable.

FYI: I'm retired now and have my DC pot invested in a range of Investment Trusts, Exchange Traded Funds and a couple of Unit Trust Funds. I get a dividend income of around 3.9% of the portfolio's initial value and this income should increase. I also have the potential for some captial growth and can sell investments in good years to crystalise this growth. 

Justso65

tacpot12 said:

Your method is basically sound and is producing an expected result, but there are a number of points that need to be made. 

1) You have assumed that you can get 2% return on your investments, but the SIPP Provider is only offering 2% on cash because interest rate are higher than this. And interest rates are higher than that because inflation is higher. A couple of years ago when interest rates were 0.5% and inflation was 2%, the SIPP providers were paying 0.1% on cash. So expecting cash to keep pace with inflation isn't going to work. So you are probably going to have to remain invested in equities and bonds to some large degree. History has shown that equity markets beat most other options and are the best chance of keeping pace with inflation, but do not guarantee it. You need a good deal of slack in hand to be sure of success. 

2) Your method ignores the impact of sequence of return risk. Actually this form if risk is harder to factor in that the effect of inflation, but the result of this risk is that you need more slack. 

3) You don't need to worry about factoring in costs to much - it is easy to do so by reducing the expected rate of return. e.g. you think you can get a return of 3% if you  invest in equity funds, but the funds charge 0.5% pa, just deduct the fund charges from the return and use 2.5% as the rate of return. (You can do the same with any platform charges). 

4) It will give you more confidence and insight if you build in the effect of inflation and any limits on what your DC pensions will pay by way of increments. Often the scheme rules will say that annual increments are limited to CPI or 5% which ever is the lower. I built a similar spreadsheet to your own and used it to model my retirement income. 
 
5) Your DB pensions and State Pension give you a valuable safety net. They mean that you can easily consider withdrawing more than 4% of your DC pot per year. In the US, 4% is seen as the upper limit, but in the UK although our investment returns are typically a bit lower, the State Pension makes such a big difference that withdrawing more than 4% pa before your state pension and DB pensions start paying is perfectly reasonable.

FYI: I'm retired now and have my DC pot invested in a range of Investment Trusts, Exchange Traded Funds and a couple of Unit Trust Funds. I get a dividend income of around 3.9% of the portfolio's initial value and this income should increase. I also have the potential for some captial growth and can sell investments in good years to crystalise this growth. 

Thank you, a very comprehensive reply and very much appreciated.

The spreadsheet is just a starter and was really meant to give me an alternative view of my potential DC pot lifetime rather than my very basic method of divide the pot by the number of years I need it to last until DB and state pensions kick in.  It is difficult to build in all the variables with very limited spreadsheet experience but I just wanted to build in some kind of increment to see the effect.  Regarding cash interest, as a net saver, I’m only too aware of the paltry interest rates for the last while.  Great isn’t it? A financial crisis caused by overly easy credit and the people who suffer are those that didn’t cause the problem.  Let’s hope interest rates can stay at a level where savers can get some kind of return and borrowers aren’t punished too much.

I’ll play around with the spreadsheet a bit more but it has given me a bit more confidence in the potential lifetime of my DC pot.  As mentioned in other discussions I just need that bit more confidence before pulling the trigger and retiring.  After the day I’ve had today it feel that cannot come soon enough.

JS

Scrudgy

There is a good example of a retirement planning spreadsheet by Lars Kroijer where he starts with the basics and you can follow and pause as required to build the spreadsheet.

if you follow it through, it’s also builds in a random number generator to simulate many different outcomes with the click of the mouse. This you can play with it to your hearts content to look at various good and bad years at the start of your drawdown to see what impact the sequence risk can be. Purely theoretical, but useful for seeing what could happen. Link to the start below.

https://www.youtube.com/watch?v=1LUIQa5hgMg

Justso65

Scrudgy said:

There is a good example of a retirement planning spreadsheet by Lars Kroijer where he starts with the basics and you can follow and pause as required to build the spreadsheet.

if you follow it through, it’s also builds in a random number generator to simulate many different outcomes with the click of the mouse. This you can play with it to your hearts content to look at various good and bad years at the start of your drawdown to see what impact the sequence risk can be. Purely theoretical, but useful for seeing what could happen. Link to the start below.

https://www.youtube.com/watch?v=1LUIQa5hgMg

Brilliant.  Lars has a new subscriber.  Just the kind of thing I was looking for.  I love these types of things where you input different starting values and the calculations update to give a long term view.

fifeken

I have something similar and at the top of each column I put in an expected growth rate.  They're currently showing 0.5% annually for Tax free allowance (from when it unfreezes), 3% increase in annual draw, 2% increase in State Pension based on today's forecast, and 5% on my pension pot (after charges).  Updating these figures lets me look at lots of possibilities.

At the start of the financial year I update all columns to actuals and change my draw based on the new figures.

Mine also monitors taxable and tax free balances as I aim to use up most of the tax free money by the time State Pension kicks in, which will allow the State Pension to compensate for the extra tax.

tacpot12

You might also have a look at cFIREsim

It uses more sophisticated modeling than you would want to write yourself in a spreadsheet, and it has a database of the historic performances of the US stockmarket that can (with care) be used as a proxy for the assets that can be bought in the UK. 

marlot

This sounds similar to the modelling I did.

If it helps, I saw my biggest risks as: 

• Inflation, especially over 5% (this seems very relevant now!)
• Means testing state pension. 
• Market volatility. 
• Care home fees.

Linton

Unless I have misunderstood something…

If you have not accounted for inflation and initially assume a DC return of 5% that means a return in £ terms of perhaps 7-8% which is very ambitious.

Justso65

marlot said:

This sounds similar to the modelling I did.

If it helps, I saw my biggest risks as: 

• Inflation, especially over 5% (this seems very relevant now!)
• Means testing state pension. 
• Market volatility. 
• Care home fees.

Thanks for the input.  regarding means tested pensions.  If that were to be suggested I think there would be an outcry and that government wouldn’t last another term.  People have paid into the system for many years and planned to get a state pension.  We have certainly accounted for the guaranteed 2 state pensions.  Whilst our combined pension would be relatively good compared to other people who maybe haven’t been able to build up additional pension the state aspect is a large proportion of it.  

  

Justso65

Linton said:

Unless I have misunderstood something…

If you have not accounted for inflation and initially assume a DC return of 5% that means a return in £ terms of perhaps 7-8% which is very ambitious.

Based on the youtube video link kindly added by Scrudgy I will be adding in an inflation aspect to the spreadsheet.  I’ll add a yearly increment to my pension take and an assumed increment to state and DB pensions.  I believe my DB pension rises are capped at 5%.

I like playing around with this type of thing even though my spreadsheet skills aren’t the best. 

The main thing I am looking for in the spreadsheet isn’t that my DC pot lasts an expected remaining pension life.  Having the 2x DB/State aspects to come at a later stage means all I’m modelling is how the DC pot might perform in the 12 years until then.  If there is something left in the pot excellent but I’m not counting on it.  We also have a relatively healthy couple of ISAs to draw on if needed.  We will also have some aspect of inheritance at some point in the foreseeable future but that is not to say we account for that in any way.  I am more than happy for my parents to leave nothing having enjoyed their retirement as much as possible.  I will certainly be trying to enjoy mine without thinking of leaving much more that a house to the kids.

As a toe in the water, and to offset a tax bill, I put some money into a SIPP for the 1st time last year and it is currently looking at around 5-6% growth in that year in Jupiter Merlin fund.  I would look to move my 2x DC pots into that SIPP prior to retirement.

I suppose the next question is how do people split up their SIPP?  Are you looking at lots of different investments or a few different types with larger sums in each?  My initial thoughts were to divide the pot around the value of my expected annual take then I can crystallise one part each year rather than taking bits from several different investment types.  Maybe keep 2-3 years draw in a relatively safe investment, draw on it each year and replenish by moving from other parts.  I know this makes the DC pot forecasts much more complicated since the “safer” aspects would grow less.  However, without taking lots of different variables into account and needing a super computer to run the calculation it’s the best I can do.