It's time to start digging up those Squirrelled Nuts!!!!

Sea_Shell

Audaxer said:

Sea_Shell said:

I don't have a template as such, i just played around with Excel.

I have the following columns.

Year
Starting fund
Growth in £  (based on an adjustable % in another cell)
Fund plus growth
Estimated Spends - adjustable (linked to an adjustable inflation % each year)
Income ( nil at present and then includes DB/SP when due)
Net Balance
Number of years spends at this rate

Copy the Net balance back to the next line down as the starting fund for year 2, repeat. 
You need to play around with the formulas a bit to get them to do what you need.

It basically tells me that if i had say a starting pot of £500,000, with a 3% growth rate, that would give me growth in £ of £15,000.
If I then said I would spend £20k per year, increasing at 2% inflation, it would show i'd have £495,000 for year 2, with growth in year 2 of £14,850 and spends of £20,400.   Drag the formulas down the columns to see when (if) you run out of money!!    Using the above, shows 25 years of spending, initially, with £400,000 left after 12 years.

£166,200 of growth, to offset £266,400 of spends.  

Hope that helps.

Sea_Shell, I have something similar, but just wondering if you also compare that 3% average growth with different sequence of returns? 

If I also take an average 3% growth rate over 30 years, I compare different sequence of returns over that period. If for example I sample a bad sequence of returns in the first decade on one sheet, and a good sequence of returns over that period on another sheet, my portfolio value after 30 years with a bad early sequence of returns will be a lot lower than with a good early sequence of returns. As a result I keep a healthy amount of cash savings, so that I will not need to drawdown from investments during years of negative growth. 

Right, i've punched some numbers are here are various scenarios, that hopefully won't come to pass...Based on day one pot of £560,000, and annual spends starting at £15,000.

Zero Growth and 5% inflation - £330,000 remaining after 12 years.
-5% growth and 5% inflation = £100,000 remaining after 12 years.
-8% growth and 5% inflation = £50,000 remaining after 12 years.
Initial -30% in year one and then Zero growth thereafter with 5% inflation = £160,000 remaining.

As the future is likely to be a combination of all of the above at any given time, then hopefully our numbers stack up.   My spreadsheet isn't yet advanced enough to model changes throughout that 12 year period, i.e. up one year down the next etc. but it at least gives a signpost to what could happen.    Other than that, we're just winging it!!!!

Sea_Shell

RetSol said:

Bravepants said:

RetSol said:

RetSol said:

Sea_Shell said:

Bravepants said:

RetSol said:

Sea_Shell said:

I don't have a template as such, i just played around with Excel.

I have the following columns.

Year
Starting fund
Growth in £  (based on an adjustable % in another cell)
Fund plus growth
Estimated Spends - adjustable (linked to an adjustable inflation % each year)
Income ( nil at present and then includes DB/SP when due)
Net Balance
Number of years spends at this rate

Copy the Net balance back to the next line down as the starting fund for year 2, repeat. 
You need to play around with the formulas a bit to get them to do what you need.

It basically tells me that if i had say a starting pot of £500,000, with a 3% growth rate, that would give me growth in £ of £15,000.
If I then said I would spend £20k per year, increasing at 2% inflation, it would show i'd have £495,000 for year 2, with growth in year 2 of £14,850 and spends of £20,400.   Drag the formulas down the columns to see when (if) you run out of money!!    Using the above, shows 25 years of spending, initially, with £400,000 left after 12 years.

£166,200 of growth, to offset £266,400 of spends.  

Hope that helps.

Thank you, Sea_Shell - that's very helpful.  I am going to need to hone my spreadsheet skills, though! 

In this game, spreadsheets become a hobby after a while!

Hobby or Obsession??!!

It's a thin line!!  

Hobby or obession, I feel myself being gripped.... 

Yay, I have just created my first spreadie, formulae and all.  Feeing very chuffed with myself. 

It's quite a good skill to develop and not everyone can take to it....it's effectively computer programming if you think about it. I've been using Excel for years, but I''m not an accountant or financial person, I'm an engineer....you would be very surprised about the uses to which you can put Microsoft Excel! 

Would love to hear more about the uses of Excel (or Google Sheets, in my case) ! 

I'm sure if you started a thread about the uses of Excel, you'd have lots of people offering you expert guidance on the vast array of things it can do.    Sadly I'm not that person.   

I was just chuffed to have recently worked out how to use a figure from on one sheet of a workbook on another sheet!!!

Audaxer

Sea_Shell said:

Audaxer said:

Sea_Shell said:

I don't have a template as such, i just played around with Excel.

