Today is 1 January 2018. Judy is 20 years old today and she is planning to purchase a car priced at $80,000 on 1 July 2022. Judy believes that, at the time of purchasing the car, she should have savings to cover 40% of the car price (i.e., $32,000) and she can borrow the remaining 60% of the car price (i.e., $48,000) through a 7-year loan.

To save the 40% of the car price (i.e., $32,000), Judy plans to deposit x% of her monthly after-tax salary into a fund at the end of each month from July 2020 to June 2022.

Judy assumes that she can successfully graduate from university and find a job on 1 July 2020. Judy estimates that her initial before-tax salary will be $60,000 p.a. payable monthly. All her salary will be paid at the end of each month. Judy forecasts that her salary will grow at the rate of 2% p.a. The salary adjustment will only be conducted at the beginning of July in each year. For example, it is assumed that her salary is $60,000 p.a. payable monthly from July 2020 to June 2021, and then her salary is $60, 000(1 + 2%) p.a. payable monthly from July 2021 to June 2022.

The deposit fund yield is estimated to be 5.5% p.a. payable monthly (i.e., j₁₂ = 5.5% p.a.) from July 2020 to June 2022.

Assume that all her salary is taxable. Progressive marginal individual income tax rates are as shown in table 1 (A5:C10). Based on the projected income and tax rate from table 1, we can calculate the income tax for a financial year1 . Then we can calculate the tax instalment and after-tax salary for each income salary payment (for simplicity, we assume that there is no Medicare Levy.). For example, if a person's annual income is $50,000. Her annual income tax is $3, 572 + ($50, 000 ? $37, 000) 0.325 = $7, 797. For each income payment, her monthly tax instalment is $7,797/12=$649.75 and after-tax monthly income is $50, 000/12?$649.75 = $3, 516.92.

Judy calculates her future income, income tax and after-tax income from July 2020 to June 2022. She then sets a formula in F8 and uses Goal Seek to find the value of x.

**1Note that a financial year is defined as a period starting on 1 July and ending on the next 30 June in Australia.

Table 1: Excel spreadsheet. :- Judy's initial $30, 000 Financial Before-tax Tax amount Tax monthly -_ Fund yield 20202021 $60, 000.00 $11,047.00 rate 3'12 Income 20202022 $61, 200.00 $11,437.00 growth rate p.a.. Income Minimum 2020 202 1 threshold tax payment J uly2 021 J uly2022 J u.ne June 0 $18, 200 0 After-tax $4,079.41? 34.146317 income per month $18, 200 $37, 000 $37, 000 32.5% $3,572 Total $32,000.00 $87, 000 accumulated deposit $87, 000 37% $19,822 Deposit rate 30.75% $ 180, 000 X 10 Above 45% $54,232 3 180, 000 a. [2 marks] Give the Excel cell formulae you would use to calculate the value of cells F2 and F3. b. [2 mark] Give the Excel cell formulae you would use to calculate the value of cells F6 and GS. c. [3 mark] Give the Excel cell formula you would use to calculate the value of cell F8. (1. [4 marks] Describe the steps Judy used to generate her result for 33% in cell F9 using Goal Seek