Updating the spreadsheets

Triumph13

I have a query for those who, like me, have been busily using spreadsheets to plan their retirement numbers. In common, I believe, with most people all my forecasts are prepared in today's money terms and using after inflation rates of return.


I'm now faced with updating spreadsheets prepared last year and therefore in last year's money to put them to this year's money. It's simple enough to update contribution rates, expected DB and state pensions, etc. but how to handle the existing DC pot ? Being largely equities it's a volatile number so I'd be interested to know what approach other people take. Options that occur to me are:

1. Pick the actual value on a particular date and do a true up (I have daily updated values available).
2. Take the predicted value from my existing spreadsheet and increase it by RPI once a year.
3. Increase all historic figures (last year's opening balance and contributions) by RPI to put the whole spreadsheet into today's terms
4. Some kind of hybrid eg take the lower of 1 or 2 above.

I'm leaning towards option 2 and then monitoring actual v predicted investment returns separately, but that may just be because I'm ahead of predictions at the moment!
Any thoughts?

Linton

I have a cash flow planning spreadsheet, one column per year, based on parameters like assumed investment return, inflation rate, tax allowance etc. Once a year I replace the starting point by the current values of my cash and share accounts at that time, which sounds like your option 1. Budgetted expenditure is updated in future years from a base value of current budget (not current expenditure), which in turn came from a budget many years ago updated by actual CPI.

It doesnt seem sensible to base your long term plans on data known to be incorrect and I dont see why you would want to update historical data.

Triumph13

Linton wrote: »

It doesnt seem sensible to base your long term plans on data known to be incorrect.

But whatever figure I put in will be 'incorrect' very quickly. I've watched my fund drop 5% in one month and recover the next a couple of times recently. Choosing the value from a particular day which may have been peak or trough and locking it into the plan for a year isn't necessarily the best idea.
What I'm seeking to do is distinguish between long term growth trends and short term volatility. I monitor separately how the actual fund is doing against forecast value and the hope is that the forecast path (straight line) should, over time, look something like the best fit line for the actual data (squiggling about all over the place).
Given the volatility of the actuals, using what's essentially a random point on that graph to rebase the forecast line each year doesn't seem very sensible.

Thrugelmir

I assume that my investments will only rise in line with inflation. Keep it conservative. The market may be in depression at the point I start to draw an income.