Spreadsheet planning assumptions

cobson

pensionpawn wrote: »

I'm knowledgeable about finance, probably more than most, though no doubt much less than those on this forum. What I find hard to accept (understand) is using a pension growth figure which is arguably only slightly more than rates achievable on significant cash deposits. My pension, not including contributions, grew 15.5% in the last 12 months. Over the last 5 years my growth (not including investments) has been around 6%. I appreciate that a single year view is unwise and a longer term view is more advisable and past performance is no etc.... However surely you peeps, much more experienced than me, must be seeing growth in your holdings higher than mine, and hence should be using less 'conservative' growth forecasts? The risk of using such low growth figures is that you push yourself longer and harder for that higher pension pot valuation at the risk of health failing partially, or totally! I have started to move around 50% of my pension(s) into a SIPP and have witnessed much better growth as a consequence and am now using 10% as a future guideline (eg., Fundsmith / Lindsell Train Global 19% pa average). Perhaps I'm wildly wrong, however I'm not convinced by growth rates < 5%

Cleverer people than me have calculated that the long term average return on a 50/50 portfolio after inflation is about 5% (see the Wade Pfau paper linked above). You may be right, and average returns may have suddenly doubled, but Pfau calculates that the chances of that are about 2-3% based on past returns. Are you happy to bet your financial future on those odds ?

Note that when people are using 2% returns in spreadsheets they are not expecting to only make 2%, they are expecting that there is a 90% chance of making 2% *or more*. They expect returns to be about 5%, but they know that the chance of getting that much or more is only 50%. Those odds aren't good enough for most people. If you were climbing a mountain would you plan on using ropes that had a 50% chance of holding your weight ?

For someone with a big DB pension that covers all their basic needs, such that their investments are just a bonus on top of that, they will probably only be interested in looking at the average returns. But someone relying on a DC pot to fund their retirement is going to want a much higher level of confidence. The maths says that to get 90% confidence you need to use a figure of about 2%.

When you are in your 50s and have nursed your pot through a few market crashes, the warm fuzzy feeling from knowing that you have a 9 in 10 chance of being ok no matter what the market does suddenly becomes very important.

ex-pat_scot

pensionpawn wrote: »

I'm knowledgeable about finance, probably more than most, though no doubt much less than those on this forum. What I find hard to accept (understand) is using a pension growth figure which is arguably only slightly more than rates achievable on significant cash deposits. My pension, not including contributions, grew 15.5% in the last 12 months. Over the last 5 years my growth (not including investments) has been around 6%. I appreciate that a single year view is unwise and a longer term view is more advisable and past performance is no etc.... However surely you peeps, much more experienced than me, must be seeing growth in your holdings higher than mine, and hence should be using less 'conservative' growth forecasts? The risk of using such low growth figures is that you push yourself longer and harder for that higher pension pot valuation at the risk of health failing partially, or totally! I have started to move around 50% of my pension(s) into a SIPP and have witnessed much better growth as a consequence and am now using 10% as a future guideline (eg., Fundsmith / Lindsell Train Global 19% pa average). Perhaps I'm wildly wrong, however I'm not convinced by growth rates < 5%

A few thoughts.

1. check your performance against benchmarks. Without reference to them, your recent performance has no real comparator.

2. there's a fundamental mindset change between assumptions used in accumulation (saving) phase, vs decumulation (spending) phase.

As noted previously, for accumulation I am using 5% real equities return, based on long term mean. That has turned out to be pessimistic for the last decade, owing to macro economic factors (QE, 2008-9 crash rebound, sterling deflation post Brexit vote, "new normal" low interest rate policy).
I do not expect such financial exuberance to continue unchecked, but recognise I cannot predict / call the top of the market.

For decumulation, I will have two figures.
One is the mean best estimate of future returns - still 5% real, based on my equities portfolio.
Two is the "safe" rate. This is the 4% (or 3.5%, or 2.5%...) safe withdrawal rate, at which you are increasingly unlikely to run out of money. Tools such as cFireSim etc allow you to model the likelihood of running out of money before you run out of time.
For me, I expect that my assumptions on what is "safe" will be 4% SWR, but will keep a close eye in early years.

