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

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.

@geoffcowen8912

Ай бұрын

You are spot on about the tax.

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

@EdmundBaileyUK

Ай бұрын

Thanks!! And pleased to read it’s helped you learn some new things. 👍

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.

@EdmundBaileyUK

21 күн бұрын

Thanks glad it helped. X

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.

@EdmundBaileyUK

Ай бұрын

Thanks that’s good to read! 👍

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!

@steveparkin6557

2 күн бұрын

Just use year 0,1,2 instead of age in the first column

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.

@EdmundBaileyUK

Ай бұрын

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. 👍👍

Thanks for your efforts Edmund, very interesting and useful spreadsheet, I like the way you have explained each Excel technique used in this.

@EdmundBaileyUK

Ай бұрын

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. 😊

Great model thank you, easy to navigate and experiment with. Really appreciate it! 🙏🏻

@EdmundBaileyUK

Ай бұрын

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.

Thank you! You have helped me a lot 🙂

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.

@EdmundBaileyUK

Ай бұрын

Amazing! Thanks. 🙏 Really pleased that it may well have helped you add to your existing model!

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.

@EdmundBaileyUK

Ай бұрын

Thanks, massively appreciate that comment and that it’s been helpful for you!

Thank you for the spreadsheet and video Edmund. Very useful indeed.

@EdmundBaileyUK

Ай бұрын

Thanks for the kind comment! 🙏

Great video, thanks 😊

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

@EdmundBaileyUK

Ай бұрын

Thanks, let me know how you get on.

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.

@EdmundBaileyUK

Ай бұрын

Thanks! That’s very kind of you to say. 👍

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.

@EdmundBaileyUK

Ай бұрын

Amazing! Thanks so much! 👍

Thank you for this

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.

@EdmundBaileyUK

Ай бұрын

Thank you Peter. Glad you found it useful. 👍

Very useful spreadsheet, will give it a try. Thank you

@EdmundBaileyUK

Ай бұрын

Thanks 🙏 Do let me know how you get on.

I didn't know about using the dollar around the cell reference, useful to know, thanks!

@EdmundBaileyUK

Ай бұрын

Or the shortcut is F4. 👍

Fantastic upload. Really helpful - thank you!

@EdmundBaileyUK

Ай бұрын

Amazing thanks! 🙏

Superb assistance, thankyou, earned a subscription.

Great video Ed. Very useful. Thank you!

@EdmundBaileyUK

Ай бұрын

Thanks! Appreciated 🙏

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.

@EdmundBaileyUK

Ай бұрын

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! 👍👍

@ushasundaram1

Ай бұрын

@@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!

@EdmundBaileyUK

Ай бұрын

@ushasundaram1 🙏

Really useful. Thanks so much, Ed.

@EdmundBaileyUK

Ай бұрын

Thanks. 🙏

So well explained, thank you.

@EdmundBaileyUK

Ай бұрын

Thanks!! 🙏

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.

@EdmundBaileyUK

Ай бұрын

Thanks! 👍

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

@EdmundBaileyUK

Ай бұрын

Honestly great to read you learnt something and that you found it useful, really appreciate the kind comment. 👍

Wow, this is brilliant thank you!

@EdmundBaileyUK

Ай бұрын

Oh amazing thanks for that! 👍

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.

@EdmundBaileyUK

Ай бұрын

Thanks! 👍 And great tips on adding or highlighting the stress tested cells.

Great video. It would be good to extend to drawing lump sums from a SIPP

Thank you this was excellent 🙏👏

@EdmundBaileyUK

Ай бұрын

Thanks 🙏

Thanks this spreadsheet is great

@EdmundBaileyUK

Ай бұрын

Thank you so much! 👍 Hope you find it useful and can adapt it easily to your own situation.

Great video which shows that an 8% pension contribution is nowhere near enough to give you a nice pension retirement income.

@EdmundBaileyUK

12 күн бұрын

Thanks and very true.

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.

@EdmundBaileyUK

Ай бұрын

Sounds like plan and thanks for the comment!

Brilliant thank you 👍

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.

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!

@EdmundBaileyUK

Ай бұрын

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.

Really useful I’ll have a go!

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!

