Investment return and drawdown assumptions financial modelling tool

Pat38493

There is also a site cfiresim that lets you measure a plan against historical data of inflation and growth.  Unfortunately in the real world, the returns are not constant but volatile.  Depending on the sequence of those returns (in other words, the date you retire and luck) you could get massively different end results.  If you get several negative years early in your retirement your pot will get depleted quicker, no matter what the long term average will be. 

As such, if you are using a spreadsheet it’s advisable to use pretty conservative growth assumptions in order to approximate the worst case.  I use inflation + 2% when doing flat rate modelling.

p.s. Don’t forget to include state pension in your modelling and check your state pension entitlements on gov.uk web site. 

SouthCoastBoy

Pat38493 said:

There is also a site cfiresim that lets you measure a plan against historical data of inflation and growth.  Unfortunately in the real world, the returns are not constant but volatile.  Depending on the sequence of those returns (in other words, the date you retire and luck) you could get massively different end results.  If you get several negative years early in your retirement your pot will get depleted quicker, no matter what the long term average will be. 

As such, if you are using a spreadsheet it’s advisable to use pretty conservative growth assumptions in order to approximate the worst case.  I use inflation + 2% when doing flat rate modelling.

p.s. Don’t forget to include state pension in your modelling and check your state pension entitlements on gov.uk web site. 

If you have a suitable cash buffer you can eliminate some of the sequence of returns risk as during fallow return years cash can be used rather than selling equities

OldScientist

SarahB16 said:

I was wondering if anybody could kindly direct me to a modelling tool that would enable me to enter various assumptions please so that I could see how long my investments and savings would last please from drawing a certain amount per year.  I presume it would be something that somebody may use re their DC pot.

By way of background I am in the very fortunate position that in retirement I will have two separate DB pensions and would like to retire when I can afford to do so.  I make LGPS AVCs so may have my AVC pot, S&S ISA, premium bonds and some savings to supplement my pension and I would like to run some scenarios to see what I could drawdown from these. 

For example, say the various investments are worth £200k when I’m 58 I would like to see how much per annum I could draw from that total savings and investment pot so it reached nil when I’m say 90.

Knowing that amount would enable me to weigh up the impact of the actuarial reduction on my DB pension schemes and to decide when I may be able to afford to retire.

I’m 51 and have many unknowns from now to when I’m 58 and had always thought I may be able to retire aged 61 or 62 but if my savings and investments are high enough this may enable me to retire earlier but I can’t find a good modelling tool then I can input my assumptions into for the return that would be generated from the savings and investment portfolio.  Keeping cash aside (c.£30k for capital expenditure and permanently topped up).

I would like to enter: I have £200k in savings and investments and across these investments they are forecast to increase by 3% per annum above inflation per year.  If in year 1 £8,000 per annum was drawn (and increased by inflation each year) this would reach nil after 30 years, etc.

Alternatively: I have £200k in savings how much can be drawn per annum so that it reaches nil after 30 years assuming an annual return of x%.  Can the models assume that the drawdowns increase each year by y% to account for inflation?

I should add I’m not too bothered if the model can’t account for inflation assumptions but I would be really interested in looking at the financial forecast modelling tools that are available please.  I’m sure there must be free ones available that people use for drawing down their DC pot but with me it will be for my AVC pot (so lower returns as initially would be invested in savings accounts/fixed rate bonds I presume) that I will only be able to drip feed into my S&S ISA and for my existing S&S ISA and the savings I have.  I will have to assume an average overall level of growth for these savings and investments.

I may be overcomplicating this as I recognise I have not really looked into this previously with not having a significant DC pot. 

Thank you in advance for your help and of course also happy to use a very basic financial modelling tool that lets me to enter the various scenarios.

One rule of thumb (for the UK) is that historically, an initial withdrawal of about 3.0-3.5% of your portfolio, subsequently adjusted for inflation didn't run out of money before 30 years. This would imply about £6k-£7k per year on the basis of £200k in your initial pot. A calculator that uses historical UK asset returns and inflation can be found at https://www.2020financial.co.uk/pension-drawdown-calculator/ (ps adding cash to the portfolio in the simulator will improve performance since long UK bonds have at times had awful returns - a 50/50 split between cash and bonds will very roughly be equivalent to an 'all stocks' bond fund).

