Everyone wants to be a millionaire. Unfortunately, unless you end up on a gameshow, it’s not an easy thing to do overnight. Believe it or not, the system of superannuation in Australia is effectively forcing many people (including you perhaps!) to be millionaires by the time they retire.
The basic concept of superannuation is quite straightforward. The government forces people to save a fixed percentage (in Australia this is around 9\%) of their salary, and keep it in a special account that you can't touch until you retire. You can do some quick research on what superannuation is all about, and why it is important.
In this investigation, we're going to see how this works, by making a spreadsheet to calculate your total superannuation savings over your lifetime.
Step 1: choose a job
First let’s get a reasonable estimate of how much you’re going to earn in your future career. If you don’t have a real career direction, just pick whatever seems nice. Find a job advertisement, and take the listed salary as your future salary.
Step 2: choose a superannuation fund
The "special savings account" where you keep your superannuation money is called a "superannuation fund" (or "super fund" for short). There are many different superannuation funds, and you can choose which one you want to keep your money in. Do some research on the internet on how to choose a super fund, and once you've chosen one, see if you can find its average rate of return over the last several years. This is the percentage interest that your money will gain while it is in the super fund.
Generally a higher interest is better, but the general rule of "high risk, high return" applies, so the funds with the highest interest rates may also run a risk of losing money by making risky investments. It's up to you to decide how you want to balance risk and return when choosing your super fund.
Step 3: making your spreadsheet layout
For this investigation, we will be using a spreadsheet program. There are many free programs available that let you work with spreadsheets - ask your teacher if you need help finding one.
Your screen should look something like this:
To start off, make a small table in the top left corner like the one below, and write your salary, the percentage of salary going to super (currently 9\%), and the average return for the super fund you have chosen. It should look something like this:
Next, we're going to make a list of each of the years that you are working for. For this exercise, we're going to assume you'll be working from the age of 25 (after you finish university or other training) to the age of 65, which is a total of 40 years. Now, we'll make a list of year numbers, starting from 1 and going to 40. Once you’ve got to Year 3 or 4, you’re probably feeling like you don’t really want to keep writing these values all the way down to 40. The good news is that spreadsheets can do that for you! Just click and drag to select all the cells that you’ve just written in, then look for the “magic blue dot” in the bottom right.
Click and drag this dot downwards. You’ll find that the numbers will magically fill up, following the pattern of the first two. Continue dragging the numbers to fill up the cells all the way down to 40. Next, make a column next to the Years column, called "Money ($)".
Step 4: the first year
Next, we’re going to work on the formulas that will make our spreadsheet work. We'll start by figuring out how much goes into your super fund each year.
First, we'll make a word formula:
Annual Contribution = Salary * Percentage Of Salary Going to Super / 100
Using the cell addresses, we can see that Salary is written in A2, and the Percentage of Salary Going to Super is in B2. If we substitute this address for the words, we have a working spreadsheet formula:
Annual Contribution = A2 * B2/100
In the first year, there is no amount from the previous year and no interest, so this is all you need for the formula. Click on cell B5 and then type "=A2 * B2/100" (without the quotation marks).
When you press enter, the computer will automatically perform the calculation for you. As a bonus, if you later decide to change your mind about what salary you put in A2, or the percentage of salary going to super in B2, the computer will automatically update your calculation.
Step 5: the second year
Now let's have a look at the second year. For this year, the formula is a bit more complicated, because we have to consider the amount of money left over from the previous year, and the interest which this money will get. Writing this as a word formula:
Super amount = Last Year's Amount + Interest + Annual Contribution
For Year 2, Last Year's amount is the amount from Year 1, which is in cell B5:
Last Year's Amount = B5
The interest is calculated as:
Interest = Last Year's Amount * Interest Rate / 100
Since Last Year's Amount is in B5, and the interest rate is in C2, this formula becomes:
Interest = B5 * C2/ 100
Putting all the pieces together, the formula for Year 2:
Super amount = Last Year's Amount + Interest + Annual Contribution
Becomes:
Year 2 amount = B5 + B5*C2/100 + A2 * B2/100
Type this formula into the cell for Year 2. Don't forget to start with an equals sign, so that the computer knows that you're asking for a calculation.
Step 6: all the rest
That seems like a lot of work for just two years. If it takes this long, isn't it quicker to just do it all by hand? Fear not, because the magic blue dot will save us again! Just select the cell for Year 2, and drag it down to fill up all the other cells. Perfect! Um...sort of...
Ok, so maybe we just broke the spreadsheet. Don't worry though, we can fix it!
Step 7: how to use dollar signs to fix broken spreadsheets
If we have a look at the formulas for Year 2 and Year 3, we can see the problem (just click on a cell to look at it's formula in the Fx box, which is above the spreadsheet). The formulas are:
Year 2 Amount = B5 + B5*C2/100 + A2 * B2/100
Year 3 Amount = B6 + B6*C3/100 + A3 * B3/100
The computer has helpfully decided to increase the number of each cell by one. For B5, this is a good thing, since "Last Year's Amount" is different for each cell. In Year 2, Last Year's Amount is in B5, but for Year 3, Last Year's Amount is in B6, so this works perfectly. However, for the other two cell addresses, changing them causes problem. This is because the salary is always in A2, the percentage going to super is always in B2, and the interest rate is always in C2, and these never change.
To tell the computer that we don't want it to change those numbers, we put a dollar sign in front of them. So, for the Year 2 formula, we change it to this:
Year 2 Amount = B5 + B5*C$2/100 + A$2 * B$2/100
Now if you drag this updated formula down with the magic blue dot, you'll find that everything works perfectly:
A lot easier than doing all 40 years by hand! You can even change the values that you've given, to see what happens if you earned more or less, or had a superannuation fund with a different interest rate.
Step 8: actually you won't be that rich (sorry)
Inflation is how much the price of goods increases each year. Thinking about it another way, it measures how much money loses value every year. In Australia, the average rate of inflation is around 3\%, which means that all your money is worth about 3\% less every year.
To update our spreadsheet to reflect the effect of inflation, we need to reduce our amount of money by 3\% each year. When we reduce a quantity by a percentage we subtract it from 100\% and multiply, so in this case:
Inflation adjusted money = Money * 97 / 100
To make this work, put brackets around your entire formula for Year 2, and then multiply by \frac{97}{100}:
Year 2 amount = (B5 + B5*C$2/100 + A$2 * B$2/100) * 97/100
Then extend this formula down to Year 40, using the magic blue dot, and see how rich you'll actually be.
Step 9: actually you will be richer than that
Well that was disappointing. From millionaire to...non-millionaire. But wait, the government will save you! The government is making some changes to superannuation. Why? Well, simply because they want you to be a millionaire when you retire. Elderly people with more savings need less financial help from the government when they get older, which will help prevent Australia from going bankrupt from the ageing population.
There are two key changes coming to superannuation. The first is the percentage of your salary which will go to super - by 2025, it will be 12\%. The second is the age that you retire - anyone who is in school now will be retiring at 70, rather than 65.
See how these changes will affect your total retirement savings. Update your spreadsheet by changing the "percentage of salary going to super" to 12\%. Next, add an extra 5 years to the end of your working life, so that you'll be working for a total of 45 years. How does your retirement savings look now? Maybe you will be a millionaire after all!
Extension: using advanced spreadsheet features
As well as being able to perform calculations for you, spreadsheets have a whole lot of more complex features available for those who know how to use them, in the form of functions which can be put in the formula box. For a simple example, try writing =RAND(1,100) in an empty cell, and the spreadsheet will generate you a random number between 1 and 100. There are hundreds of these functions available, and figuring out how to use them is just a quick online search away.
For example, it's a bit weird seeing dollar values like \$176.333333333. In everyday life, we round things to the nearest cent. See if you can search for the correct function to make the spreadsheet round your values to the nearest 2 decimal places, and make it work on your spreadsheet.
See if you can figure out any other advanced functions which could make your spreadsheet better. If you need a list of all the functions that exist, just look it up!
Now your turn: why credit card debts are a bad idea
So far we've seen what the power of compound interest can do to your savings, and how even a little bit of savings over a long time can make you very rich. Now, we'll see what that same power can do to your debts if you let them build up. We're going to imagine you borrow \$5000 on your credit card to pay for something shiny you saw in a shop window, and you wait for 5 years before paying it off. This time we won't walk you through how to set up the spreadsheet, it's up to you to figure it out!
Find a real credit card and look at its interest rate (ignore any interest free period). Make yourself a spreadsheet which figures out how this \$5000 will build up over time (say, five years), and see how much you'll end up paying. You'll need to figure out the layout of your spreadsheet, the word formulas of the equations you'll be using, and convert these to cell formulas to put in your spreadsheet. If you get stuck, have a look at the previous example to remind yourself how it all works.
Extension: a more realistic example of debt repayment
Most of us don't just take out \$5000 loans and let them sit there for five years. It is much more common for people to try to pay back the loan, but not have enough money to do it all at once. For example, a person with a \$5000 loan might pay back \$200 per month to try to pay off the loan. However, even as they are paying, interest is accruing, and the total amount they pay ends up being more than the original \$5000.
Break down your spreadsheet into months, and try to incorporate a monthly payment into your calculations, with the interest being calculated each month (it's not easy!). See if you can figure out the total amount that someone ends up paying for their \$5000 loan.
Many young people in Australia get caught in this kind of "debt spiral", struggling to keep up with debts that continually grow with interest. In 2019 credit card debt in Australia was \$45000000000 and counting - don't let yourself fall into the same trap!
Summing it all up
Use the power of compound interest for good, not evil. Save up your money and watch it grow, rather than taking out high-interest debts that can easily get out of control. Good luck!