I have the following columns.

Year
Starting fund
Growth in £  (based on an adjustable % in another cell)
Fund plus growth
Estimated Spends - adjustable (linked to an adjustable inflation % each year)
Income ( nil at present and then includes DB/SP when due)
Net Balance
Number of years spends at this rate

Copy the Net balance back to the next line down as the starting fund for year 2, repeat. 
You need to play around with the formulas a bit to get them to do what you need.

It basically tells me that if i had say a starting pot of £500,000, with a 3% growth rate, that would give me growth in £ of £15,000.
If I then said I would spend £20k per year, increasing at 2% inflation, it would show i'd have £495,000 for year 2, with growth in year 2 of £14,850 and spends of £20,400.   Drag the formulas down the columns to see when (if) you run out of money!!    Using the above, shows 25 years of spending, initially, with £400,000 left after 12 years.

£166,200 of growth, to offset £266,400 of spends.  

Hope that helps.

Sea_Shell, I have something similar, but just wondering if you also compare that 3% average growth with different sequence of returns? 

If I also take an average 3% growth rate over 30 years, I compare different sequence of returns over that period. If for example I sample a bad sequence of returns in the first decade on one sheet, and a good sequence of returns over that period on another sheet, my portfolio value after 30 years with a bad early sequence of returns will be a lot lower than with a good early sequence of returns. As a result I keep a healthy amount of cash savings, so that I will not need to drawdown from investments during years of negative growth. 

Right, i've punched some numbers are here are various scenarios, that hopefully won't come to pass...Based on day one pot of £560,000, and annual spends starting at £15,000.

Zero Growth and 5% inflation - £330,000 remaining after 12 years.
-5% growth and 5% inflation = £100,000 remaining after 12 years.
-8% growth and 5% inflation = £50,000 remaining after 12 years.
Initial -30% in year one and then Zero growth thereafter with 5% inflation = £160,000 remaining.

As the future is likely to be a combination of all of the above at any given time, then hopefully our numbers stack up.   My spreadsheet isn't yet advanced enough to model changes throughout that 12 year period, i.e. up one year down the next etc. but it at least gives a signpost to what could happen.    Other than that, we're just winging it!!!!

Gosh, hopefully we will have none of these scenarios over the next 12 years. I think most retirees who would require a higher percentage withdrawal rate than the 2.68% I calculated from your example, would probably run out of money after 12 years with any of these average returns combined with the 5% inflation.  A bad sequence of returns in any of the first 3 scenarios would make things even worse.

cfw1994

Sea_Shell said:

Audaxer said:

Sea_Shell said:

I don't have a template as such, i just played around with Excel.

I have the following columns.

Year
Starting fund
Growth in £  (based on an adjustable % in another cell)
Fund plus growth
Estimated Spends - adjustable (linked to an adjustable inflation % each year)
Income ( nil at present and then includes DB/SP when due)
Net Balance
Number of years spends at this rate

Copy the Net balance back to the next line down as the starting fund for year 2, repeat. 
You need to play around with the formulas a bit to get them to do what you need.

It basically tells me that if i had say a starting pot of £500,000, with a 3% growth rate, that would give me growth in £ of £15,000.
If I then said I would spend £20k per year, increasing at 2% inflation, it would show i'd have £495,000 for year 2, with growth in year 2 of £14,850 and spends of £20,400.   Drag the formulas down the columns to see when (if) you run out of money!!    Using the above, shows 25 years of spending, initially, with £400,000 left after 12 years.

£166,200 of growth, to offset £266,400 of spends.  

Hope that helps.

Sea_Shell, I have something similar, but just wondering if you also compare that 3% average growth with different sequence of returns? 

If I also take an average 3% growth rate over 30 years, I compare different sequence of returns over that period. If for example I sample a bad sequence of returns in the first decade on one sheet, and a good sequence of returns over that period on another sheet, my portfolio value after 30 years with a bad early sequence of returns will be a lot lower than with a good early sequence of returns. As a result I keep a healthy amount of cash savings, so that I will not need to drawdown from investments during years of negative growth. 

Right, i've punched some numbers are here are various scenarios, that hopefully won't come to pass...Based on day one pot of £560,000, and annual spends starting at £15,000.

Zero Growth and 5% inflation - £330,000 remaining after 12 years.
-5% growth and 5% inflation = £100,000 remaining after 12 years.
-8% growth and 5% inflation = £50,000 remaining after 12 years.
Initial -30% in year one and then Zero growth thereafter with 5% inflation = £160,000 remaining.

