Growth & inflation calculation for forecasting

Linton

Qyburn said:

SouthCoastBoy said:

If you're not the recipient of a db pension I don't see how you could make a retirement decision without a spreadsheet 

I don't see the point in calculating to three decimal places when investment growth, inflation and interest rates can only be guessed at. 

The need for a spread sheet is not to calculate to high accuracy but rather to understand the long term implications of reasonable guesses for the major parameters given current wealth and expenditure. In our case this calculation is complicated as a significant part of our total income is fixed in £ terms.

The key output from the spreadsheet is one number, how much money is left at age 95. As long as this is greater than say 10 X annual drawdown needs at current prices normal expenditure can continue. Our cash and lower risk investments are sufficient to last 10 years so year by year market fluctuations are irrelevant and there is sufficient time to take remedial action should the age 95 limit appear to be at risk. Conversely whilst the outlook is much more favourable large one-off expenditures can be made without worry.

marlot

For me, the main use of a spreadsheet was so that I could model scenarios.

• What if inflation is high for an extended period?
• What if the state pension becomes means tested?
• What if I live longer than my model predicts?
• What if there is a stock market downturn early in my retirement?
• What happens when one of us dies?  Will the other be OK?
• What if one of us needs a care home?

Qyburn

Linton said:

Qyburn said:

I don't see the point in calculating to three decimal places when investment growth, inflation and interest rates can only be guessed at. 

The need for a spread sheet is not to calculate to high accuracy but rather to understand the long term implications of reasonable guesses for the major parameters given current wealth and expenditure. In our case this calculation is complicated as a significant part of our total income is fixed in £ terms.

Cheers.  I have a model to cover the transition from living off savings plus small DB pension, to first State Pension kicking in part way through the year, to second SP kicking in near the start of next year. I done this by tax year as the main reason for detail is to confirm the most tax efficient plan. I've just done this using today's pricing, making the very wild guess that inflation and investment earnings will cancel each other out.

For long term I am not sure I could build the sort of model you describe, so we're working wholly off guesswork.  In today's figures our steady state drawdown once both SPs are paying equates to 2% of the DC pot so I am hoping we have a decent safety margin there if we can ride out the transition.

Robert_McGeddon

marlot said:

For me, the main use of a spreadsheet was so that I could model scenarios.

• What if inflation is high for an extended period?
• What if the state pension becomes means tested?
• What if I live longer than my model predicts?
• What if there is a stock market downturn early in my retirement?
• What happens when one of us dies?  Will the other be OK?
• What if one of us needs a care home?

There we have it.  "What if" is the key spreadsheet use in retirement planning.

OldMusicGuy

Qyburn said:

I don't see the point in calculating to three decimal places when investment growth, inflation and interest rates can only be guessed at. 

The whole point of something like a spreadsheet is it allows you to stress test and come up with a range of outcomes because you can only guess what might happen with those variables. You can start to see what impact changes in any of those variables have on your future income and expenditure.

Also, like Linton said, the point of the model is to ensure we have a decent enough sum of money left at 95. I want to ensure we can fund potential care home fees.

NedS

jimi_man said:

I assume inflation = growth. This may be rather pessimistic (I realise not the case at present but I’m thinking long term) but any improvement on that is a bonus. 

I have 80% DB pension so it’s less important for me as it’s only a buffer to SPA and after this  we’ll be well over 100% DB. 

Still use a spreadsheet though just to keep track of things. 

I do the same. I don't see much point trying to guess what my investment returns may look like over the next 30 years or what inflation may be over that period. The important thing to know is if my investments / income streams will keep up with inflation, or not.

Luckily for us, much of our income is from SP and gold plated final salary DBs with no caps on inflation (I have one small DB capped at 2.5% but the rest are uncapped).

I have a spreadsheet that is updated weekly that tells me what I have and what our income will be based on what I have, and I use professional tools like Timeline that do model investment growth and inflation to model our income streams and cash flows to assure me we won't run out of cash, even if taking an income far in excess of what we actually need.

Maybe the reason I don't feel the need to model growth and inflation is because our plan isn't relying on any real term growth (growth above inflation) to succeed.

OldMusicGuy

NedS said:

Luckily for us, much of our income is from SP and gold plated final salary DBs with no caps on inflation (I have one small DB capped at 2.5% but the rest are uncapped).

I don't have any DB pensions, just SP and a DC pot (which has to provide for both me and my wife who has SP and a very small DB pension). I definitely need to do detailed modelling to give me the comfort I need that our money will last. I guess am doing what a financial planner would do for their clients. 

MK62

OldMusicGuy said:

NedS said:

Luckily for us, much of our income is from SP and gold plated final salary DBs with no caps on inflation (I have one small DB capped at 2.5% but the rest are uncapped).

I don't have any DB pensions, just SP and a DC pot (which has to provide for both me and my wife who has SP and a very small DB pension). I definitely need to do detailed modelling to give me the comfort I need that our money will last. I guess am doing what a financial planner would do for their clients. 

Very similar to us tbh........I feel the need to model these things because I find it difficult to make decisions if I have no idea what real effect high inflation and/or negative returns will actually have, beyond being "not good" - using spreadsheets is the only feasible way I've found to do that.......though some of the apps/websites doing similar things can be useful too (though I find their data sources/assumptions/explanations to be a little vague at times).

SpeedSouth

My attempts have all been in a) camp.  Assumed growth % minus the inflation % is the real amount in todays prices when I get to retirement.  

This is as far as my excel skills have got me previously.  How are people modelling the "what if" scenarios on excel? I had a conversation with an IFA a while back and he had some software that was doing the same thing and looked pretty powerful for those other models you need.

Is there excel templates online for this or have you all built your own over time?

Pat38493

In the actual spreadsheets I have created so far, I just use the same approach as NedS and assume that inflation and growth is a wash.  

I also use several online tools to sense check things, and I found that the above approach is toward the lower (more pessimistic) end of the predictions from these tools, so it seems a reasonable assumption that you can at least match inflation over the long term with your investments.

Probably once I pull the trigger I will create a more advanced sheet along the lines of some of the descriptions above with columns for each month where I can put forecasts and overwrite them with actuals on a monthly basis.