We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
It's time to start digging up those Squirrelled Nuts!!!!
Comments
-
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.
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.
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!!!!How's it going, AKA, Nutwatch? - 12 month spends to date = 2.98% of current retirement "pot" (as at end April 2025)2 -
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.
Hobby or Obsession??!!
It's a thin line!!
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!
I was just chuffed to have recently worked out how to use a figure from on one sheet of a workbook on another sheet!!!How's it going, AKA, Nutwatch? - 12 month spends to date = 2.98% of current retirement "pot" (as at end April 2025)0 -
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.
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.
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!!!!
0 -
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.
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.
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!!!!
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+financesPlan for tomorrow, enjoy today!2 -
..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?.."It's everybody's fault but mine...."0
-
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.1
-
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.1
-
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.
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
0 -
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?How's it going, AKA, Nutwatch? - 12 month spends to date = 2.98% of current retirement "pot" (as at end April 2025)0 -
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.0
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.2K Banking & Borrowing
- 252.8K Reduce Debt & Boost Income
- 453.1K Spending & Discounts
- 243.1K Work, Benefits & Business
- 597.5K Mortgages, Homes & Bills
- 176.5K Life & Family
- 256.1K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards