We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
Retirement Cashflow Models

Malvolio
Posts: 17 Forumite


I have recently retired with a DB pension accrued over more than 40 years. Ignoring inflation, this should provide the majority of our target retirement income and will be supplemented in due course by SPs for both of us plus my wife's very small DB and draw down from savings and investments.
My DB has a number of tranches and GMP elements with different inflation caps. Whist my pension and cash lump sum values at commencement look very good, the inflation caps on the tranches within the residual pension will seriously erode its value if inflation remains significantly above 2.5% RPI for a long period. The freezing of tax personal allowance and thresholds until 2026 will do further significant damage and savings and investment returns will probably not keep pace with inflation either.
I've build an Excel spreadsheet that calculates our target net income, net pensions income and the required drawdown from savings and investments. It takes into account and allows me to vary scenarios and assumptions covering longevity, changes in target income, inflation and its effects on target income and gross pension incomes (taking each of my DB tranche caps and the SP triple lock guarantees into account), savings and investment returns, and changes in personal allowance, tax thresholds and tax rates.
The main purpose of the spreadsheet is to calculate the annual drawdown needed from savings and investments to supplement the net pensions income to meet our target income. It also calculates the residual balances of the savings and investments and whether these will be sufficient for each scenario/set of assumptions.
I'm reasonably confident in the formulas and calculations I've built into the spreadsheet but far less confident in the assumptions I'm using for inflation, savings and investment returns and taxation. It also does not do any probability modelling, just calculations based on the input data.
Is there a model available that takes account of all the above factors/elements and also provides probability modelling of the likelihood of maintaining our target income using historical data for inflation, savings and investment returns and tax policy?
My DB has a number of tranches and GMP elements with different inflation caps. Whist my pension and cash lump sum values at commencement look very good, the inflation caps on the tranches within the residual pension will seriously erode its value if inflation remains significantly above 2.5% RPI for a long period. The freezing of tax personal allowance and thresholds until 2026 will do further significant damage and savings and investment returns will probably not keep pace with inflation either.
I've build an Excel spreadsheet that calculates our target net income, net pensions income and the required drawdown from savings and investments. It takes into account and allows me to vary scenarios and assumptions covering longevity, changes in target income, inflation and its effects on target income and gross pension incomes (taking each of my DB tranche caps and the SP triple lock guarantees into account), savings and investment returns, and changes in personal allowance, tax thresholds and tax rates.
The main purpose of the spreadsheet is to calculate the annual drawdown needed from savings and investments to supplement the net pensions income to meet our target income. It also calculates the residual balances of the savings and investments and whether these will be sufficient for each scenario/set of assumptions.
I'm reasonably confident in the formulas and calculations I've built into the spreadsheet but far less confident in the assumptions I'm using for inflation, savings and investment returns and taxation. It also does not do any probability modelling, just calculations based on the input data.
Is there a model available that takes account of all the above factors/elements and also provides probability modelling of the likelihood of maintaining our target income using historical data for inflation, savings and investment returns and tax policy?
0
Comments
-
I’m not aware of such a model. There are models that calculate, based on historical data or other statistical approaches the probability of different drawdown strategies running out of cash and I expect you have seen those. There are some model results that show that dynamic drawdown strategies- varying how much you spend based on investment returns - can help to increase living standards in most cases.My view on this is that you can create scenarios and work out what you’d do in each, but that assigning probabilities to different scenarios is ultimately guesswork.I don’t think anyone can ever get total certainty. If you had the model with all the variables, what would you expect from it? Since it would be guessing, in the end.For me, it’s not only about whether I will have enough money. It’s also about how much everyone else has. In the dire scenarios, I’ll be poor, but in that case so will everyone else and we can huddle together for warmth. If everyone is poor I won’t feel left out.So I reckon it’s only worth worrying about dire scenarios if the outcome for me would be out of line with everyone else.0
-
0
-
That's where annuities step in. Providing a guaranteed income for life. Too many variables to make forecasting with any accuracy a non starter. Certainly not a productive use of time. As the investments are unlikely to look after themselves.0
-
Unfortunately this is a question that in my opinion should have been asked several years before retirement and not after, as some variation may have been possible. The value of the pensions has not been mentioned, nor your age and if other avenues were considered before retirement, such as possible transfer to a SIPP, where greater control is possible.
I found that the Excel spreadsheets were and are very helpful and had mine set up several years before I retired. It helped me decide when to stop work and when to actually start drawing State pension, That alone did make a big difference to the overall pot, which I eventually moved to a SIPP. That has grown considerably over the years and only the last year has slowed down due to the financial markets and my Excel spreadsheet tracks the performance on a regular basis.
Tables and calculations can only give a rough guide and there are so many variable that cannot always be covered and the lifestyle that you wish to have during retirement is very important. Having already retired I believe it's too late to start more calculations now, but anyone else should seek advice well before they wish to retire and discuss the possible results that could be achieved by other routes.I'm a retired IFA who specialised for many years in Inheritance Tax, Wills and Trusts. I cannot offer advice now, but my comments here and on Legal Beagles as Sam101 are just meant to be helpful. Do ask questions from the Members who are here to help.0 -
Thanks for the replies.
SeniorSam, I did indeed start my spreadsheets years ago and they were a big help in making the decision to take early retirement.
With my DB pension, considered transferring out but pretty much every thread on the subject on here over the last few years has cautioned against it unless there are special circumstances. In sticking with the DB, I had options on pension commencement of how much cash lump sums to take. I tried to find an IFA who would help me with this decision via the Unbiased website but after speaking with a couple who responded, none were interested. The ones I spoke with gave the impression they only were interested in helping me decide what to do with any lump sums once I'd made the decision.
I've decided to take one of the higher lump sum options to provide additional savings and investments for draw down and reduce the level of reliance on the pension. My spreadsheets enabled me to test all options against different sets of assumptions and scenarios and whilst none of the options met our target income in every scenario, the one I've chosen appears to be least worse in my high inflation/low returns scenarios.
So having helped me decide how much pension to commute for cash lump sums, my spreadsheets are running out of steam in the next set of decisions which is what to do with the cash. I'm sure it will be easier to get IFA help with this but I'm not in a rush and would like to get a better feel for the options before I seek professional help.0
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 349.8K Banking & Borrowing
- 252.6K Reduce Debt & Boost Income
- 453K Spending & Discounts
- 242.8K Work, Benefits & Business
- 619.6K Mortgages, Homes & Bills
- 176.4K Life & Family
- 255.7K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 15.1K Coronavirus Support Boards