Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2003
    Location
    Central Coast, NSW
    Posts
    614

    Default Spreadsheet to workout how long retirement money will last

    Hi. I'm looking for a spreadsheet which will help me do some retirement planning. What I want is to put in our ages, a hypothetical drop dead date, the money we have, the likely invstment earnings, a certain amount to be spent each year, inflation etc and have it break down our financial position year by year.
    I've done a bit of Googling and I find a few American ones, and various versions which concentrate on the question how much do we need to retire - but what I'm looking for is a bit differnt- its like a year by year breakdown of the future.
    The ideal would be for each year to have : the starting point in terms of total investments, the money planned to be spent to give a decent lifestyle plus a bit of contingency, the money earnt through investment income, the whole lot adjusted by inflation, and the ending point in terms of total investments.
    Anyone have one ?

    Cheers
    Arron
    Apologies for unnoticed autocomplete errors.

  2. #2
    Join Date
    Jul 2006
    Location
    Port Huon
    Posts
    373

    Default

    Speak to your investment adviser, they should be able to answer your question.

  3. #3
    Join Date
    Nov 2010
    Location
    Gippsland Victoria
    Posts
    25

    Default This is quite good - ASIC calculator

    From ASIC

    https://www.moneysmart.gov.au/tools-...rement-planner

    I have the older version that they let you download as a spreadsheet -send me a PM if you would like a copy.

    Bill

  4. #4
    Join Date
    Apr 2011
    Location
    Dandenong, Vic
    Posts
    0

    Default

    Not very useful.
    Doesn't let you enter any investment income when you have retired.
    Assumes you stop earning at retirement time.

    Well thats for me anyway.

  5. #5
    Join Date
    Jun 2005
    Location
    Helensburgh
    Posts
    608

    Default

    From my recent experience they only extrapolate the income/number of years etc using the capital and earnings are a bonus. The minimum income to have a reasonable life is reckoned to be 32K which might sound alright until the expenses are added up and suddenly it isn't so good. The figures are projected with a male life expectancy of about 83 years old but I can't recall the female age except it is higher. I guess they don't include earnings as there is no way to come up with a figure due to the various investment vehicles you can use. I have found it is not a very nice looking at dwindling financial resources when all my life I have tried to accumulate and had a job with money coming in every week. The reward is waking up in the morning and not having work commitments, a Saturday morning every day.
    CHRIS

  6. #6
    Join Date
    Nov 2010
    Location
    Gippsland Victoria
    Posts
    25

    Default spreadsheet

    Okay, agreed, if you've got other investments outside of super that page wont work for you at all. It wasnt designed for that.

    Suggest you do your own spreadsheet - or get somebody who knows how to drive excel to do it for you.

    Maybe worth talking to financial planners - I found one that charges a flat hourly fee to give me advice - I prefer this to the alternative approach of "Give me $X,000 and I will give you a plan"

    Bill

  7. #7
    Join Date
    Feb 2003
    Location
    back in Alberta for a while
    Age
    69
    Posts
    1,133

    Default

    Quote Originally Posted by Arron View Post
    Hi. I'm looking for a spreadsheet which will help me do some retirement planning. What I want is to put in our ages, a hypothetical drop dead date, the money we have, the likely invstment earnings, a certain amount to be spent each year, inflation etc and have it break down our financial position year by year.
    I've done a bit of Googling and I find a few American ones, and various versions which concentrate on the question how much do we need to retire - but what I'm looking for is a bit differnt- its like a year by year breakdown of the future.
    The ideal would be for each year to have : the starting point in terms of total investments, the money planned to be spent to give a decent lifestyle plus a bit of contingency, the money earnt through investment income, the whole lot adjusted by inflation, and the ending point in terms of total investments.
    Anyone have one ?

    Cheers
    Arron
    hi Arron

    what you are looking for is a spreadsheet set-up to combine an annuity with an income stream outside the annuity
    all pretty easy to do inside any spreadsheet program.

    However, if you don't understand what the formulas are doing inside the spreadsheet then the results you are looking at can be very misleading, and getting the numbers severely wrong is all too easy.
    Plus there are "rules" around how much of your super you must "spend" each year.


    If you go down the calculate it yourself path, rather than using a hypothetical drop dead date, you could use the ABS's life tables 3302.0.55.001 - Life Tables, States, Territories and Australia, 2010–2012 and calculate the probability that you will live to a certain age.

    In general terms, life tables are expressed as the probability that a person of a particular age and gender will be dead in 12 months time. The tables are averages and are not adjusted for smoking, alcohol consumption or other known high risk factors
    So if you're in NSW and have just turned 56, there's a 1 in 200 chance you will die before turning 57
    If you've just turned 64, it's a 1 in 100 chance you will die before turning 65
    at 72 it's a 1 in 50 chance, and so on.

    The flip side of those depressing stats is that the older you get, the longer you will last.
    at 56, you can "expect" to celebrate your 82nd birthday, but not your 83rd,
    but if you're already 64 you should expect to celebrate your 84th birthday and
    if you're 72, birthdays 85 and probably 86 should come around.
    and if you make it to 85, you should also make it to 91.
    regards from Alberta, Canada

    ian

  8. #8
    Join Date
    May 2003
    Location
    Central Coast, NSW
    Posts
    614

    Default

    Ok, thanks for the replies guys.
    Firstly, to those who said 'talk to your financial planner' - well nothing wrong with that idea but regardless of any involvement I might have with a planner I would still expect to understand and work this sort of stuff out for myself.

    Anyway, failing to find what I want I have built a spreadsheet of my own - which I will use and further develop over time. The way it works is:
    You put in the following inputs :
    Starting year
    Starting age
    Life expectancy (from acturial tables)
    Total investments
    Projected inflation (average for last 70 years is 5.3 %, so I use 5%)
    Projected return on investments (i have 15 yrs of data from our own investment management, so i project from that)
    Total projected spend per year

    The speadsheet has one row for each year from star point to last year of life expectancy of longest surviving partner. One year is probably an acceptable granularity, not sure at this stage.

    The spreadsheet uses the input data to seed the following columns:
    Year
    Participants ages
    Total investments at start of year
    Per year spend (adjusted for inflation)
    Projected investment income
    Total investments at end of year

    There are also the following columns which cant be worked out from the seed values but are entered directly into the spreadsheet:
    Per year spend tapering - where the user can increase or decrease the pys by a percentage, such as to accommodate the fact that most people spend less as they get older
    Other income - either earnt income or windfalls
    Reverse mortgages - as we have no descendents, we expect to die with last cheque bouncing
    Eoy adjustment - where you can add a figure as each year passes to align the projections with reality.

    Of course its based on a lot of assumptions ie:
    That we wont ever be receiving a Gvt pension - which looks to be the case
    Also, we have a Smsf, so no need to factor in Fund pensions etc.

    And there is much more work to do:
    I havent factored in purchase of an annuity or similar strategies - just dont know enough about them as yet.
    Id also like to break investment performance down into asset class, and better accommodate taxation.
    I'd like to show investment properties as both expense and income, as I really want to understand the impact of keeping them at all and how it is affected by different strategies of management.

    Anyway, just doing the exercise has been a revelation. It is astounding to see how one's financial position changes when the seed factors are changed just a little. Like what impact at end of life does earning just a small amount of money in the early years of retirement have. Or what impact reducing pys by a small amount at age 75 and 80 has. And the big one is the difference that just a 1 or 2 % difference in investment returns has when inflation is held steady. Another astounding difference is between self managed and professional investments (due to compounding effects of fees paid).

    There is scary stuff too. Like what impact does a few years of 1970's style inflation have. And what impact does a major illness have (assuming you seek some sort of treatment at own cost).

    Knowledge is power.

    The upshot is that I'm very interested in keeping this going. Anyone who would like a copy or is interested in critiquing or co-developing let me know. Any input is welcome.

    Cheers
    Arron
    Apologies for unnoticed autocomplete errors.

  9. #9
    Join Date
    Nov 2010
    Location
    Gippsland Victoria
    Posts
    25

    Default Spreadsheets

    Aaron,

    A few years ago I went through the same exercise and developed my own spreadsheets.

    Was quite surprised at the big difference that relatively small changes in returns and inflation made to the various scenarios.

    I modelled a range of inflation rates and returns.

    Keen to see how somebody else has gone about it.

    Have sent you a PM.

    Bill

  10. #10
    Join Date
    Jun 2005
    Location
    Helensburgh
    Posts
    608

    Default

    From what I understand the compulsory minimum spend from within a SMSF is only applicable if it is not in pension mode. I have refrained for putting our SMSF in pension mode in case I decide to return to work either full or part time if my circumstances change. I also have an aversion to so called financial advisors from previous family experience with my parents investments.
    CHRIS

  11. #11
    Join Date
    May 2007
    Location
    Gold Coast
    Age
    71
    Posts
    456

    Default

    There are some calculators on the AMP website that may do some of what you are after. Their retirement simulator gives a graphic visualization of drawdown of super together with other investments, including the available contribution from a government pension if eligible.

    It probably doesn't give enough details to go into an indepth analysis, but the visualization is pretty slick.

  12. #12
    Join Date
    Feb 2003
    Location
    back in Alberta for a while
    Age
    69
    Posts
    1,133

    Default

    Quote Originally Posted by Arron View Post
    Ok, thanks for the replies guys.
    Firstly, to those who said 'talk to your financial planner' - well nothing wrong with that idea but regardless of any involvement I might have with a planner I would still expect to understand and work this sort of stuff out for myself.

    Anyway, failing to find what I want I have built a spreadsheet of my own - which I will use and further develop over time. The way it works is:
    You put in the following inputs :
    Starting year
    Starting age
    Life expectancy (from acturial tables)
    Total investments
    Projected inflation (average for last 70 years is 5.3 %, so I use 5%)
    Projected return on investments (i have 15 yrs of data from our own investment management, so i project from that)
    Total projected spend per year

    The speadsheet has one row for each year from star point to last year of life expectancy of longest surviving partner. One year is probably an acceptable granularity, not sure at this stage.

    The spreadsheet uses the input data to seed the following columns:
    Year
    Participants ages
    Total investments at start of year
    Per year spend (adjusted for inflation)
    Projected investment income
    Total investments at end of year

    There are also the following columns which cant be worked out from the seed values but are entered directly into the spreadsheet:
    Per year spend tapering - where the user can increase or decrease the pys by a percentage, such as to accommodate the fact that most people spend less as they get older
    Other income - either earnt income or windfalls
    Reverse mortgages - as we have no descendents, we expect to die with last cheque bouncing
    Eoy adjustment - where you can add a figure as each year passes to align the projections with reality.

    Of course its based on a lot of assumptions ie:
    That we wont ever be receiving a Gvt pension - which looks to be the case
    Also, we have a Smsf, so no need to factor in Fund pensions etc.

    And there is much more work to do:
    I havent factored in purchase of an annuity or similar strategies - just dont know enough about them as yet.
    Id also like to break investment performance down into asset class, and better accommodate taxation.
    I'd like to show investment properties as both expense and income, as I really want to understand the impact of keeping them at all and how it is affected by different strategies of management.

    Anyway, just doing the exercise has been a revelation. It is astounding to see how one's financial position changes when the seed factors are changed just a little. Like what impact at end of life does earning just a small amount of money in the early years of retirement have. Or what impact reducing pys by a small amount at age 75 and 80 has. And the big one is the difference that just a 1 or 2 % difference in investment returns has when inflation is held steady. Another astounding difference is between self managed and professional investments (due to compounding effects of fees paid).

    There is scary stuff too. Like what impact does a few years of 1970's style inflation have. And what impact does a major illness have (assuming you seek some sort of treatment at own cost).

    Knowledge is power.

    The upshot is that I'm very interested in keeping this going. Anyone who would like a copy or is interested in critiquing or co-developing let me know. Any input is welcome.

    Cheers
    Arron
    Hi Arron

    some other sources of data.
    The Reserve Bank has a lot of historical data on their web site Reserve Bank of Australia - Home Page you may find the RBA's inflation adjusted 10 year bond rate a better proxy for the future than the average for inflation over the past 70 years. Also I believe the RBA is about to issue 20 and 30 year bonds which in essence will give another measure of future inflation.

    Also, Federal Treasury has projections for economic growth and inflation into the future.

    Rather than dying with the last cheque bouncing -- someone has to pay for your funeral -- you could always donate the balance of your estate to a worthwhile cause like a scholarship for a suitably "worthy" student to attend the year long furniture course at the Sturt School in Mittagong or some other cause.

    Re the age pension -- an assumption that you won't access it (and the associated fringe benefits) is possibly too conservative


    Life expectancy is an average, not an absolute
    If you're currently 60, life expectancy might be 23 years, but there's a 25% chance you will live to 90.


    Lastly, as I understand it -- the average Australian incurs around 50% of their total life-time health expenditure in their last year of life.
    regards from Alberta, Canada

    ian

  13. #13
    Join Date
    Nov 2006
    Location
    Bendigo Victoria
    Age
    80
    Posts
    4,565

    Default

    Quote Originally Posted by ian View Post
    Lastly, as I understand it -- the average Australian incurs around 50% of their total life-time health expenditure in their last year of life.
    So, if we all decide to die a year earlier we can save ourselves, and the country, a whole heap of money.

  14. #14
    Join Date
    May 2007
    Location
    North of the coathanger, Sydney
    Age
    69
    Posts
    0

    Default

    the difficulty with any of this is the extra costs of retirement

    after you retire you are at home (probably) so home costs go up ie you use more electricity and more water etc
    and that is not including the sudden death of all our whitegoods in the past two years

    also there are the unexpected cost of living increases ... since I retired electricity and petrol have both risen well above the inflation rate
    and I won't mention the sudden need for vital necessities such as better tools and more wood etc
    regards
    Nick
    veni, vidi,
    tornavi
    Without wood it's just ...

  15. #15
    Join Date
    Jun 2005
    Location
    Helensburgh
    Posts
    608

    Default

    Nick, I have the last one covered. You should see my tool and workshop expenditure list in the last two months, it is more than I have spent in the last ten years while working. There is no way my kids are inheriting anything.I started looking at camera lenses last night, there is another way to spend lots of money on.
    CHRIS

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •