We’ve gone through what superannuation is, superannuation contribution types, superannuation account types, and how to invest your super.
In this article, we’ll show you how to use Excel formulas for calculating how much to put away to reach your retirement goal, and then move on to a more comprehensive way of determining how much to save inside vs outside super to reach your retirement savings goal in the most effective way.
How much should you be saving in your super
This will depend on how much you want to retire with, and there is an example below on how to calculate how much you need to save to reach your retirement income goal, but until you decide on a retirement income goal, as a default, you might like to consider putting at least 20% of your gross salary towards your retirement savings.
The compulsory employer super contribution is 11.5%, which means you only need to add another 8.5% of your gross salary.
And don’t forget that you will get a tax deduction for this if it goes into super.
Let’s see an example of someone on a salary of $70,000 with a 30% marginal tax rate, or 32% including the Medicare levy.
Your take-home pay on $70,000 is $56,812.
When you salary sacrifice 8.5% ($5,950), your take-home pay is $52,805, which means that even though you sacrificed $5,950 from your pre-tax salary, your take-home pay has only been reduced by $4,006 and the government has chipped in the other $1,944.
And you are now putting 20% of your gross salary into super.
A more accurate way to calculate with Excel
You will need to estimate your retirement income goal, which you get from expense tracking and adjusting for expenses that you won’t have in retirement, such as a mortgage, childcare costs, and work-related costs, and adding costs for recreational activities.
Once you have a retirement income goal, there are 3 parts to calculating how much you need to contribute:
- Calculate the amount of assets you need to build
- Calculate how much you need to put away each year to achieve that goal
- Calculate how much is needed above your employer’s compulsory contribution
1. Calculate the amount of assets you need to build
The way to figure this out would normally be to decide an assumed rate of return on your investments. So, if you were in a 60/40 portfolio, which has historically returned 5% over inflation, you would divide your retirement income needs by that. For example, if you wanted $50,000 per year in retirement, you would calculate $50,000/5% = $1,000,000.
However, due to the volatile nature of stocks, drawing down during periods when stocks are low depletes your portfolio faster, potentially more than it can recover since there is less in there to re-grow when the market rebounds. As a result, William Bengen used historical stock market returns and came up with the 4% rule, which has had a high probability (95%) of lasting 30 years.
So you would use $50,000/4% = $1,250,000
For a more comprehensive figure, you would take the age pension into account because it slows the depletion of your retirement funds significantly, and this is what financial planners do, but that’s beyond what I can fit into this article.
2. Calculate how much you need to put away each year to achieve that goal
There are a couple of Excel functions that can do this in a few keystrokes. Either one will work.
The FV function will calculate the future value based on your current assets and regular savings.
The PMT function will give you the regular savings amount needed based on your current assets and the future value you are aiming for.
The FV formula
The FV formula calculates the future value given the following parameters:
- rate (rate of return)
- nper (number of periods)
- pmt (payment added/subtracted each period)
- pv (present value)
=FV (5%, 30, -12500, -100000)
This example is using a 5% inflation-adjusted return, 30 years until retirement, $12,500 annual contribution, and a $100,000 starting value.
Note that pmt and pv are negative since you are adding rather than paying out.
This will require you to play around with the contribution amount (the third parameter) until you find a future value that matches your goal. The PMT formula (below) rearranges this to include the future value and return the contribution amount.
The PMT formula
The PMT formula calculates the payment value given the following parameters:
- rate (rate of return)
- nper (number of periods)
- pv (present value)
- fv (future value)
=PMT (5%, 30, 100000, -1250000)
This is using a 5% inflation-adjusted return, 30 years until retirement, $100,000 starting value, and a target end value of $1,250,000.
Note that the future value is negative.
3. Calculate how much is needed above your employer’s compulsory contribution
As you can see from the above formula inputs, achieving a retirement funding goal of $1,250,000 in 30 years will require $12,309 added per year.
Importantly, this is your after-tax amount. Since super is taxed at 15% on the way in, you will need to contribute a little more to make up for that:
$12,309 * 100/85 = 14,481 p.a.
So, if your employer is contributing $7,000 per year, you will need to salary sacrifice an additional $7,481 per year to achieve the goal of $1,250,000 in 30 years to draw down $50,000 per year with a high probability of it lasting for 30 years, given you presently have $100,000.
Nearing retirement and this is not feasible — don’t panic just yet
If you are nearing retirement and have left your accumulation of assets too late and this is not feasible, don’t panic just yet.
In Australia, we have a welfare system in place for those who did not save as much as needed – the age pension.
While the age pension alone is a small amount of money to live on (indexed at just 27.7% of ‘Male Total Average Weekly Earnings’ (MTAWE) for singles and 41.76% of MTAWE for couples), the age pension kicks in before you run out of money, so it helps reduce the rate at which you deplete what capital you have, and your capital continues to achieve some growth from investment earnings, which also reduces the rate at which you deplete your capital. This allows you to use your capital in combination with the aged pension for longer.
Additionally, lifetime annuities provide another option, which the government provides excellent advantages when used in conjunction with the aged pension.
And as you get older, you can potentially drawdown from equity in your home. While this is often not ideal, it provides options.
So, while you still need to plan your retirement because the age pension payment is low, don’t panic just yet. Seek advice if you are in this situation so you can get a plan in place sooner than later.
Retiring early — how much to invest inside vs outside super
Some people say they don’t want to save for retirement because they want to enjoy their life now when they’re still healthy and able to. The problem is that this frames the question of spending now and saving for the future as mutually exclusive. There’s no rule that you can’t do both.
Unless you have a reason to think that you are likely to die by 60, you are probably going to be alive for decades beyond that. Why not take the massive tax deductions (i.e., free money) to supercharge that portion of your retirement savings, and for anything needed before that, invest it outside super? By not taking advantage of super for the portion of your assets you will need beyond 60, you will need to work many more years to build the same nest egg.
In deciding how much to put inside vs outside super, determine how much you want in each timeframe:
- Anything you need in the next few years, use a high-interest savings account, fixed-term deposit, or an offset account
- Anything you need after, say, 5 years and before preservation age, invest in low-cost index funds outside super
- Anything you don’t need until after preservation age, use index options in super
The most tax-efficient way to split the last 2 steps is to start with super to get the tax benefits for the long term first, and once the amount in super is taken care of, you can work on bringing forward your retirement date through outside super investments. Here’s a great write-up on this.
Here is an example.
Let’s say you want to live off $50,000 p.a. and would like to retire at age 50 in 20 years.
That means:
- Outside super – you will need to draw down $50,000 before-tax during those last 10 years. To last 10 years, you will need $500,000.
- Inside super – you will need $1.25m inside super by age 60 (going by the 4% rule explained earlier), which means $825,000 in super by 50, which grows to 1.25m by 60 with no further contributions based on a 5% real return.
In the image below, you can see that this is met by doing the following:
Stage 1 — age 30 to 43
The maximum super contribution is made ($27,500 cap less 15% tax = $23,375).
The remaining surplus cash flow ($10,000) is invested outside super.
Stage 2 — age 43 to 50
Having only compulsory employer contributions added to super results in achieving the $1.25m goal in super by 60 without additional personal contributions.
Since no additional super contributions are needed to reach the super goal, capital is redirected outside super, with those amounts reaching the $500,000 outside-super goals by age 50.
Stage 3 — age 50 to 60
Outside-super investments are drawn down while the inside-super investment continues to grow.
Stage 4 — age 60 onwards
Super is available to be used.
A spreadsheet like this can be used once you figure out how much you want to retire on and at what age. You can then adjust how much you invest inside and outside super to see how much earlier or later you can retire based on your own priorities of enjoying your money now vs savings to retire earlier.
Some important points:
- You will need to balance this with your other goals, the big ones being home ownership and paying down your mortgage.
- Have some additional padding outside super to account for the possibility of poor market returns.
- Remember to always account for:
- Inflation — by using inflation-adjusted return estimates.
- Tax — based on your marginal tax rate for the outside super portion.
- Investment fees — reduce your return estimates by investment fees.
Here is a replication of the above spreadsheet created by OZ-FI:
https://docs.google.com/spreadsheets/d/1aoGTtLcQxwmGlNXXWFjisOyZ0gACh9MQmls6bYhaFi4
Final thoughts
Hopefully this has given you an understanding of how to do some Excel modelling to determine how much to save inside vs outside super to optimise your retirement savings.
In the following article, we will go through contribution types and account types, before moving onto consolidating your super accounts, selecting suitable investment options, and going through several superannuation strategies.