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
1125126128130131437

Comments

  • Audaxer
    Audaxer Posts: 3,547 Forumite
    Eighth Anniversary 1,000 Posts Name Dropper
    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
    Sea_Shell Posts: 10,022 Forumite
    Tenth Anniversary 1,000 Posts Photogenic Name Dropper
    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.
    How's it going, AKA, Nutwatch? - 12 month spends to date = 2.60% of current retirement "pot" (as at end May 2025)
  • Bravepants
    Bravepants Posts: 1,640 Forumite
    Part of the Furniture 1,000 Posts Name Dropper Photogenic
    RetSol said:
    RetSol said:
    Sea_Shell 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!  :)
    If you want to be rich, live like you're poor; if you want to be poor, live like you're rich.
  • coyrls
    coyrls Posts: 2,508 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    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
    RetSol Posts: 553 Forumite
    Fifth Anniversary 500 Posts Photogenic Name Dropper
    RetSol said:
    RetSol said:
    Sea_Shell 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
    Anonymous101 Posts: 1,869 Forumite
    Part of the Furniture 1,000 Posts Name Dropper Combo Breaker
    edited 21 July 2020 at 9:18PM
    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
    Aos Posts: 4 Newbie
    First Post
    edited 21 July 2020 at 9:28PM
    RetSol said:
    RetSol said:
    Sea_Shell 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
    RetSol Posts: 553 Forumite
    Fifth Anniversary 500 Posts Photogenic Name Dropper
    Aos said:
    RetSol said:
    RetSol said:
    Sea_Shell 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
    Audaxer Posts: 3,547 Forumite
    Eighth Anniversary 1,000 Posts Name Dropper
    Aos said:
    RetSol said:
    RetSol said:
    Sea_Shell 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
    cfw1994 Posts: 2,127 Forumite
    Part of the Furniture 1,000 Posts Hung up my suit! Name Dropper
    RetSol said:
    Aos said:
    RetSol said:
    RetSol said:
    Sea_Shell 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  :D

    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!
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

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

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.