Any topic (writer's choice)

Instructions

    Provide your answers in the worksheets in the each worksheet is named after a question you need to answer, e.g. answer question 1, 2, 3, in worksheet Q1, Q2, Q3. Each question should be answered in one worksheet. Scroll down the worksheet of each question to find any subparts which you need to answer.    When presenting or commenting on your results (final answer), format excel cells to round off numbers to 2 decimal places but use whole numbers (not rounded off) in subsequent or intermediate computations, unless a question states otherwise.    When asked to comment on your results it is sufficient to write 4-5 lines of comment.Data Description    A 15 years loan on $550,000 (Amortisation worksheet)    A 18.5 years bond (Bond worksheet)    Annual yields on 30 day bank accepted bills (prices worksheet)    All Ordinaries Share price index (prices worksheet)    Share prices of Australian and New Zealand Banking Group Limited (ANZ) and Woolworths Group Limited (WOW) (prices worksheet)    Dividends for ANZ (dividends worksheet)    Dividends for WOW (dividends worksheet)Questions Q1a. Assume you take a loan of $550,000. The loan is for 15 years and the annual interest rate offered by bank is 5.35%. If the cash repayment frequency is on monthly basis, using the worksheet Amortisation calculate the number of repayments, effective period rate and size of repayment on the loan amount. Q1b. Complete the loan amortisation table using worksheet Q1. At the end of the table calculate the principle repayment amount for period 160? Explain how the amortisation table works in loan repayment schedule. Q2a. Company Locklend Ltd has issued 18.5 years bond to raise capital. If the bond face value is 1000, the interest rate is 3.5% p.a. compounded semi-annually and the coupon rate is 4.5% p.a. Find the price of the bond using both PRICE and PV function. Comment on your results of the two functions. Q2b. Assume new information is given about yield and coupon rate. If yield reduces by 2% and all other information stay same as in part 2a, recalculate the price of the bond either using the PV or PRICE function. Instead of yield change suppose coupon rate reduces by 2% and all other information stay constant as in part 2a. Recalculate the price of the bond either using the PV or PRICE function. Comment on the results.Q3a. Compute the monthly returns for the All Ordinaries (????), Woolworth Limited (????????) and ANZ (????????) using the following formula ????=100?????????11?. Compute a monthly 30 day bank accepted bill rate as follows: ??????=??????????????12 .Q3b. Compute ??(??), ?? and CV for all four return series over the 01/10/2010 - 01/09/2020 time period. Comment on all computed quantities in regards to the risk return trade-off.Q4b. Obtain CAPM betas for both WOW and ANZ using excess returns by estimating the following regressions??????????=????????+??????????????+??????????????????=????????+??????????????+????????where ???????? and ???????? are the random error terms of the regression models.Q4c. Present and comment on the two beta coefficients estimated in question 4b. What do they imply about the amounts of systematic risk? Note: provide final answer in 4 decimal placesQ4d. Compute the expected returns for WOW and ANZ using the CAPM, i.e.??(?????????? )=??????+???????????(????)?????????(????????)=??????+???????????(????)???????Where: ?????? is the average of the monthly risk free rate computed in question 3 ??(????) is the average of the market return computed in question 3Note: provide final answer in 4 decimal placesQ4e. Assume you buy one share of WOW and one share of ANZ on 1/01/2019 and sell the shares on 1/12/2019. Calculate your holding period return for the two shares. State the formula you use in your calculation. Comment and compare your results with part 4d above and state which share is over-valued and under-valued. Note: provide final answer in 4 decimal places.Q5a. Calculate annual growth rates of dividends for ANZ (????????) and for WOW (????????) using the formula ? ????=????????11? and the average annual growth rate for ANZ and WOW. Note: provide final answer in 4 decimal placesQ5b. Assume constant growth model holds. The effective annualised return of ANZ and WOW are 11.70% and 3.89% respectively. Find the share price of ANZ and WOW at time 2020 if the average growth rate is expected to decline for ANZ but increase for WOW from time 2020 onwards infinitely. Note: provide final answer in 4 decimal placesQ6. Consider an investment cost $5000 today which pays fixed amount of $250 at the beginning of every month for 10 years and the annual percentage rate is 6.3% p.a. compounded quarterly. Explain how the conversion rate works which can be used to calculate either the present value or future value of this investment. Show the conversion formula only using the appropriate inputs in the formula?

Answer