Assignment -
Assume today is 1 March 2018 and you are a financial advisor. Darren Smith, one of your clients, seeks your opinion on his personal finance and investment portfolio. Aged in his mid-40s, Darren has been working as a scientist in the area of artificial intelligence. Since 2012, Darren has earnt more than $200,000 per year (after tax).
Question 1: Darren purchased an investment property for $1,000,000 on 28 February 2013. The purchase was funded by a $670,000 mortgage from the bank. This loan was for a term of 30 years at an interest rate of 8.4% per annum. The initial five year period was subject to 'interest-only' repayments, in which Darren only had to pay interest on the mortgage balance outstanding ($670,000) at the end of each month (since 31 March 2013). From 1 March 2018, Darren must make equal monthly repayments (at the end of each month, with the next payment due 31 March 2018). For each of the questions below, assume each month has the same number of days.
a. How much is Darren's total recurring monthly repayment if he intends to pay off the mortgage over the remaining 25 years?
b. Based on the result in part (a), prepare a monthly mortgage amortisation schedule in EXCEL (from 1 March 2018 to 28 February 2043). The last repayment must clear up the mortgage outstanding balance and accrued interest (as of 28 February 2043). An amortisation schedule should show the following items at each time interval: (i) opening balance; (ii) interest paid; (iii) principal repayments; and (iv) closing balance.
c. What is the remaining mortgage balance on 1 March 2023? You can make reference to your mortgage amortization schedule in part (a). Bonus mark - if students use Equation/Formula to evaluate this section successfully, each member in the group will receive TWO bonus marks in their final grade (not exam). Final grade is 'capped' at 100.
d. If Darren decides to repay a total of $7,000 per month, when will he pay off the mortgage? Please specify in which year and month.
Question 2: Darren also actively trades in the security market. Today, his broker gave him four investment recommendations and quotes (market prices).
Security 1: Bank Accepted Bill
The bank accepted bill has a face value of $100,000 with a maturity of 190 days. Currently, it is trading at $98,000. Darren's required rate of return on this investment is 4.38% per annum.
Security 2: Corporate Bond
The corporate bond has a face value of $100,000 with an 8% per annum coupon. The coupon is paid semi-annually, and the last coupon was paid yesterday. The bond will mature in four years. Currently, it is trading at $102,500. Darren's required rate of return on this investment is 7% per annum.
Security 3: Common Share A
The underlying company pays a constant dividend of $3 annually. The last dividend was paid yesterday. Currently, Common Share A is trading at $24.50. The broker can offer Darren up to 3,000 shares. Darren's required rate of return on this investment is 12% per annum.
Security 4: Common Share B
The underlying company pays an annual dividend. There was a $2 dividend paid yesterday. As per the market consensus, the company's dividend is expected to grow by 20% per annum in the first four years. After that, the dividend growth rate is expected to be constant at 5% per annum indefinitely. Currently, Common Share B is trading at $37. The broker can offer Darren up to 4,000 shares. Darren's required rate of return on this investment is 14% per annum.
a. Which security or securities do you suggest Darren purchase? Darren has sufficient funds to purchase all four investment recommendations from his broker i.e. (i) one Bank Accepted Bill; (ii) one Corporate Bond; (iii) 3,000 Common Share A; and (iv) 4,000 Common Share B. Darren's funds currently sit in the bank earning 2% interest per annum. Quantitatively justify your recommendation(s).
b. Suppose Darren follows your recommendation in part (a). Assume one month has elapsed, and the price of each security is shown in the table below. How much is Darren's realized return over the month?
|
Security
|
Bank Accepted Bill
|
Corporate Bond
|
Common Share A
|
Common Share B
|
|
Price
|
98,300
|
102,000
|
23.80
|
37.50
|