We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
It's time to start digging up those Squirrelled Nuts!!!!
Options
Comments
-
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.0 -
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.
Yes, I'm always punching in alternate figures, including minus growth and 5% inflation.
I can't remember what my "doomsday" predictions were, where we run out of money before DHs DB/SP kick in, but if I remember, I'll look them up when I'm on the laptop in the morning.How's it going, AKA, Nutwatch? - 12 month spends to date = 2.60% of current retirement "pot" (as at end May 2025)1 -
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!If you want to be rich, live like you're poor; if you want to be poor, live like you're rich.0 -
Stubod said:..obsession for me, been at it for years..and yes low interest rates certainly help with the "might as well spend it now" attitude! (Hope inflation remains low to compensate)
1 -
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 finished off the spreadsheet this evening and it has changed my life already! It's enabling me to see a possible path from here to SPA (67) so I am feeling somewhat reassured.
Would love to hear more about the uses of Excel (or Google Sheets, in my case) !3 -
RetSol said:I'm a lawyer by training so spreadsheets are definitely outside my normal remit. I am good at learning how to use software though which I think stems from my also being something of a linguist. It's all about syntax.
I finished off the spreadsheet this evening and it has changed my life already! It's enabling me to see a possible path from here to SPA (67) so I am feeling somewhat reassured.
Would love to hear more about the uses of Excel (or Google Sheets, in my case) !
I've worked in Excel for a couple of decades now and still feel that sense of excitement when I learn a new function.
Excel is incredibly flexible which is great, it can do almost anything you can think of being possible with numbers. Certainly sufficient for modelling stock market returns and retirement expenditure. If flexibility is its undoing though and it likely that the more complex your workbooks become the more errors creep in. That will absolutely be the case if more than one person is working on the same book.
N.B. If you want to invest a some time to see just how powerful excel can be at modelling financial plan's Lars Kroijer's series on building your own model is worth the time. Take it slow if you're new to excel though, it moves pretty quickly.
https://www.youtube.com/watch?edufilter=NULL&v=1LUIQa5hgMg
1 -
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!1 -
Aos 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!
If you will permit me a metaphor, the way I see it is that a spreadsheet is like an OS map. It is based, necessarily, on historical data. Meaning that you may find that the path indicated on it does not, in reality, now exist, necessitating a revision of your route.
The map is useful for planning purposes but, as the military saying goes, one's plans generally last less than 5 minutes' engagement with the enemy...
0 -
Aos 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!1 -
RetSol said:Aos 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!
If you will permit me a metaphor, the way I see it is that a spreadsheet is like an OS map. It is based, necessarily, on historical data. Meaning that you may find that the path indicated on it does not, in reality, now exist, necessitating a revision of your route.
The map is useful for planning purposes but, as the military saying goes, one's plans generally last less than 5 minutes' engagement with the enemy....
Not deeply into excel, but I did use Supercalc V to score a car treasure hunt back in the late 80s on a big laptop with a tiny grey scale screen
The OS map really is “cast in stone”. Sure, the online ones today could be updated, but rarely are.
A spreadsheet is a point in time view, which I suspect is what you mean....but by popping in a variable to multiply the numbers, you can evolve the plan as frequently as you want.
I used to check/track my pension numbers on a quarterly basis, but earlier this year it was almost daily. I think obsession probably is the right word these days.....In a spreadsheet with a row per year, you can model a pot of money (column of numbers!) to increase by 3%. Pop that in an adjacent column. You could then model a “bad sequence of returns”, by perhaps changing a couple of those 3% numbers to (for example) -10% and -20% in a couple of the early years to see how that impacts things.In military terms.....follow the 7 Ps....Plan for tomorrow, enjoy today!1
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.9K Banking & Borrowing
- 253.1K Reduce Debt & Boost Income
- 453.5K Spending & Discounts
- 243.9K Work, Benefits & Business
- 598.8K Mortgages, Homes & Bills
- 176.9K Life & Family
- 257.2K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards