Today is 1 July 2022. Jen is 22 years old today and she is planning to purchase a real estate with the price of $1,000,000 on 1 January 2033. Jen believes that, at the time of purchasing the house, she should have saving to cover the 20% of the house price (i.e., $200,000) over the period from July 2022 to December 2032 and she can borrow the remaining 80% of the house price (i.e., $800,000) through a 25-year mortgage (it starts from 1 January 2033) from MQU Bank at an interest rate of j12 = 5.2% p.a. To save the 20% of the house price (i.e., $200,000), Jen plans to deposit x% of her monthly after-tax salary at the end of each month from July 2022June 2028 and 3x 2% of her monthly after-tax salary at the end of each month from July 2028 December 2032 into a fund. For example, if x% = 20%, then Jen will deposit 20% of her monthly after-tax salary at the end of each month from July 2022June 2028 and 3 20%2 = 12% of her monthly after-tax salary at the end of each month from July 2028December 2032 into this fund. (30 marks) Jens current before-tax salary will be $60,000 p.a. payable monthly (i.e., $60,000/12 paid by the end of each month). Jen forecasts that her salary will grow along with inflation at the rate of y% p.a. The salary adjustment will only be conducted at the beginning of March in each year. For example, it is assumed that her current salary is $60,000 p.a. payable monthly from July 2022 to February 2023, and then her salary is $60, 000(1 + y%) p.a. payable monthly from March 2023 to February 2024. Jen assumes the value of y is same as the Australian CPI rate for 2022. You need to use FactSet to find the Australian CPI rate for 2022. Jen predicts that she might change her job every three years and she might have a salary increase of $20,000 at the time she changes her job. It is assumed that Jen will firstly change her on 1 July 2025 and she will change again every three year afterwards. The deposit fund yield is estimated to be 5.75% p.a. payable monthly (i.e., j12 = 5.75%) from 1 July 2022 to the end of December 2024 and is estimated to be 6.25% p.a. payable monthly (j12 = 6.25%) from 1 January 2025 to the end of December 2032. Note that we need to accumulate 29 months from July 2022 to the end of December 2024 (since the first deposit will be paid at the 1 ACST2001 Financial Modelling Spreadsheet Project Task S2 2022 end of July 2022) and accumulate 96 months from January 2025 to the end of December 2032. Assume that all Jens salary income is taxable and she is exempted for medicare loading. 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., Jens annual income is $60,400 ($5,000 per month from July 2022 to February 2023 and $5,100 per month from March 2023 to June 2023) for the 20222023 financial year. Her annual income tax is $5, 092 + ($60, 400 $45, 000) 0.325 = $10, 097.00. For each income payment, her tax instalment is $10,097/12=$841.417 and her after-tax income per month is $4,158.583 from July 2022 to February 2023 and $4,258.583 from March 2023 to June 2023.

Table 1: Individual income tax rates

Taxable income Tax on this income

0$18,200 Nil

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

$45,000$120,000 $5,092 plus 32.5c for each $1 over $45,000

$120,000$180,000 $29,467 plus 37c for each $1 over $120,000

$180,000 and over $51,667 plus 45c for each $1 over $180,000

i. Calculate Jens 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 Jens average annual tax rate for each financial year from July 2022 to June 2028 (to calculate it, divide the total annual tax payment by Jens annual salary income.). Use a bar/column chart to present your results.