@EdmundBaileyUK

Ай бұрын

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.

@user-si7fj5rh5u

Ай бұрын

@@EdmundBaileyUK You mean you aren't trying to get us to sign up to your email list to download it?! I'm shocked :)

@EdmundBaileyUK

Ай бұрын

Thanks, just added to www.dropbox.com/t/3u528ztkRVFNBPQE and in description.

@1963dalek

Ай бұрын

Brilliant. Thanks for this Edmund, very much appreciated 🙏

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 !

@EdmundBaileyUK

Ай бұрын

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%

Thank very interesting.

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.)

@EdmundBaileyUK

Ай бұрын

Amazing thanks for the tips!! 👍👍

@revelationmd

Ай бұрын

I use Google sheets extensively every day. Did not know that! Thanks!

@scr33tch

Ай бұрын

Really useful video, thanks for posting the spreadsheet too, very helpful. I was just going to post the F4 tip too :-)

@EdmundBaileyUK

Ай бұрын

Thanks so much! And rest assured I will never forget the F4 tip. 😃

Fantastic video. Thank you. How do they work out a Bogleheads variable withdrawal percentage for each year?

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.

@EdmundBaileyUK

Ай бұрын

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.

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.

@EdmundBaileyUK

Ай бұрын

Great and thanks for the kind comment. 🙏 And yes that’s easy to add onto the spreadsheet if you need to.

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...

@EdmundBaileyUK

Ай бұрын

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.

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

@EdmundBaileyUK

Ай бұрын

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.

@EdmundBaileyUK

Ай бұрын

www.dropbox.com/t/3u528ztkRVFNBPQE

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 😂

@EdmundBaileyUK

29 күн бұрын

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.

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.

@EdmundBaileyUK

Ай бұрын

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.

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?

@EdmundBaileyUK

Ай бұрын

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.

Sequence of returns!!!

@EdmundBaileyUK

Ай бұрын

Yes, you can stress test it by front loading stock market declines in the earliest years... its up to you.

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

@EdmundBaileyUK

27 күн бұрын

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.

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?

@EdmundBaileyUK

Ай бұрын

Thanks so much and no I don’t but a good idea for one. 👍

@aarondawkins8668

Ай бұрын

@@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.

Great example, but adding 20% band tax makes a big change to requirements and end results. Could you add a column for 20% tax?

@EdmundBaileyUK

Ай бұрын

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.

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

@EdmundBaileyUK

12 күн бұрын

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! 🥂

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?

@EdmundBaileyUK

9 күн бұрын

Thanks yeah you can reduce it by a percentage or manually type it in.

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?

@EdmundBaileyUK

26 күн бұрын

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

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)

@EdmundBaileyUK

Ай бұрын

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.

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?

@EdmundBaileyUK

3 күн бұрын

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.

thanks for the download link, im struggling as i retire next year , how do i change the date the state pension starts etc

@EdmundBaileyUK

Ай бұрын

Your State Pension age is next year?

@Jasperroodog

Ай бұрын

@@EdmundBaileyUK yes when im 66

@EdmundBaileyUK

Ай бұрын

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.

The capacity for loss column doe snot include the 10 year market drops you added to stress tests though...does it?

@EdmundBaileyUK

3 күн бұрын

Correct, it does not.

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!

@EdmundBaileyUK

17 күн бұрын

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.

@tc5122

17 күн бұрын

@@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!

@EdmundBaileyUK

17 күн бұрын

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.

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?

@EdmundBaileyUK

Ай бұрын

Thanks 🙏 It is a cautious position to assess poor but realistic outcomes and whether or not the capital would deplete under those outcomes.

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?

@EdmundBaileyUK

11 күн бұрын

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.

@gregandrews4364

11 күн бұрын

@@EdmundBaileyUK thanks will check out the other video. Really helpful content, keep up the great work 👏

Hi, can you just clarify the "contribution" column please.. Also, presume this is based on draw down?

@EdmundBaileyUK

22 күн бұрын

The contributions are simply payments into the account, investments into it for a few years before withdrawals are taken.

Dude. F4.

@EdmundBaileyUK

Ай бұрын

👍