As an example, my 77 year old father keeps having to revise his SWR upwards, even after 25 years of retirement, as his decumulation strategy has been proven to be very much too pessimistic, in spite of covering tech crash 2000, financial crisis 2008 etc.

Linton

I think it is a mistake to try and get "accurate" multi-decade plans based on pseudo-statistics. You do not know what future inflation and investment returns will be and you do not know how your employment, health, and personal wishes and circumstances will pan out. You do have some idea of reasonable bounds on the numbers based on 100 years or more of history, but they may or may not apply to the future. Complex calculations and discussions on probabilities, nth decile, SWR and clever withdrawal strategies dont change this basic fact and to my mind are somewhat futile.

So what do you do? Perhaps do what I did to plan my early retirement which I took nearly 15 years ago and to plan my ongoing expenditure and investment management ever since.....

1) Know where you are now in terms of expenditure, income and assets

2) Make a set of basic assumptions: inflation, rate of return, perhaps tax levels that you feel happy with. At this stage it doesnt matter much what they are as long as they are not wildly different from what has gone one before, but it is prudent to be pessimistic.

3) Decide on objectives: Dates for events such as retirement or death, future expenditure at current prices, asset holdings at a particular date as needed to:


4) Make a year by year spreadsheet plan based on the assumptions and achieves the objectives showing income, expenditure, investment contributions, assets etc at end of each year. What is important is that the plan actually works, so it may be necessary to change the assumptions or objectives.


5) Try to follow the plan for the next year


6) At the end of the year repeat from (2). Normally you should find that you are not very far out, with pessimistic assumptions you will often be ahead of plan. All you need do then is to make minor changes - eg bringing retirement forward by a few months, increasing contributions by a small %. With a long term plan even major events may have limited long term effects since they are spread over the remaiuning duration of the plan.

To quote ex-pat scot

As an example, my 77 year old father keeps having to revise his SWR upwards, even after 25 years of retirement, as his decumulation strategy has been proven to be very much too pessimistic, in spite of covering tech crash 2000, financial crisis 2008 etc.

In a sense that is exactly what you should do as it ensures that each year you are planning on the best information you have available at the time. 25 years ago ex-pat scot's father did not know how the following 25 years would turn out and so was right to be pessimistic.


On the other hand it shows one of the problems of taking SWR too seriously and as cast in stone. Your predicted SWR when you start saving for retirement must, in most cases, be much lower than it eventually turns out to be in practice because you have to take into account uncertainties that become less uncertain as time progresses. Over time it will normally increase because the older you get the less likely it is that there will be a major crash and if there is one you would have normally de-risked your investments so that it would not matter so much anyway. At an age of 90 your SWR is perhaps 10%.



I have no interest in SWR except as a sanity check against what the plan indicates. I have no interest in clever withdrawal strategies because I spend what I want or need to within the limit given by the latest plan.

michaels

Linton wrote: »

I think it is a mistake to try and get "accurate" multi-decade plans based on pseudo-statistics. You do not know what future inflation and investment returns will be and you do not know how your employment, health, and personal wishes and circumstances will pan out. You do have some idea of reasonable bounds on the numbers based on 100 years or more of history, but they may or may not apply to the future. Complex calculations and discussions on probabilities, nth decile, SWR and clever withdrawal strategies dont change this basic fact and to my mind are somewhat futile.

So what do you do? Perhaps do what I did to plan my early retirement which I took nearly 15 years ago and to plan my ongoing expenditure and investment management ever since.....

1) Know where you are now in terms of expenditure, income and assets

2) Make a set of basic assumptions: inflation, rate of return, perhaps tax levels that you feel happy with. At this stage it doesnt matter much what they are as long as they are not wildly different from what has gone one before, but it is prudent to be pessimistic.

3) Decide on objectives: Dates for events such as retirement or death, future expenditure at current prices, asset holdings at a particular date as needed to:


4) Make a year by year spreadsheet plan based on the assumptions and achieves the objectives showing income, expenditure, investment contributions, assets etc at end of each year. What is important is that the plan actually works, so it may be necessary to change the assumptions or objectives.


5) Try to follow the plan for the next year


6) At the end of the year repeat from (2). Normally you should find that you are not very far out, with pessimistic assumptions you will often be ahead of plan. All you need do then is to make minor changes - eg bringing retirement forward by a few months, increasing contributions by a small %. With a long term plan even major events may have limited long term effects since they are spread over the remaiuning duration of the plan.

To quote ex-pat scot

In a sense that is exactly what you should do as it ensures that each year you are planning on the best information you have available at the time. 25 years ago ex-pat scot's father did not know how the following 25 years would turn out and so was right to be pessimistic.


On the other hand it shows one of the problems of taking SWR too seriously and as cast in stone. Your predicted SWR when you start saving for retirement must, in most cases, be much lower than it eventually turns out to be in practice because you have to take into account uncertainties that become less uncertain as time progresses. Over time it will normally increase because the older you get the less likely it is that there will be a major crash and if there is one you would have normally de-risked your investments so that it would not matter so much anyway. At an age of 90 your SWR is perhaps 10%.



I have no interest in SWR except as a sanity check against what the plan indicates. I have no interest in clever withdrawal strategies because I spend what I want or need to within the limit given by the latest plan.

All this talk of adjusting each year sounds very like guyton-klinger.

I still think whether you use a simulation model or historic data it still makes sense to look first whether your worse case scenario (98% success rate, 5th decile) covers your 'minimum spend' number even as you also use your mid estimate when thinking about what you are likely to have to spend.

This does not mean we don't all have to make a trade off between conserving assets to guard against sequence of returns risk but the opposite risk, that we end up with a huge capital pot having failed to live the life we could have through excessive prudence....

Linton

michaels wrote: »

All this talk of adjusting each year sounds very like guyton-klinger.

No - in Guyton Klinger as I understand it you cut your withdrawal immediately in response to a bad year. In my case any cut in withdrawal is spread over the remaining years of the plan and so is much less severe.

I still think whether you use a simulation model or historic data it still makes sense to look first whether your worse case scenario (98% success rate, 5th decile) covers your 'minimum spend' number even as you also use your mid estimate when thinking about what you are likely to have to spend.

This is why I refer to pseudo statistics. What does 98% mean? It certainly is not a valid future prediction. Yes, you can use it to check whether your planned drawdowns seems wildly out, but it is not a satisfactory basis on which to actually implement drawdown.

This does not mean we don't all have to make a trade off between conserving assets to guard against sequence of returns risk but the opposite risk, that we end up with a huge capital pot having failed to live the life we could have through excessive prudence....

Yes you need some way of conserving assets. My approach is to assume a low but adequate investment return and to lower excessive returns by derisking.


Taking SWR too seriously you dont know that your prudence has been excessive until you have built up a huge capital pot. With my approach you would have detected your capital increasing beyond plan well in advance and begun to take some remedial action, like marginally increasing your expenditure by perhaps taking more extravagent holdays or derisking and hence lowering your investment returns.

Audaxer

Linton wrote: »

Taking SWR too seriously you dont know that your prudence has been excessive until you have built up a huge capital pot. With my approach you would have detected your capital increasing beyond plan well in advance and begun to take some remedial action, like marginally increasing your expenditure by perhaps taking more extravagent holdays or derisking and hence lowering your investment returns.

So where you have a year of good returns like last year, and the investment pot has increased above their plan, I appreciate some people may want to drawdown a higher percentage and increase expenditure. As you say the other option if the capital pot is increasing, would be de-risking. If you did not want to de-risk by increasing the percentage of bonds due to the concerns about bonds, I would have thought another option would to drawdown enough cash to cover, say, the next two years expenditure. Would there be any concerns with that strategy?

Robert_McGeddon

I echo what Linton said in post #34.

As well as considering the gross income side of things (which has been discussed at length) I think one has to consider tax in order to arrive at net available income and also projected expenditures to see if you're in surplus or deficit each year. The numbers may change radically between pre and post retirement and later on if care home fees are required.

Perhaps consider modelling an income and expenditure statement and a cash flow statement side-by-side.

Linton

Audaxer wrote: »

So where you have a year of good returns like last year, and the investment pot has increased above their plan, I appreciate some people may want to drawdown a higher percentage and increase expenditure. As you say the other option if the capital pot is increasing, would be de-risking. If you did not want to de-risk by increasing the percentage of bonds due to the concerns about bonds, I would have thought another option would to drawdown enough cash to cover, say, the next two years expenditure. Would there be any concerns with that strategy?

I too am concerned about safe bonds so base my de-risking on a significant holding in specialist Wealth Preservation funds and ITs with a smaller holding in a Strategic Bond fund. Also I regard taking dividend/interest income as somewhat less risky than relying on selling equity and so use excess capital gains to increase the % of income from dividends/interest.


Cash I feel is less satisfactory as inflation is a concern. You cannot get anywhere near inflation matching cash returns inside a SIPP and holding the cash outside the SIPP is inflexible (you cant easily rebalance back into equity) and liable to tax.

princeofpounds

Two important points, which may echo some comments above (I haven't read every post).

- The assumed real rates of return on investments relate to the average return over the lifecycle of the pension. There will be points at which returns are bad and points at which returns are good, but if you are contributing steadily over time then a lot of that will even out.

If you look at the best and worst outcomes over short and long term time periods (in this case for the S&P index, as an example) you can see how return volatility drops sharply over long holding periods. If you are continually investing year-to-year, you will also further smooth out the experience as each 'vintage' of investment will start at different points along the cycle. That narrows the range of outcomes over a lifetime a great deal more than some people appreciate.

https://www.thebalance.com/thmb/qp6_ZTVZ2JXccAskSoMD_SxD9E0=/800x0/filters:no_upscale():max_bytes(150000):strip_icc():format(webp)/SP500IndexRollingReturns-59039af75f9b5810dc28fe2c.jpg

Specific scenarios for future returns matter more as you get closer to retirement, of course, as your end outcome is increasingly dominated by shorter-term returns on a larger accumulated pile of wealth, than long-term returns on future contributions.

This is one of the reasons why younger people in particular shouldn't worry too much about the market having done well in recent years. If it crashes, that raises the potential return on your future contributions, which can actually be more beneficial to your end state.

I just mention this because people can get very wrapped up in what the market has done recently, and what it means for future returns over the medium term, but for a large subset of pension investors it's actually not the thing to focus on.

- The other point I wanted to make is that the value in pension modelling isn't so much in making specific predictions, which are impossible. It's about the process of sticking to a 'glide path' that will get you to within a sensible range of outcomes at the end.

If your estimate of long-term real returns is a bit off, it shouldn't actually matter too much. Every year you run the model, and if realised returns have been higher/lower than your assumption, then you'll see your target outcome change incrementally and you can adjust your savings behaviour to compensate. The important point here is incrementally; you don't have to correct the effect of erroneous assumptions all at one time.

This is a core reason why I don't think that you have to get so conservative as to model a 90% confidence rate on your real return assumption (at least until you get much closer to retirement).

That implicitly assumes that you cannot or will not make compensating adjustments as you go, and it leads to a high likelihood that you will over-contribute early on and under-contribute later in the process. OK over-contributing isn't that common or that bad, but it's particularly inappropriate if you are targeting the maximum lifetime allowance, as it will lead to a very high likelihood of exceeding it (or, more realistically, de-risking the portfolio earlier than would be necessary for investments reasons).

The overall point I want to make is that being a bit conservative is fine, and prudent. But it's not necessary to totally overdo it unless you are nearing retirement and capital preservation is key.