My retirement spreadsheet

Dh6

Hi everyone. If possible could you please comment on my retirement spreadsheet. I’d like to have over 300k in my SIPP at 60 in order to retire or think about retiring.

Currently I’m 100% invested in a global equities tracker. I’ve used a growth figure of 4% on my return on investment. I’ve also factored in a 2% rise in my contributions annually to keep up with inflation. 

I’ve also included 2 pretty hefty drops in value of my pension funds to try and allow for any market crashes that will occur over my investment period.

Im also paying into a cash LISA as my no risk asset which will be worth in the region of £115k at 60 years old.

My wife is also paying into a cash LISA which will be worth around the same at mine and has an estimated pension value of 180k at 60. 

Were looking to have around 30k net between us each year until we reach 70 and then dropping to around 20k between us from 70 onwards when we will be drawing full state pension.

I understand it’s not an exact
science but are my figures realistic? 

Kind regards DH

Dh6

www

zagfles

It really is pure guesswork. You can look at historical averages but I'm not sure they help that much. The future won't necessarily be similar to the past, eg the past decade has been different in lots of ways to previous decades.

You also need to account for cash investments potentially losing value, since now and for recent years it's been hard to get interest rates that even keep up with inflation. If you're only getting 1.5% interest and inflation is 2.5% you're losing about 1% a year.

Your assumptions are less optimistic than some I've seen, but I think the key is to have a "what if" plan, so if your investments did badly what would you do - live on less, retire later etc rather than have a fixed plan. Don't believe the myth that the stockmarket always goes up over the long term - the Japanese stock market is under half its value of 30 years ago, the UK stockmarket was lower in 1950 than in 1900.

But personally I don't see financial risk as a big deal, I'm more worried about other risks eg health risks. If I can live with 1 chance in 2 of getting cancer I can certainly live with the risk of not having quite the pension I was expecting. Just like you might eat your 5 a day to reduce your cancer risk, invest sensibly and you reduce your financial risk, but you don't eliminate it. 

Sorry that turned more into a philosophical answer than a practical one...

Linton

The spreadsheet looks fine as a very simple long term model.  The one criticism I would make is your treatment of inflation.  You have two choices:

1) Work completely in current value.  In this case the % investment return is that above inflation and the model would have the inbuilt assumption that contributions increase with inflation so you would not add extra withibn the spreadsheet.  This has the benefit that you can think in terms of current prices when calculating when you would have enough money to retire.

If you were taking this approach I think that assuming 4% return above inflation would be a bit ambitious.  When planning it is better to be pessimistic  with a chance of being happily surprised than the reverse.

2) Work completely in £ terms.  You would therefore need to explicitly include inflation in all your calculations, incl;uding income requirements in retirement.

Many, perhaps most, people use approach (1) as it's simpler for one-off calculations.  I use approach (2) as it is more flexible, a major benefit if you are including fixed rate incomes and reserves of cash in your calculations.

Notepad_Phil

Don't forget the £30k per year is going to be worth a lot less than that in real money by the time you get to 60 and have to take into account inflation. You could add a column to your spreadsheet that starts at 1 and increases by 2% each year to divide into your values to see how much they are in worth in current day values.

Edit - beaten by Linton to post on the point of inflation. I too use the inflation method that Linton uses as it helps to account for what happens to cash and how it loses money over time.

Stubod

..as per above. I have been using a spreadsheet to record actual income and expenditure and also "predicted" expenditure and pensions etc. I include an "inflation" factor which I can adjust to reflect various scenarios. I work on a (hopefully) pessimistic ratio of inflation at 3.5% (for example), and any "interest/investments" as a ratio of this which I always assume is lower than inflation, eg a 50% ration would give 1.75% for savings and investments...

Linton

zagfles said:

It really is pure guesswork. You can look at historical averages but I'm not sure they help that much. The future won't necessarily be similar to the past, eg the past decade has been different in lots of ways to previous decades.

You also need to account for cash investments potentially losing value, since now and for recent years it's been hard to get interest rates that even keep up with inflation. If you're only getting 1.5% interest and inflation is 2.5% you're losing about 1% a year.

Your assumptions are less optimistic than some I've seen, but I think the key is to have a "what if" plan, so if your investments did badly what would you do - live on less, retire later etc rather than have a fixed plan. Don't believe the myth that the stockmarket always goes up over the long term - the Japanese stock market is under half its value of 30 years ago, the UK stockmarket was lower in 1950 than in 1900.

But personally I don't see financial risk as a big deal, I'm more worried about other risks eg health risks. If I can live with 1 chance in 2 of getting cancer I can certainly live with the risk of not having quite the pension I was expecting. Just like you might eat your 5 a day to reduce your cancer risk, invest sensibly and you reduce your financial risk, but you don't eliminate it. 

Sorry that turned more into a philosophical answer than a practical one...

To answer some of the philosophical points...

Yes, or course a plan for 30 years hence is pretty much guesswork though it does have the value that the plan is consistent and based on explicit assumptions.  However as a one-off its value is limited.

The real benefit comes with maintaining the plan over time, updating it as circumstances change.  You are then in a position to see well in advance whether you are getting closer to achieving your objectives or need to take some limited action now to reduce the need for drastic action later.

Like Zagfiles I dont see financial risk as a big deal.  However this is not because I see other risks as even greater, but rather because my plans  are based on very pessimistic assumptions, have a great deal of slack, and have served me well for the past 20 years.

Dh6

Many thanks for the above comments. I take on board what you say and will post my spreadsheet with inflation adjusted returns ( @ 2% ) . I’m a relatively new member of this forum and an investment beginner so I’m trying to follow hints and tips on this forum which are helping me understand my financial position now and hopefully in the future. 

My spreadsheet is very basic as it’s the first one I’ve done since I was in secondary school about 20 years ago. Another thing to point out is that my annual contributions are spread out @ £750 pm which I don’t know how to calculate! 

Dh6

Www

Linton

Dh6 said:

......

My spreadsheet is very basic as it’s the first one I’ve done since I was in secondary school about 20 years ago. Another thing to point out is that my annual contributions are spread out @ £750 pm which I don’t know how to calculate! 

The difference will be pretty small compared with the degree of uncertainty in the assumptions so its not worth worrying about.

If it does worry you, you can be pessimistic with contribution+return X last years total, or optimistic with (contribution+lastyears total) X return, or you could take the average.