As the future is likely to be a combination of all of the above at any given time, then hopefully our numbers stack up.   My spreadsheet isn't yet advanced enough to model changes throughout that 12 year period, i.e. up one year down the next etc. but it at least gives a signpost to what could happen.    Other than that, we're just winging it!!!!

I would suggest perhaps have a column next to the pot colume with a % number - start at 2 or 3% and copy that down the columns.   
Use *that* number (rather than a fixed number applied to all rows) to multiply each row - then you can edit occasional rows to slide in some nasty negatives, and go back to 'normal' on next row.
The reality (I would suggest!) is that you won't see -5% for ALL your years, just as you won't see +3% throughout - you may have a dip, then go back, and 10 years later, another dip.
In the example below, here is your pot with 3% growth throughout......on the right, 3%, then -10% after 2 years, back to 3%, then -20% in the 7th year, then (hallelujah!) back up to 3% for the next few.....
(not showing any withdrawals here, but that you already have)

Just gives you flexibility to try a few scenarios out.
All entirely theoretical, of course.....& I am pretty sure you have a great handle on your lifestyle+finances 

Stubod

..some good tips. I use a (hopefully) fairly pessimistic plan in my long term spreadsheet. I use the inflation percentage as the key figure and assume any interest on investments / savings are a percentage of that. ie currently plan on interest rates being between 25 and 50% of whatever the inflation rate is, so if inflation is 4.5%, (my default), any investment returns are between 2.25 and 1.15%. 

When both state pensions + a rather "limited" non indexing private pension kick in we should still be having an income above what we normally spend, but this is still a few years off.

Interesting that my lowest ever savings interest I had planned for was 1% and I assumed this would be the worst ever case but it now looks like it will soon be even lower. I am hoping that this will be compensated for by an even lower (than budgeted) inflation rate?

cobson

Spreadsheets are a great tool for getting a grip on your finances and understanding the effect of different rates of growth, inflation etc. but as some posters have pointed out, they hide sequence of return risk.  A tool that incorporates Monte Carlo analysis goes some way to alleviate this e.g. I use the one at www.flexibleretirementplanner.com.  I don't bother with spreadsheets anymore as this tool does everything I need.

Audaxer

cobson said:

Spreadsheets are a great tool for getting a grip on your finances and understanding the effect of different rates of growth, inflation etc. but as some posters have pointed out, they hide sequence of return risk.  

On a spreadsheet you can also vary percentages of gain/loss each year and input some big losses for equity crashes at different times in a 30 year retirement period to compare what effect different sequences of returns have on the same withdrawal rates. So for the same average growth rate of say 3%, and same £15k withdrawal each year increasing with inflation, it will show how the resultant portfolio value after a number of years can differ greatly depending on the sequence of returns. 

cobson

Audaxer said:

On a spreadsheet you can also vary percentages of gain/loss each year and input some big losses for equity crashes at different times in a 30 year retirement period to compare what effect different sequences of returns have on the same withdrawal rates. So for the same average growth rate of say 3%, and same £15k withdrawal each year increasing with inflation, it will show how the resultant portfolio value after a number of years can differ greatly depending on the sequence of returns. 

Yes, but you can only put one set of figures in at a time - put a different set of figures in and the results will be completely different, so it isn't very useful.  Monte Carlo analysis runs thousands of simulations with randomized returns and shows you the stats so it is much more informative.

For spreadsheeters who are feeling brave:
https://support.microsoft.com/en-us/office/introduction-to-monte-carlo-simulation-in-excel-64c0ba99-752a-4fa8-bbd3-4450d8db16f1

Sea_Shell

cobson said:

Spreadsheets are a great tool for getting a grip on your finances and understanding the effect of different rates of growth, inflation etc. but as some posters have pointed out, they hide sequence of return risk.  A tool that incorporates Monte Carlo analysis goes some way to alleviate this e.g. I use the one at www.flexibleretirementplanner.com.  I don't bother with spreadsheets anymore as this tool does everything I need.

Interesting, I had a very quick look.

Can this tool cope with putting a couples details in, with their individual pension dates and or investments, or do have to run each separately.

Or do you just assume all based on first to claim pensions?

cobson

Yes, you can put a couples details in - that is how I use it.  Set the retirement date as the date when the first person retires, and enter any income for the second person until they also retire.  I would ignore the income value on the front page - that is only for very simple situations where income remains fixed, and enter everything on the Additional Inputs screen. 

The Sensitivity Analysis screen is useful, as you can compare a spread of two values such as growth rates and desired income, and see a heat-map of how risky a particular combination of values are.