Make your own retirement income cashflow model, stress test and capacity for loss on Excel.
HTML-код
- Опубликовано: 11 окт 2024
- Make your own retirement income cashflow model, stress test and capacity for loss on Excel.
If you're looking to work with a Financial Planner for financial advice and determine if we would be a good fit for you, contact us today:
Website:
featherstonepa...
LinkedIn:
/ edmund-bailey-chartere...
Spreadsheet used in demonstration added to Dropbox below:
www.dropbox.co...
Please note:
The information provided is based on the current understanding of the relevant legislation and regulations and may be subject to alteration as a result of changes in legislation or practice. Also it may not reflect the options available under a specific product which may not be as wide as legislations and regulations allow.
All references to taxation are based on my understanding of current taxation law and practice and may be affected by future changes in legislation and the individual circumstances.
This channel is for information and education purposes only. Any information or guidance given does not act as financial advice. Please consult a financial adviser if you are unsure in anyway.
Keep in mind that the value of your investments can go down as well as up, so you could get back less than you invest.
#pension #retirementplanning #financialplanninguk
Template spreadsheet added to Dropbox from where it can be downloaded:
www.dropbox.com/t/3u528ztkRVFNBPQE
As always, thanks so much for watching! 👍 Appreciate the video takes a bit of time to watch but hopefully it's 34 minutes of your time that will be worth using!
Do ask me should you have any questions or queries and please do let me know how you get on and whether you found it useful? Ed
Brilliant! Exactly what I was looking for. I’ve built a very basic spreadsheet to help me understand how much money I will need in retirement. This is definitely next level. Thank you so much
This is a great approach. Building a sheet for ones specific pension and incoming streams.
Just spent the last week creating a spreadsheet like this. With a mix of DB+DCs. The key variable was the Tax calculations and doing a flex withdrawal of the DC keeping within the 20% tax band.
We will have to see what the Autumn Statement budget (30Oct24) brings!
It's interesting watching you do this as I did someone similar for myself a few months back. I also added in columns calculating the tax to pay as i think keeping tax low is essential in retirement.
You are spot on about the tax.
Hi can you tell me how you added the tax column in?
I'd love to know how you add in the tax column too. Thanks
great spreadsheet, great ideas for forecasting. i added a column, for occassional "lump sum" withdrawals. (new car, kitchen etc). Still works out, clear results. i started pensions in my 20's, but stoped contributing for 20 years! Horrah for compound interest! Lucky enough to be able to save significantly in the last 6 years i have till 67.
Thanks glad it helped. X
Again Brilliant..... Thank you very much for posting this on drop box...... As a very amateur/novice use of excel/sheets I struggled getting my head around the charting and capacity for risk......But this is exactly what I need to push my learning experience......Thank you very much
Thanks!! And pleased to read it’s helped you learn some new things. 👍
This was really useful. I watched the video step by step, created my own with some personalized modifications and hey, I’ve got a tool that I can status/adjust etc as time moves on. Well worth the 30 odd minutes to watch and digest it before starting my own version. Thanks for taking the time to share this.
Thanks that’s good to read! 👍
very helpful and I was able to follow and create my own prognosis. I went a bit further to add annual payrise (16yrs to go). Bonus and additional payments add to contributions. Then I added 2nd state pension column for my wife. Really great spreadsheet, thanks.
Thanks so much and really appreciate that! It’s awesome you have been able to take it and adapt it to fit your situation which was my intention with the video. 👍👍
Good - ive done similar in the past & currently doing it in Google sheets
One suggestion would be using a Named range for Tax etc, makes changes easier
Really useful thanks, I'm now looking at how I can build in my wife's details into the same sheet but we are different ages and will retire some years apart so it's quite a bit more complicated - fun to try though!
Just use year 0,1,2 instead of age in the first column
Thank you so much for providing this spreadsheet and explaining its workings.
Thanks for your efforts Edmund, very interesting and useful spreadsheet, I like the way you have explained each Excel technique used in this.
Thanks, appreciate the kinds words and yes, I did want to really explain it out so that others can replicate to suit their own needs rather than me just punting out an absurdly complex spreadsheet to harvest emails or for people to pay for. 😊
Truly brilliant work. Both a really useful excel tutorial and gives us the power and control to test out our own thoughts. Much appreciated!!! Keep up the great work.
Thanks! That’s very kind of you to say. 👍
Thanks Edmund for a very useful tool. I've produced something similar in the past, but yours is better and I like the use of charts for ease of understanding the possible outcomes. Like others, I have added a column for gross expenditure to allow for tax and then used this for the projections. Plus the use of the F4 button!
Amazing stuff thanks! 🙏
A great tutorial...thank you! Learnt some new Excel skills along the way as well!!!
Thanks so much and I learnt some from the comments as well! 👍👍
This is so useful thank you so much as ever. Really useful to see it all mapped out. Ever since I paid off my mortgage nearly 2 years ago (25 yr mortgage paid off in exactly 8 yrs and 8 months) I've been doing a rough stress-test scenario modelling, not necessarily on an Excel spreadsheet but crude calculations. I look at my expenses, track inflation, look at my DC and DB pots and do an average calculation of how much I will need each year and working backwards to see when and how I can afford to retire fully and partly. I also do best case - worst case scenarios which is helpful to develop Plans A, B, C etc. More than anything else, I'd say it gives me options. Its good to look at the various options available rather than adopt a doom and gloom scenario about the financial future which I did for a long time. So its really useful to see something like this to reassure myself that I am thinking along the right lines.
Thanks for sharing your situation and it’s sounds like you have been doing some wonderful planning and running a ton of different scenarios!! Thanks for the kind comment! 👍👍
@@EdmundBaileyUK Really wonderful videos Edmund. You are doing such a huge service using your time and effort. Yes planning is tricky but essential. In my case I don't have children or dependents, just myself so in an ideal world I'd like to spend it all before I go but also to make sure I don't make any hasty decisions with respect to financial future so its about getting the balance right. Thank you so much once again!
@ushasundaram1 🙏
Outstanding! Very useful and just what I was looking for. Thank you! Subscribed.
Really useful Ed. Thankyou! I am retired so I can simplify the number of columns a little :) I'm sure that I will be returning to this on a regular basis.
Thank you Peter. Glad you found it useful. 👍
Thank you for the spreadsheet and video Edmund. Very useful indeed.
Thanks for the kind comment! 🙏
Hi I’ve also done a similar process one thing I added to the calculation the ‘current’ tax that I’d need to pay, tax is always a requirement 🤣, especially as it’s likely that tax in the future may need to be paid on the basic state pension, quite an impact if your not careful. Also good to pop a comment on each cell where you may leave a stress test, in case you leave them in the Calc, or highlight the cell.
Good video.
Thanks! 👍 And great tips on adding or highlighting the stress tested cells.
hi I have subscribed, this is far far better than what I produced.. Ill watch this a few times then look to alter it as I have a DC pension... thanks this is excellent
Thanks, let me know how you get on.
Great video Edmund. Would be great if you could provide the excel sheet and maybe highlight the cels for the individuals figures? Just a thought 😊 Thanks again!
Thanks!! Great idea! I’ll need to work out how to do that though as no idea how to link to video. Will ask someone that might be able to help me set that up.
Thanks, just added to www.dropbox.com/t/3u528ztkRVFNBPQE and in description.
Brilliant. Thanks for this Edmund, very much appreciated 🙏
Thank you Edmund and a very worthy 34 minutes of video. I, too, have built a "calculator" which by comparison is somewhat simplified and in all honesty limited by my understanding of Excel. I shall re-create your example and see what impact additional influences have on my planning. Again many thanks.
Amazing! Thanks. 🙏 Really pleased that it may well have helped you add to your existing model!
I didn't know about using the dollar around the cell reference, useful to know, thanks!
Or the shortcut is F4. 👍
Great tutorial on creating a robust spreadsheet - I learned a huge amount on formulas. I succeeded in replicating your example, now on to create one for my own circumstances
Honestly great to read you learnt something and that you found it useful, really appreciate the kind comment. 👍
Great model thank you, easy to navigate and experiment with. Really appreciate it! 🙏🏻
Oh brilliant thanks. We've used lots of models and some that are far more complex with multiple scenarios, and do run models with real rates of return and tax but I come back to actually the simplicity is best, as the more data points that are added simply create something that becomes less and less realistic due to the variability of all the data entered. Ultimately its all projections and more complexity does not create something that is anymore realistic.
About to see my pension advisor so was good to watch this and plug in my own numbers. Will try and add in a column for tax also.
Sounds like plan and thanks for the comment!
Hi Edmund, that's a brilliant video. I had done my own version but it was great to see another perspective and I'm going back to play around with my instance. Please do keep the excellent content coming.
Thanks, massively appreciate that comment and that it’s been helpful for you!
Hi Edmund, new to your channel but the last 2 videos have been excellent. Have managed to set up that spreadsheet and found it really useful thanks.
Amazing! Thanks so much! 👍
Thanks for the spreadsheet. I am hoping/planning to only draw a similar amount to growth from my pot, say 4%, so my pot is always there and I can leave it to my son.
Hi Edmund.... Brilliant! Any chance of posting your completed spreadsheet somewhere public so we could all just ammmend to suit ourselves?
Once again Briliant Just what I needed
Amazing and thanks for the kind comment!! This is presently beyond me technically as to how I attach an accessible excel spreadsheet and link it to the YT video but i will endeavour to find out and add once i know how.
www.dropbox.com/t/3u528ztkRVFNBPQE
Thanks for this. Top tip if you're using Windows and want to auto-add the $ symbols to the value, set the cursor anywhere in the value (e.g. for cell A:1, set the cursor anywhere after the A and before the 1) and hit F4 (on Mac Fn + F4). Super little time saver 😀 (PS also works on Google sheets, which are great free alternative for folks without Excel.)
Amazing thanks for the tips!! 👍👍
I use Google sheets extensively every day. Did not know that! Thanks!
Really useful video, thanks for posting the spreadsheet too, very helpful. I was just going to post the F4 tip too :-)
Thanks so much! And rest assured I will never forget the F4 tip. 😃
Edmund, thank you for the video and spreadsheet.
The one thing that is puzzling me is whether your rates of return on the investments are nominal or real rates of return. That is, all of the income figures are shown in future dollars (as they are increased in line with CPI) but it feels as if you projecting grown of the investments in today's dollars. The reason that I think this is your low rate is 1% which would need to be 3.5% if you want to project in future dollars unless you are suggesting th real rate of return on the 'low' assumption is -1%. Are you mixing PV and FV values in the model?
Thanks these are nominal rates of return. I have done a video showing how to calculate real rates of return.
Great video, thanks 😊
Very useful spreadsheet, will give it a try. Thank you
Thanks 🙏 Do let me know how you get on.
Thanks Edmund. Kept up for most of it! Strikes me that of course this is all predictions based on low/medium/high performance. Would be useful if we could insert the fund value to an actual figure annually, say on April 6th. This would give a fund actual will it/wont it provide for the requested period. Could also insert an annual figure for capital withdrawal too!
Thanks! The projection rates are those prescribed by the FCA and you are right, it’s predications and guesswork… and in truth linear returns will not be accurate year to year given the volatility in asset prices. But certainly updating year to year is a great way to track what has happened and how this impacts on projections. I’d always argue it’s better to have a plan then go blindly into saving investing with no clear end game or objective.
Hey Edmund. Really found this useful. My excel skills are a bit limited so I am hoping that you'll be able to post a link to your fantastic spreadsheet.
Thanks for the kind comment!! I’m trying to work out how to do that as not entirely sure how I can link the doc to the channel… it’s a work in progress.
Fantastic upload. Really helpful - thank you!
Amazing thanks! 🙏
Thanks very much for this. Is there any way of reducing the required income later in the retirement period to reflect the natural slowdown of activity and therefore spending?
Thanks yeah you can reduce it by a percentage or manually type it in.
Thank you! You have helped me a lot 🙂
Great video. It would be good to extend to drawing lump sums from a SIPP
Great video, thanks !!
I'll try and amend it to add an ISA pot and a DC pot. I think trying to work out the tax on flexible drawdowns might be a bit of a stretch for me !
Thanks!!! Really appreciate that. And hope you can make something useful. Tax is nice one the ISA as it’s not relevant and so gross still works and you’ll still need to take the gross value out of the pension as you are testing sustainability but you could then simply take the tax off of the gross pension amount e.g 20%
Superb assistance, thankyou, earned a subscription.
Great video Ed. Very useful. Thank you!
Thanks! Appreciated 🙏
Im planning for only having So So Security for the next 3 years. After that i intend to be self funded.
Also im working with yearly inflation currently at 14% (hence pension is almost worthless in 10years time😢).
Mind you we're better off than most as we've been investing since 2008.
14% inflation rate would be incredibly hard to beat on a linear consecutive basis, that would be destructive for most individuals. The average CPI from 1950 to 1988 was 6.4% and then 2.5% from 1989 to Apr 22.
Excellent video; to develop further, split flexible income by tax wrapper, include resulting tax calculation, add spouse data, further split flexible income with asset allocation over time horizon/expected return for asset type, include one off events to expenditure. End result cash flow ladder, but complicated 😂
Thanks!! Yes it’s definitely possible to add lots of complexity but having run both highly complex models and simpler ones… the simpler ones win for me given it’s all projections and therefore ‘guess work’… it’s simply useful as a guide.
Thanks Edmund, what a fantastic video, thanks for sharing it. I’m due to retire next year so it’s proved very helpful. One question, I’m assuming that Income Requirement is what you need to live on once in retirement? Why did you start income requirement at 63 when the DB income (and presumably retirement) doesn’t start until 65? Thanks
Thanks so much and yes income requirement is the amount needed to live on! Best of luck with your retirement and the next phase of your life! 🥂
So well explained, thank you.
Thanks!! 🙏
Fantastic video. Do you also have a video describing example portfolios? For which funds could be used for different risk profiles, how many funds/indexes to use and allocations?
Thanks so much and no I don’t but a good idea for one. 👍
@@EdmundBaileyUK It would be good, as there are vids, like yours that explain taking an amount to draw or 60/40. Or a larger balanced portfolio. But explaining the fund/indexes examples to actually hold, when to take your income, would be very helpful.
Well that was excellent...! Just one thing, I'm quite good at the cell bits, but the charts were zipped through quite quickly and try as I might I couldn't get them to work...
Thanks so much! Are the numbers not looking quite right? I’ll try and link the example doc but not entirely sure how to do that yet.
Thank you for this
Thanks this spreadsheet is great
Thank you so much! 👍 Hope you find it useful and can adapt it easily to your own situation.
Thank you this was excellent 🙏👏
Thanks 🙏
Thanks Edmund, really good sheet, I combined it with but own 'how much do I need to live' sheet from which I derive my income needs @ retirement point and then your sheet forecasts nicely. Curious about where the l/m/h rates come from, conservative ? Also is it realistic to expect a crash every 10 years? seems likely but why 19% ? Thanks again
Thanks so much for the kind words! The growth rates are those prescribed by the FCA. And the reasoning behind the crashes every ten years is really to consider the impact of significant drawdowns over a given period. The declines will be dictated by the level of risk and volatility being taken but certainly a moderately high risk portfolio could see declines of c 19% in a given year.
Great video. I have my own version but will add some stress test scenarios. I also look at values in today’s money I.e I exclude inflation for income and costs and also use the real rate of return on investments. I know that inflation is a significant factor and I have accounted for it but I find it easier to visualise where I am today and what to aim for. Do you have a view on this?
Thanks for the kind comment! 👍 Yes real vs nominal, I think this comes down to personal preference, I tend to use real when working with clients as it’s easier to explain whereas some people find looking at real values odd due to not seeing the actual values increase. But amending the growth rates downwards using the appropriate formula will give the real value.
Wow, this is brilliant thank you!
Oh amazing thanks for that! 👍
Thanks Edmund, this is really helpful and easy to copy.
Only thing I would have liked would be for an additional ‘cash’ capital holding column, which might be used,say, for paying for an early retirement year or two before drawing on the investments.
Great and thanks for the kind comment. 🙏 And yes that’s easy to add onto the spreadsheet if you need to.
Amazing - thank you
Thank you for providing such a great video, I had also built myself a something a similar but it was great to watch and build your spreadsheet with your analysis.
Thanks! 👍
Fantastic video. Thank you. How do they work out a Bogleheads variable withdrawal percentage for each year?
Thanks - great video! I was doing similar calculations myself and wanted to take it to the next level i.e. to use real historical data market return for the last xx years and see what happens if the simulation starts in different times in the history (rather than using low-medium-high cases). I believe similar calculations are done in some commercial software for pension planning - you show some screenshots of that in different videos. Unfortunately, I couldn’t find any time series historical data on the internet. Do you know if there are any global market return data for last 50+ years available free of charge (or for a low fee) somewhere on the internet?
Thanks! Yes its absolutely possible to add in historical data sets from actual periods of time. Although I always tend to go back to basics and focus on linear models with low, mid and high values as simplistic as this is. Partly because all of these models are as a guide only but it is interesting to see a more realistic output in terms of the up and downs of the market. The US data is easy to find here: pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/histretSP.html
Hi Ed, really great spreadsheet, thank you for sharing, it is making my life so much easier to set up the cashflow analysis.
Just one thing though, I am struggling with setting up column L, its just not calculating correctly for me, are you able to help?
Thanks so much for the kind comment. The capacity for loss column will need to be recalculated using the ‘what if analysis’. It will need to be recalculated each time any numbers are amended in the main table.
@@EdmundBaileyUK Hi Ed, what is the best way to add my husbands details in it. Hes has already retired and is drawing a state pension only at this stage. We want to see how much we could draw from our private pension from next year.
Really useful I’ll have a go!
The capacity for loss column doe snot include the 10 year market drops you added to stress tests though...does it?
Correct, it does not.
Brilliant thank you 👍
I'm trying to build my own model but at age 30 the figures quoted at hopeful retirement age are astronomical for example state pension i calulated to be 28k by the time ill be elegible! Should i be planning this far ahead or is it not worthwhile?
Yeah it’s worth it. Definitely in terms of your own projected pension values. The State Pension is trickier as it will likely be adapted or amended in some way. Although those values look high they are in nominal terms so actually it’s the same purchasing power as today.
Could be worth doing it using ‘real’ returns rather than nominal as per the recent video I did.
@@EdmundBaileyUK thanks will check out the other video. Really helpful content, keep up the great work 👏
I can follow this other than the Contribution part, is this 3 extra payments of 10k into the pension in the last 3 years (sorry for being a bit dim here i guess i fit my profile pic well)
Not dim at all, just added the contribution of £10,000 per year for 3 years from age 60 just to show how to add in contributions.
Great example, but adding 20% band tax makes a big change to requirements and end results. Could you add a column for 20% tax?
Thanks!! 🙏 Yes you can adjust your figures for tax to get to your net income. But of course when calculating sustainability you will need to take out the gross figure from your pots and not net. The calcs in this example are based on gross incomes.
Sequence of returns!!!
Yes, you can stress test it by front loading stock market declines in the earliest years... its up to you.
Great video - thank you. I will be contributing for another 10+ years and I have my own spreadsheet but it doesn't quite match your numbers (using the same assumptions). Your formula doesn't add any growth for the contributions as it is added as a single lump sum value (but many people will be adding contributions on a monthly basis over the year). I've found this small difference starts to snowball! Just a suggestion that you can add in any v2... Thank you again!
Thanks for the kind comment. Yes the contributions can be increased as well, technically they should go up by inflation but in reality not everyone's pay does go up by inflation or often it is less than inflation. I can look also to do a follow up using both real and nominal growth rates as I know some people prefer the values in 'real' terms rather than nominal.
@@EdmundBaileyUK increasing contributions is one part - but what I'm referring to is the actual growth on a monthly basis for contributions. An example is - instead of putting £12k in as a lump sum, if you put £1k in a month for a year, then the first monthly £1k will be growing at the low/mid/high rate for the whole year, then repeat for each remaining monthly contribution which is growing for a shorter time in that years calculation. This is a bit like regular savers from banks where they offer 7% interest but you get approximately half of this rate because of contribution timing. My calculation adds roughly half of the growth assumption (1/4/7%) and adds it as additional growth for the contribution . Hope that makes sense!
Thanks, sorry I had the wrong end of the stick. You are right the calculation in my model assumes the growth is before the contribution and the contribution comes in one lump sum at the end of the year and does not benefit from the growth. I've always tended towards a simpler more cautious position as a preference.
Thank very interesting.
thanks for the download link, im struggling as i retire next year , how do i change the date the state pension starts etc
Your State Pension age is next year?
@@EdmundBaileyUK yes when im 66
So you need to change the ages on the left handside so it starts from your current age 65. And then you need to put your state pension next to age 66 and based on 2.5% increase, assuming it’s full state pension it will start at 11,787. Quite hard to explain in a message but hope that helps.
Great video which shows that an 8% pension contribution is nowhere near enough to give you a nice pension retirement income.
Thanks and very true.
Thanks for the video some food for thought. One point I'd like to get your opinion on is that you have included 20% stock market crashes for stress testing but you haven't included the reverse of stock market bull rallies where the market increases by a lot more than the high return rate.
Take for example the S&P 500 which lost 18% in 2022 but then bounced back with 26% return the following year. So this model really is the doomsday scenario and ultra cautious is it not?
Thanks 🙏 It is a cautious position to assess poor but realistic outcomes and whether or not the capital would deplete under those outcomes.
Hi, can you just clarify the "contribution" column please.. Also, presume this is based on draw down?
The contributions are simply payments into the account, investments into it for a few years before withdrawals are taken.
Dude. F4.
👍
Really useful. Thanks so much, Ed.
Thanks. 🙏