Today is 1 January 2018. Judy is 20 years old today and she is planning to purchase a real estate with the price of $800,000 on 1 July 2028. Judy believes that, at the time of purchasing the house, she should have saving to cover the 20% of the house price (i.e., $160,000) over the period from January 2018 to June 2028 and she can borrow the remaining 80% of the house price (i.e., $640,000) through a 30- year mortgage (it starts from 1 July 2028) from MQU Bank at an interest rate of j12 = 6% p.a.

To save the 20% of the house price (i.e., $160,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 2028. (30 marks). 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 y% p.a. The salary adjustment will only be conducted at the beginning of February in each year. For example, it is assumed that her salary is $60,000 p.a. payable monthly from July 2020 to January 2021, and then her salary is $60, 000(1 + y%) p.a. payable monthly from February 2021 to January 2022. Judy assumes the value of y is same as the Australian CPI rate for 2017. You need to use FactSet to find the Australian CPI rate for 2017.

The deposit fund yield is estimated to be 5.5% p.a. payable monthly (i.e., j12 = 5.5%) from July 2020 to December 2022 and is estimated to be 6.5% p.a. payable monthly (j12 = 6.5%) from January 2023 to June 2028.

Assume that all her salary income is taxable. Progressive marginal individual income tax rates are as shown in table 1. 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 installment and after tax salary for each income salary payment (for simplicity, we assume that there is no medicare levy.). For example, if income growth rate is 2% p.a., Judys annual income is $60,500 ($5,000 per month from July 2020 to January 2021 and $5,100 per month from February 2021 to June 2021) for the 20202021 financial year. Her annual income tax is $3, 572 + ($60, 500 ? $37, 000) 0.325 = $11, 209.5. For each income payment, her tax instalment is $11,209.5/12=$934.125 and her after-tax income per month is $4,065.875 from July 2020 to January 2021 and $4,165.875 from February 2021 to June 2021.

Table 1: Individual income tax rates

Taxable income Tax on this income

0$18,200 Nil

$18,200$37,000 19c for each $1 over $18,200

$37,000$87,000 $3,572 plus 32.5c for each $1 over $37,000

$87,000$180,000 $19,822 plus 37c for each $1 over $87,000

$180,000 and over $54,232 plus 45c for each $1 over $180,000

c. i. Calculate Judys monthly loan repayment amount. Assume that the repayment is paid at the beginning of each month.

ii. Calculate the accumulated deposit value, if x = 35%.

iii. Calculate Judys average annual tax rate for each financial year from July 2020 to June 2028 (to calculate it, divide the total annual tax payment by Judys annual salary income.). Use a bar/column chart to present your results.

Using EXCEL