# 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…

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., j12 = 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 2020—2021 \$60, 000.00 \$11,047.00rate 3‘12 Income 2020—2022 \$61, 200.00 \$11,437.00growth rate p.a.. Income Minimum 2020 202 1threshold tax payment J uly—2 021 J uly—2022J u.ne June 0 — \$18, 200 0 After-tax \$4,079.41? 34.146317income permonth \$18, 200 —\$37, 000 \$37, 000 — 32.5% \$3,572 Total \$32,000.00\$87, 000 accumulateddeposit \$87, 000 — 37% \$19,822 Deposit rate 30.75%\$ 180, 000 X 10 Above 45% \$54,2323 180, 000 a. [2 marks] Give the Excel cell formulae you would use to calculate the valueof cells F2 and F3. b. [2 mark] Give the Excel cell formulae you would use to calculate the value ofcells F6 and GS. c. [3 mark] Give the Excel cell formula you would use to calculate the value ofcell F8. (1. [4 marks] Describe the steps Judy used to generate her result for 33% in cellF9 using Goal Seek. 