Today is 1 January 2018 Judy is 20 years old today and she i

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 rem aining 60% of the car price (ie,, $48.000) through a 7-year loan To save the 40% of the car price (ie.. S32.000), Judy plans to deposit 2% of her monthly after-tax salary into a fund at the end of each month from July 2020 to June 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% pa. 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%) pa. payable monthly from July 2021 to June 2022 The deposit fund yield is estimated to be 5.5% p.a, payable m onthly (i.e., J12- 5.5% pa.) 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 year. Then we can calculate the tax instalment and after-tax salary for each income salary pay- ment (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) x 0.325 $7,797. For each income payment, her monthly tax insta- ment 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 ofr Note that a financial year is defined as a period starting on1 July and ending on the next 30 June in Australia.

Solution

a) In column F2 following formula will be entered =IF(E2>180000,(E2-180000)*0.45+$C$8+$C$9+$C$10,IF(E2>87000,(E2-87000)*0.37+$C$8+$C$9,IF(E2>37000,(E2-37000)*0.325+$C$8,IF(E2>18200,(E2-18200)*0.19,0))))

in column F3  =IF(E3>180000,(E3-180000)*0.45+$C$8+$C$9+$C$10,IF(E3>87000,(E3-87000)*0.37+$C$8+$C$9,IF(E3>37000,(E3-37000)*0.325+$C$8,IF(E3>18200,(E3-18200)*0.19,0))))

b) in column F6 =(E2-F2)/12

in column G6 =(E3-F3)/12

c) in column F8 =80000*0.4