Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Spreadsheet Project Task Today is 1 July 2022. Jen is 22 years old today and she is planning to purchase a real estate with the
Spreadsheet Project Task 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 2% 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 3 x 20%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. . 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 (712 = 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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started