You mention that you have DB pensions, and of course, eventually, the state pension. It might also be useful for you to a) determine how much you will need to spend in retirement (categorising into 'core' and 'adaptive' can be useful) and b) map out the total amount of income you would have from these guaranteed sources with time (this is easier if the DB pensions are fully index-linked, but can be approximated otherwise). You can then break any shortfalls down into smaller sections. For example, if you retired at 62, you have 5 years before your state pension and might want to spend some of your portfolio making up the shortfall over those 5 years (at today's values, assuming a real return on cash and fixed rate accounts of 0%, that would cost 5*11.5k~58k).

In the longer term, if the income from your state pension and DB pensions is enough to satisfy at least your core expenditure, then withdrawals from your portfolio can be taken as a percentage of the current portfolio value since this allows withdrawals for life although income is then variable depending on market conditions.

SarahB16

Pat38493 said:

There is also a site cfiresim that lets you measure a plan against historical data of inflation and growth.  Unfortunately in the real world, the returns are not constant but volatile.  Depending on the sequence of those returns (in other words, the date you retire and luck) you could get massively different end results.  If you get several negative years early in your retirement your pot will get depleted quicker, no matter what the long term average will be. 

As such, if you are using a spreadsheet it’s advisable to use pretty conservative growth assumptions in order to approximate the worst case.  I use inflation + 2% when doing flat rate modelling.

p.s. Don’t forget to include state pension in your modelling and check your state pension entitlements on gov.uk web site. 

Thank you for the helpful reminder and yes I have included the full state pension in my financial model. I regularly check and only require two more years to ensure I receive the full state pension.  

In case anybody has looked at the financial model that Peterrr kindly shared (the link to the tutorial on YouTube) for someone like me who will have a mix of guaranteed income (2 x DB pension plus my state pension) plus supplemented with my savings and investments the financial model was truly excellent. I have created the model in Excel so I have my own version with some draft forecasts and I know I will find it invaluable.

However, I will also refer to the other financial modelling tools that have been kindly shared too.   

pterri

Yep, the guide tool is very good

michaels

SouthCoastBoy said:

Pat38493 said:

There is also a site cfiresim that lets you measure a plan against historical data of inflation and growth.  Unfortunately in the real world, the returns are not constant but volatile.  Depending on the sequence of those returns (in other words, the date you retire and luck) you could get massively different end results.  If you get several negative years early in your retirement your pot will get depleted quicker, no matter what the long term average will be. 

As such, if you are using a spreadsheet it’s advisable to use pretty conservative growth assumptions in order to approximate the worst case.  I use inflation + 2% when doing flat rate modelling.

p.s. Don’t forget to include state pension in your modelling and check your state pension entitlements on gov.uk web site. 

If you have a suitable cash buffer you can eliminate some of the sequence of returns risk as during fallow return years cash can be used rather than selling equities

Generally the size of the buffer needed means that historically this strategy gives a lower safe withdrawal rate than having a mix of stocks and bonds.  People who suggest this mostly do not understand the maths.

Notfarfromtheborder

michaels said:

SouthCoastBoy said:

Pat38493 said:

There is also a site cfiresim that lets you measure a plan against historical data of inflation and growth.  Unfortunately in the real world, the returns are not constant but volatile.  Depending on the sequence of those returns (in other words, the date you retire and luck) you could get massively different end results.  If you get several negative years early in your retirement your pot will get depleted quicker, no matter what the long term average will be. 

As such, if you are using a spreadsheet it’s advisable to use pretty conservative growth assumptions in order to approximate the worst case.  I use inflation + 2% when doing flat rate modelling.

p.s. Don’t forget to include state pension in your modelling and check your state pension entitlements on gov.uk web site. 

If you have a suitable cash buffer you can eliminate some of the sequence of returns risk as during fallow return years cash can be used rather than selling equities

Generally the size of the buffer needed means that historically this strategy gives a lower safe withdrawal rate than having a mix of stocks and bonds.  People who suggest this mostly do not understand the maths.

Maybe people who suggest this have enough saved to be able to hold a large enough cash buffer which make the maths irrelevant. If you don't need to chase returns and can accept a lower safe withdrawal rate for peace of mind then why do so?

michaels

Notfarfromtheborder said:

michaels said:

SouthCoastBoy said:

Pat38493 said:

There is also a site cfiresim that lets you measure a plan against historical data of inflation and growth.  Unfortunately in the real world, the returns are not constant but volatile.  Depending on the sequence of those returns (in other words, the date you retire and luck) you could get massively different end results.  If you get several negative years early in your retirement your pot will get depleted quicker, no matter what the long term average will be. 

As such, if you are using a spreadsheet it’s advisable to use pretty conservative growth assumptions in order to approximate the worst case.  I use inflation + 2% when doing flat rate modelling.

p.s. Don’t forget to include state pension in your modelling and check your state pension entitlements on gov.uk web site. 

If you have a suitable cash buffer you can eliminate some of the sequence of returns risk as during fallow return years cash can be used rather than selling equities

Generally the size of the buffer needed means that historically this strategy gives a lower safe withdrawal rate than having a mix of stocks and bonds.  People who suggest this mostly do not understand the maths.

Maybe people who suggest this have enough saved to be able to hold a large enough cash buffer which make the maths irrelevant. If you don't need to chase returns and can accept a lower safe withdrawal rate for peace of mind then why do so?

In that case you can almost certainly afford an annuity.

MK62

michaels said:

SouthCoastBoy said:

Pat38493 said:

There is also a site cfiresim that lets you measure a plan against historical data of inflation and growth.  Unfortunately in the real world, the returns are not constant but volatile.  Depending on the sequence of those returns (in other words, the date you retire and luck) you could get massively different end results.  If you get several negative years early in your retirement your pot will get depleted quicker, no matter what the long term average will be. 

As such, if you are using a spreadsheet it’s advisable to use pretty conservative growth assumptions in order to approximate the worst case.  I use inflation + 2% when doing flat rate modelling.

p.s. Don’t forget to include state pension in your modelling and check your state pension entitlements on gov.uk web site. 

If you have a suitable cash buffer you can eliminate some of the sequence of returns risk as during fallow return years cash can be used rather than selling equities

Generally the size of the buffer needed means that historically this strategy gives a lower safe withdrawal rate than having a mix of stocks and bonds.  People who suggest this mostly do not understand the maths.

...or perhaps they fully understand the maths, but are just not assuming that the early years of their retirement will necessarily follow the historical average.....

sgx2000

Peterrr said:

If you have Microsoft Excel, this is a step by step tutorial video on how to build a retirement cash flow model: https://www.youtube.com/watch?v=7Wkr5QtY-G8
You can then vary the inputs, drawdown amounts, inflation and investment returns to your hearts content

Made my own spreadsheets....
Then found this youtube video....
I asked, and he has made the final spreadsheet available to everyone ....just follow the link..

It is a great video tutorial, and a great starting point for you own custom, modified, spreadsheet.....

I have, so far, have added the following to my spreadsheet
State pension
Db pension
Tax due
Net income