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

Audaxer

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. 

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. 

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.

Bravepants

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! 

coyrls

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)

Although low interest rates and high inflation would be an even greater justification for a "might as well spend it now" attitude.

RetSol

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! 

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

Anonymous101

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

Excel / formulae are all about syntax too when you boil it down.
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

Aos

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! 

Combine Excel with Visual Basic and you are really cooking with gas.

RetSol

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.

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! 

Combine Excel with Visual Basic and you are really cooking with gas.

Crikey, not sure that I need to go beyond Google Sheets, personally.
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... 

Audaxer

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.

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! 

Combine Excel with Visual Basic and you are really cooking with gas.

I agree as VBA (Visual Basic Application) is great for programming in Excel as a means to create more automated applications using macros.  

cfw1994

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.

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! 

Combine Excel with Visual Basic and you are really cooking with gas.

Crikey, not sure that I need to go beyond Google Sheets, personally.
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 so sure I agree with the OS map analogy.  Speaking as someone who has helped facilitate over 40 DofE expeditions and also spent over 30 years in IT .  
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....