Forecasting - Accumulation

BlobCore

Hi folks, despite all the helpful links I've found to various spreadsheets to forecast the stages of accumulation and de-accumulation I've decided it's time to make my own. Everything I can find is either far too simplistic to take all the variables into account or uses formulas so complex i have no hope of tailoring to my needs. Going to attempt something using only simple formulae whilst being as comprehensive as my understanding allows.

Below are my workings and assumptions for accumulation.  I'd really welcome any constructive feedback and comments. If it ends up anywhere near decent I'll be happy to share of course.

As this may get lengthy I'll post my equivalent de-accumulation in a separate post!

Accumulation;
(Columns in sheet)
Start of year value (SV)
Annual Return (AR)
Annual Cashflow (ACF)
Tax relief (TR)
Fees (Fx)
End of year value (EV)

(Variables)
Expected return = 5% (ER)
Cashflow = CF
Cashflow increase = 2% (CF∆)
Fx (different for each account)


Each row in the table represents a calendar year and looks like this with some example figures
Year - Date - Age -  SV       -    AR    -    CF      -   TR    -      Fx   -   EV (forecast)   -    EV (actual)
1.        2021.    44    £1000.  -    £50   -   £2400. -   £600  -     £18  -    £4032              -      ???
2.        2022.    45
etc...

Calculations

SV Year n+1 = EV Year n. 
AR = SV * ER
ACF = CF + CF∆
TR = CF * 0.25
Fx = SV+AR+CF+TR
EV = (SV+AR+CF+TR) - Fx

Notes:
All calculations on a yearly basis whereas in reality contributions etc. are monthly. (does this make totals higher or lower than my model?)
I run a few separate tables 2 for SIPPs, 1 for S&S ISA, 1 for cash savings.
I've extrapolated this forward to hoped retirement at 60 in 2037 with a few assumptions;
2% cashflow increase to account for inflation.
SIPP1 - 100% large cap US tech stocks - 5% AR
SIPP2 - 80 / 20 global ex US equities / short term bonds - 5% AR
S&S ISA, same investments as SIPP2
Cash savings 1%
Ignores any work bonus, windfalls, inheritance as extras, also ignores any periods where contributions have to drop...
Each year I'll update SV with real EV rather than forecast and see if that implies increasing CF to keep on track.

Sorry for the ramble, would be easier if I knew how to post a spreadsheet!

All feedback appreciated!

Blob

Thrugelmir

With a high overseas exposure, currency exchange rates will make forecasting accurately very difficult.  Likewise market returns are not linear. After high growth periods expect lower ones. Save as much as you can as early as you can.  The latter years can then (a) perform heavy lifting with income reinvestment (b) allow flexibility depending on personal circumstances and market conditions (c) Part of the portfolio can be winterised and positioned more defensively. 

hugheskevi

Few thoughts:

• It looks like you are working in cash terms, rather than real terms. Personally I like that approach (you can always discount to real terms at end if you like) but many prefer working in real terms.
• Personally I keep things simpler by using return net of fees, but that is just  a preference.
• Is there any benefit in splitting tax relief out from contribution? Personally I just use gross contribution
• I'd increase contributions ("Cashflow") by more than prices, rather, something closer to expected earnings increase so more like 4%. However, if you are close to a key threshold, eg higher rate tax, a more tailored contribution structure may be appropriate. 
  
• Expected rate of return is a key assumption. Whatever you assume will be wrong, probably very wrong, in each overall year. A 5% nominal rate of return before fees is a reasonable enough assumption, perhaps cautious if you intend to keep a high equity percentage
• Assuming a linear rate of return assumes away issues like sequence of return risk in drawdown or very adverse market changes on the eve of retirement. This may or may not matter, depending on how you plan to use this.
  

SouthCoastBoy

I think you need to take account for inflation at year level (my default is 2.5%, this year and for the next 3 years I have set to 4%), I have projected growth as a percentage, derived at year level so can adjust accordingly, with a default of 3.5%
I ignore fees I just have a valuation of my pot at year end (which in my case is FY year end) this will already have taken fees into account.
I also have a 2013 column and a 2020 column as well as real time so I can get some context about current values as I my spreadsheet covers the next 34 years. The 2013 column is a legacy from when I started but keep it as a reference point, 2020 will be defunct at some point and I will introduce another column (most probably around 2025)

