planning to purchase an apartment

FIND A SOLUTION AT Academic Writers Bay

Today is 1 July 2019. Jake is 35 years old and he is planning to purchase an apartment valued at $700,000 on 1 January 2027. Jack believes that, at the time of purchasing the house, he should have enough saving to cover 20% of the apartment price (i.e., $140,000) on 1 January 2027 and he can borrow the remaining 80% of the apartment price (i.e., $560,000) through a 30-year mortgage (it starts from 1 January 2027). (30 marks)
It is assumed that Jake has two loan packages to choose between on 1 January 2027.
Package 1.
– Jack will make 360 monthly repayments at the end of each month over the 30-year loan period (from 1 January 2027 to 31 December 2056) with the first payment being made on 31 January 2027. This loan needs to be fully repaid by the end of 30 years.
– This package has an annual fee of $500. The package fee is paid on 31 July of each year during the 30-year loan period (from 1 January 2027 to 31 December 2056). The first one will be paid on 31 July 2027.
– This package has a one-time loan establishment fee of $1,000 paid on 1 January 2027.
– The interest rate of this package is j12 = 6% p.a.
Package 2.
– Jake will make 360 monthly repayments at the end of each month over 30-year loan period (from 1 January 2027 to 31 December 2056) with the first payment will be made on 31 January 2027. This loan needs to be fully repaid by the end of 30 years.
– Jake can have a five-year interest-only-period at the beginning of the mortgage. Jake’s repayments will be interest-only1 for the first 5 years
1Interest-only repayment means your repayments only cover the interest on the amount you have borrowed, during the interest-only period. For example, if you borrow $1,000 through a five-year mortgage on 1 July 2019 with a one year interest-only period at j12 = 6% during the first year (1 July 2019–30 June 2020), your monthly repayment is $1, 000 × 6%/12 = $5 per month. On 1 July 2020, you need to use the remaining four years to repay the borrowed $1,000. The present value on 1 July 2020 of all payments in the remaining four years should be equal to $1,000.
(i.e., first 60 payments will be interest-only payments), followed by pay-ments of principal plus interest for the following 25 years.
– This package has an annual fee of $650. The package fee is paid on 31 July of each year during the 30-year loan period (from 1 January 2027 to 31 December 2056). The first one will be paid on 31 July 2027.
– This package has a one-time loan establishment fee of $1,500 paid on 1 January 2027.
– The interest rate of this package is j12 = 6% p.a.
To save the 20% of the apartment price (i.e., $140,000), Jake plans to deposit x% of his monthly after-tax salary into a investment fund at the beginning of each month from July 2019 to December 2026. Jake’s current after-tax income is $90,000 p.a. payable monthly. His salary is paid at the beginning of each month. Jake forecasts his salary will grow at the rate of y% p.a. The salary adjustment will only be conducted at the beginning of March in each year after the annual review with his manager. For example, it is assumed that his salary is $90,000 p.a. payable monthly from July 2019 to February 2020, and then his salary is $90, 000(1 + y%) p.a. payable monthly from March 2020 to February 2021 (inclusive). Jake assumes the value of y is same as the Australian CPI rate for 2018. You need to use FactSet to find the actual Australian CPI rate for 2018.
The yield rate of Jake’s investment fund is estimated to be 6.5% p.a. payable monthly (i.e. j12 = 6.5%) from 1 July 2019 to 31 December 2023 and is estimated to be 6.8% p.a. payable monthly (i.e. j12 = 6.8%) from 1 January 2024 to 31 December 2026. [12 marks]
– Calculate the loan repayment amount (excluding the annual fee and es-tablishment fee) for each month of package 1 and package 2.
– Use Goal Seek to find the net borrowing cost for package 1 and pack-age 2 by including the annual fee (expressed as a rate p.a. compounded monthly).
– Use a bar or column chart to compare the loan repayment amount of package 1 and package 2 over the first 7 years. Plot 84 monthly payments for both package 1 and package 2. [6 marks]
– Calculate Jack’s after-tax income per month from July 2019 to December 2026.
– Use Goal Seek to find the value of x.
[12 marks] It is assumed that Jack will rent out this apartment for $2,500 per month at the end of each month after his purchase and sell this apartment 7 years later immediately after the loan repayment is made (i.e. 1 January 2034) with the net sale proceeds of $800,000. Assume that there is no rental payment on 1 January 2034. Jack needs to use part of the sale proceeds to pay back the outstanding loan amount (i.e. present value of all future loan repayments). It is noticed that there are two potential outcomes for Jack: choosing loan package 1 and choosing loan package 2. We do not consider negative gearing for this question.
– Find the net cash flow in each month from January 2027 to December 2033 (inclusive2) for these two potential outcomes.
– Find the loan outstanding amount on 1 January 2034 for both loan pack-age 1 and package 2. Assume that the interest rate used for this calcula-tion is j12 = 6%.
– Use Goal Seek to find Jack’s net yield rate for both outcomes.
The deadline for Spreadsheet Project Task 3 submission is October 8th 2019, 9:00AM. Present your answers to the above questions in a functional Excel spread-sheet (it should be a XLSX or XLS file), giving each solution on a separate sheet (labelled Part a, Part b, and Part c). Your spreadsheet should be clearly labelled and easy to understand. Make sure you identify what the inputs and outputs are. Please include the necessary information, e.g., title, axis titles, etc., in your plot. Please document and describe your working steps. Please note that uploading a file can take up to 10 or 15 minutes. You need to submit your file at least 20 minutes before the deadline to ensure a successful submission.

YOU MAY ALSO READ ...  Critical Thinking Assignment 1
Order from Academic Writers Bay
Best Custom Essay Writing Services

QUALITY: 100% ORIGINAL PAPERNO PLAGIARISM – CUSTOM PAPER