smc-6912-60-Spreadsheets

Mei sets up a superannuation account to save for retirement. She contributes $5000 at the end of each year into the account which earns interest at 6% per annum, compounded annually.

The spreadsheet shown models the first 4 years of the account.

Calculate the value in cell C9. (1 mark)
--- 2 WORK AREA LINES (style=lined) ---
From the end of year 4, Mei increases her annual contribution from $5000 to $8000. Calculate the balance in her superannuation account at the end of year 7, and determine how much more this is than if she had continued contributing $5000. (3 marks)
--- 10 WORK AREA LINES (style=lined) ---

Show Answers Only

a. $\text{C9} = \$618.00$

b. $\text{Balance at end of year 7} = \$51\,519.99$

$\text{Mei has}\ \$9550.80\ \text{more than at the standard contribution.}$

Show Worked Solution

a. $\text{C9 (Year 3 interest)} = \text{balance at start} \times \text{rate}$

$\text{C9} = 10\,300 \times 0.06 = \$618.00$

b. $\text{Using}\ \ P+I+C\ \ \text{from end of year 4 balance}\ \$21\,873.08:$

$\text{Increased contributions of}\ \$8000\ \text{from year 5:}$

$\text{Year 5:}\ 21\,873.08+21\,873.08 \times 0.06+8000=\$31\,185.46$

$\text{Year 6:}\ 31\,185.46+31\,185.46 \times 0.06+8000=\$41\,056.59$

$\text{Year 7:}\ 41\,056.59+41\,056.59 \times 0.06+8000=\$51\,519.99$

$\text{Standard contributions of}\ \$5000\ \text{from year 5:}$

$\text{Year 5:}\ 21\,873.08+21\,873.08 \times 0.06+5000=\$28\,185.46$

$\text{Year 6:}\ 28\,185.46+28\,185.46 \times 0.06+5000=\$34\,876.59$

$\text{Year 7:}\ 34\,876.59+34\,876.59 \times 0.06+5000=\$41\,969.19$

$\text{Difference}=51\,519.99-41\,969.19=\$9550.80$

$\therefore\ \text{Mei has}\ \$9550.80\ \text{more by increasing her contributions.}$

Julie invests $12 500 in a savings account. Interest is paid at a fixed monthly rate. At the end of each month, after the monthly interest is added, Julie makes a deposit of $500.

Julie has created a spreadsheet to show the activity in her savings account. The details for the first 6 months are shown.

By finding the monthly rate of interest, complete the final row above for the 7th month. (3 marks)

--- 6 WORK AREA LINES (style=lined) ---

Show Answers Only

`15\ 624.20,\ 23.44,\ 16\ 147.64`

Show Worked Solution

`\text{Monthly interest rate} = \frac{18.75}{12\ 500} = 0.0015 =\ text{0.15%}`

♦ Mean mark 43%.

`\text{Row 7 calculations:}`

`\text{Beginning balance}= 15\ 624.20`

`\text{Monthly interest}= 15\ 624.20 \times 0.0015= 23.44`

`\text{End of month balance}= 15\ 624.20 + 23.44 + 500= 16\ 147.64`

Financial Maths, STD2 EQ-Bank 29

Financial Maths, STD2 F5 2021 HSC 21