By having everything at year level it gives the opportunity to complete some "what if" analysis (e.g. set growth at -20% for 3 consecutive years, or -50%, -10%, +20% etc.). Also can do the same with inflation.

During de-accumulation phase I have all required spending in 2020 terms and use my inflation factor (with 1 being 2020) to derive the real time value required for the year. (e.g. to have £1500 a mth in around 14 years time will cost me around 26k  in 2020 money accounting for tax and my current inflation figures assuming tax threshold keeps up with inflation.

[Deleted User]

Its your time and you are very welcome to waste it but this exercise is completely pointless.  You are adding a lot of complexity and pretend accuracy to something that is inherently uncertain. In 16 years your 90% stock portfolio could show a zero percent growth or it could grow by a factor of 3.  You have no control over growth.  You can control 3 things:

1. Your contributions.
2. The timing of your retirement.
3. Costs.

Focus on the things you can control. 

SouthCoastBoy

I agree it is very uncertain, however I have found it very useful, and continue to refine my model. It gives me confidence in my numbers and also allows me to complete what if analysis on some extreme edge cases. 

Linton

Deleted_User said:

Its your time and you are very welcome to waste it but this exercise is completely pointless.  You are adding a lot of complexity and pretend accuracy to something that is inherently uncertain. In 16 years your 90% stock portfolio could show a zero percent growth or it could grow by a factor of 3.  You have no control over growth.  You can control 3 things:

1. Your contributions.
2. The timing of your retirement.
3. Costs.

Focus on the things you can control. 

Disagree that the exercise is a waste of time.  I have done much the same thing for the past 20 years both before and after retirement.  The exercise is eessential if you are going to financially manage your path to retirement and beyond.


There are  other things you can and need to control.  Most importantly
 - Objective
 - Level of risk you need to take, which drives asset allocation

Where I would question the OPs approach is in the title "Forecasting".  The purpose is not really to forecast the future but rather to identify your objectives and what you need to do to achieve them, measure your progress to meeting your objectives, to determine at an early stage when you are off target and to identify changes you need to make.

So I suggest:

Initially:
1) specify your objective in terms of when you want to retire and what lump sum you want available at that time. 
2) set up your spread sheet with whatever assumptions you need to make to achieve your objective.  It does not matter whether your assumptions prove to be accurate, just not totally unreasonable.
3) Sanity check all your assumptions and if necessary adjust until you are happy, iterating from (1) until the plan works.

Each year
1) Update your plan with actuals
2) Calculate the resultant lump sum at your chosen retirement date.
3) Analyse the results
 - If they are what you wanted carry on until the next year
-  If its significantly better or worse review your objectives and assumptions, Change them until you are happy with them and the plan works again.  For example if you are well ahead of target you may choose to move your retirement date forward or to lower your investment risk.  If not you may accept that you need to work longer or contribute more.

Working in this way you are in control of how you are meeting your objectives and are able to make changes well in advance.

BlobCore

Thanks for the feedback folks. Not sure I understand or agree with a few points but all thoughts welcome.
To clarify here which I probably should have mentioned. The objective here isn't to build a super accurate forecast taking into account every possible scenario and variable.

It's just to give me a rough idea as the years progress if I'm on target to hit "The Number" at my target age and an indication if I need to adjust contributions, fees, risk, allocations etc. as I progress. Currently it's predicting using assumptions above that I'm on track but early days!

Terron

I don't think it is worth the effort. I just assumed that things would keep up with inflation. That meant I could just work in today's values. It is a rather pessimistic assumption, but leads to pleasant surprises.

BlobCore

So you assume portfolio growth = inflation? I'd hope things would be better than that but your right could led to a nice surprise!

The 2% i plan to increment contributions is admittedly probably low for the coming years at least. I've based this more on what I expect my pay to increase by. Of course may need to adjust to keep on plan.

On the drawdown side I'm kind of making a similar assumption. I'm ignoring increasing annual drawdown to account for inflation and assuming a move to lower volatility investments aimed at just matching inflation.

Terron

Yes. It is lower than is likely, but that reduces risk as well as eliminating the need to try to predict the future.

When I was made redundant at 54 I had a nice surprise as I found I had enough not to need to work. Though it did not feel right to live off my capital, so I put what was not in my pensions or ISAs to work in BTLs and lived off the income from that.