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 2022-June 2028 and 3x2% 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 2022-June 2028 and 320%2=12% of her monthly after-tax salary at the end of each month from July 2028-December 2032 into this fund. (30 marks) - Jen's 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. - Jen's 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 end of July 2022) and accumulate 96 months from January 2025 to the end of December 2032. - Assume that all Jen's 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 year 1. 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., Jen's 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 2022-2023 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 a. [12 marks] i. Find Jen's before-tax income per month from July 2022 to June 2033. ii. Based on the table 1 , calculate the annual tax amount for each financial Table 1: Individual income tax rates a. [12 marks ] i. Find Jen's before-tax income per month from July 2022 to June 2033. ii. Based on the table 1 , calculate the annual tax amount for each financial year from July 2022 to June 2033. iii. Calculate Jen's after-tax income per month from July 2022 to June 2033. Note that Jen only makes deposits into the fund from July 2022 to December 2032 , but you need the salary and tax information for the whole 2032-2033 financial year to calculate the after tax income. 1 Note that a financial year is defined as a period starting on 1 July and ending on the next 30 June in Australia. b. [6 marks] Use Goal Seek to find the value of x. c. [12 marks ] i. Calculate Jen's 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 Jen's average annual tax rate for each financial year from July 2022 to June 2028 (to calculate it, divide the total annual tax payment by Jen's annual salary income.). Use a bar/column chart to present your results. 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 2022-June 2028 and 3x2% 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 2022-June 2028 and 320%2=12% of her monthly after-tax salary at the end of each month from July 2028-December 2032 into this fund. (30 marks) - Jen's 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. - Jen's 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 end of July 2022) and accumulate 96 months from January 2025 to the end of December 2032. - Assume that all Jen's 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 year 1. 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., Jen's 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 2022-2023 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 a. [12 marks] i. Find Jen's before-tax income per month from July 2022 to June 2033. ii. Based on the table 1 , calculate the annual tax amount for each financial Table 1: Individual income tax rates a. [12 marks ] i. Find Jen's before-tax income per month from July 2022 to June 2033. ii. Based on the table 1 , calculate the annual tax amount for each financial year from July 2022 to June 2033. iii. Calculate Jen's after-tax income per month from July 2022 to June 2033. Note that Jen only makes deposits into the fund from July 2022 to December 2032 , but you need the salary and tax information for the whole 2032-2033 financial year to calculate the after tax income. 1 Note that a financial year is defined as a period starting on 1 July and ending on the next 30 June in Australia. b. [6 marks] Use Goal Seek to find the value of x. c. [12 marks ] i. Calculate Jen's 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 Jen's average annual tax rate for each financial year from July 2022 to June 2028 (to calculate it, divide the total annual tax payment by Jen's annual salary income.). Use a bar/column chart to present your results