Spreadsheet to workout how long retirement money will last

[Arron]

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

[snowyskiesau]

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

[steamingbill]

From ASIC

https://www.moneysmart.gov.au/tools-and-resources/calculators-and-tools/retirement-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

[_fly_]

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.

[Chris Parks]

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.:2tsup:

[steamingbill]

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

[ian]

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
Arronhi 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 (http://www.abs.gov.au/ausstats/[email protected]/mf/3302.0.55.001) 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.

[Arron]

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

[steamingbill]

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

[Chris Parks]

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.

[Fuzzie]

There are some calculators on the AMP website (https://www.amp.com.au/wps/portal/au/AMPAUGeneral3C?vigurl=%2Fvgn-ext-templating%2Fv%2Findex.jsp%3Fvgnextoid%3D4942ae205f711210VgnVCM10000081c0a8c0RCRD) 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.

[ian]

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
ArronHi 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 (http://www.rba.gov.au) 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.

[Big Shed]

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.:U

[Sawdust Maker]

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

[Chris Parks]

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:o.I started looking at camera lenses last night, there is another way to spend lots of money on.

[AlexS]

Good article in todays SMH on the effect of fees on your super.

[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 (http://www.rba.gov.au) 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.

Yes, I am really not sure about how to model life expectancy. I understand that its a normal curve, and that if 83 is your lot by the best guess of the actuaries then you might die at any point along the bell curve. So should you model to 83, and assume that if you get lucky then the Gvt can support you from then on (assuming your wants are pretty modest in your late 80's anyway). Or should you draw the line at 25% chance and model to 90. Or should you accept that there is a 1% chance that you will live to 100 and therefore model to 100.

Re the age pension. Well that's the way I've always seen it but I intend to see a Financial/retirement planner or two in the new year and perhaps they can show me otherwise. I have no issues at all with getting a bit of government support, even if it requires manufacturing circumstances a bit to do so.

all interesting
cheers
Arron

[Chris Parks]

It is a gamble and nothing else describes it. My father was involved in superannuation and he always said tell me when you will die and I will tell you how much money you need.

[ian]

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

after you retire you are at home so home costs go up ie you use more electricity and more water etc
snip
and i won't mention the sudden need for vital necessities such as better tools and more wood etc :rolleyes: :rolleyes: :rolleyes:

[steamingbill]

I agree with Sawdustmaker and Chris.

I reckon everybody should plan on spending a bit more on toys and activities during early retirement and spend relatively less (taking inflation into account) in "their deep old age" - I got that phrase from a financial planner.

Lets face it, you are highly likely to spend more time reading books and watching videos when you are 90 than when you are a sprightly 60yo and still eager to make a mess in the shed.

Bill

[Sawdust Maker]

... I started looking at camera lenses last night, there is another way to spend lots of money on.

I commented last night that I was looking at another lens - "what do you need ANOTHER lens for" on that tone of voice! :((