Excel file data will be given below.

Excel File data

Dividend Data

Price data

Data Description Excel file acst6003-assignment.xlsx contains the following data: - A 10-year loan on $355,000 ('Amortisation_Q1' worksheet) - Annual yields on 30-day bank accepted bills ('price data' worksheet) - All Ordinaries Share price index ('price data' worksheet) - Share prices of Australian and New Zealand Banking Group Limited (ANZ) and Cochlear Limited (COH) ('price data' worksheet) - Dividends for COH ('dividend data' worksheet) - Dividends for ANZ ('dividend data' worksheet) For the purposes of this report, when you look at the prices for all companies only consider the data column "Adj Close price" in your analysis. Questions Q1a. Assume you take a loan of $355,000. The loan is for 10 years, and the annual interest rate offered by bank is 10.75 per cent. If the cash repayment frequency is on monthly basis and interest payment frequency is on quarterly basis, using the worksheet "Amortisation_Q1" calculate the number of repayments, effective annual rate, and size of repayment on the loan amount. ( 3 marks) Q1b. Complete the loan amortisation table using worksheet "Amortisation_Q1". At the end of the table calculate the principal repayment amount for period 20 ? Comment on whether the effective annual rate calculated in part Q1a matches periodicity of cash flow or interest rate and explain why (4 marks) Q2a. Compute the monthly returns for the All Ordinaries (Rm), Cochlear Limited (RCOH) and ANZ (RANZ) using the following formula Rt=100(Pt1Pt1). Compute a monthly 30 day bank accepted bill rate as follows: Rrf=12Rannual (1.5 marks). Q2b. Compute E(R),,CV and sharp ratio for all three-return series over the 01/02/20051/12/2021 time period. Comment on all computed quantities in regard to the risk return trade-off using CV and sharp ratio. Which risk return trade-off would you prefer and why? (4 marks) Q2c. Compute excess returns from 01/02/2005 to 01/12/2021 for the market, i.e., market risk premium as Rme=RmRrf, where Rm is the return on the All Ords computed in question 2a, and Rrf represents the monthly return on the 30 -day bank accepted bill rate. Also compute excess return for each ANZ and COH as follows RANZe=RANZRrf and RCOHe= RCOHRrf(1.5 marks) Q2d. Plot the excess returns of each company against All Ords market index in two different scatter plots with best fitted line and equation. ( 3 marks) Q3a. Using the following regression model obtain CAPM betas for both ANZ and COH using excess returns by estimating the following regressions ( 1 mark) RCOHe=COH+COHRme+uCOHRANZe=ANZ+ANZRme+uANZ where uCOH and uANZ are the random error terms of the regression models. where uCOH and uANZ are the random error terms of the regression models. Q36. Present the two beta coefficients estimated in question Q3a (use 4 decimal places). Comment on what do they imply about systematic risk? (2 marks) Q4a. Compute the expected returns for ANZ and COH using the CAPM, i.e. E(RANZ)=Rrf+ANZ(E(Rm)Rrf)E(RCOH)=Rrf+COH(E(Rm)Rrf) Where: Rrf is the average of the monthly risk-free rate - E(Rm) is the average of the market return Note: provide final answer in 4 decimal places (1 mark) COH on 01/12/2019 and you decide to hold the shares in the first two years of COVID-19 crises then sell the two shares on 1/12/2021. Calculate your holding period return for the two shares. State the formula you use in your calculation. Comment and compare your results with part 4a above and state which share is over-valued and under-valued. ( 3 marks) Q5a. Suppose your graduate base salary is $120,000 p.a. and your marginal tax rate is 37%. You bought 10,000 shares of COH and 5,000 shares of ANZ just on 1/01/2019. You decide to sell them on 1/08/2021. Calculate your tax payable amount of each share separately, show your working in detail and state formula? (2 marks) Q5b. Assume constant growth model holds. The effective annualised return is 0.1021 and 0.2047 for ANZ and COH respectively. Find the share price of ANZ and COH at time 2021 if the average of the annual growth rate is expected to decline for ANZ and increase for COH from time 2021 onwards infinitely. You will need to consider calculating the annual growth rate in dividend for both shares using the formula (gt=Dt1Dt1) and show your working Additional Note: Assume all dividends were paid between between February and November months of each year Ready Zx Accessibility: Investigate Ready Zx2 Accessibility: Investigate 17C 17C Partly sunny Data Description Excel file acst6003-assignment.xlsx contains the following data: - A 10-year loan on $355,000 ('Amortisation_Q1' worksheet) - Annual yields on 30-day bank accepted bills ('price data' worksheet) - All Ordinaries Share price index ('price data' worksheet) - Share prices of Australian and New Zealand Banking Group Limited (ANZ) and Cochlear Limited (COH) ('price data' worksheet) - Dividends for COH ('dividend data' worksheet) - Dividends for ANZ ('dividend data' worksheet) For the purposes of this report, when you look at the prices for all companies only consider the data column "Adj Close price" in your analysis. Questions Q1a. Assume you take a loan of $355,000. The loan is for 10 years, and the annual interest rate offered by bank is 10.75 per cent. If the cash repayment frequency is on monthly basis and interest payment frequency is on quarterly basis, using the worksheet "Amortisation_Q1" calculate the number of repayments, effective annual rate, and size of repayment on the loan amount. ( 3 marks) Q1b. Complete the loan amortisation table using worksheet "Amortisation_Q1". At the end of the table calculate the principal repayment amount for period 20 ? Comment on whether the effective annual rate calculated in part Q1a matches periodicity of cash flow or interest rate and explain why (4 marks) Q2a. Compute the monthly returns for the All Ordinaries (Rm), Cochlear Limited (RCOH) and ANZ (RANZ) using the following formula Rt=100(Pt1Pt1). Compute a monthly 30 day bank accepted bill rate as follows: Rrf=12Rannual (1.5 marks). Q2b. Compute E(R),,CV and sharp ratio for all three-return series over the 01/02/20051/12/2021 time period. Comment on all computed quantities in regard to the risk return trade-off using CV and sharp ratio. Which risk return trade-off would you prefer and why? (4 marks) Q2c. Compute excess returns from 01/02/2005 to 01/12/2021 for the market, i.e., market risk premium as Rme=RmRrf, where Rm is the return on the All Ords computed in question 2a, and Rrf represents the monthly return on the 30 -day bank accepted bill rate. Also compute excess return for each ANZ and COH as follows RANZe=RANZRrf and RCOHe= RCOHRrf(1.5 marks) Q2d. Plot the excess returns of each company against All Ords market index in two different scatter plots with best fitted line and equation. ( 3 marks) Q3a. Using the following regression model obtain CAPM betas for both ANZ and COH using excess returns by estimating the following regressions ( 1 mark) RCOHe=COH+COHRme+uCOHRANZe=ANZ+ANZRme+uANZ where uCOH and uANZ are the random error terms of the regression models. where uCOH and uANZ are the random error terms of the regression models. Q36. Present the two beta coefficients estimated in question Q3a (use 4 decimal places). Comment on what do they imply about systematic risk? (2 marks) Q4a. Compute the expected returns for ANZ and COH using the CAPM, i.e. E(RANZ)=Rrf+ANZ(E(Rm)Rrf)E(RCOH)=Rrf+COH(E(Rm)Rrf) Where: Rrf is the average of the monthly risk-free rate - E(Rm) is the average of the market return Note: provide final answer in 4 decimal places (1 mark) COH on 01/12/2019 and you decide to hold the shares in the first two years of COVID-19 crises then sell the two shares on 1/12/2021. Calculate your holding period return for the two shares. State the formula you use in your calculation. Comment and compare your results with part 4a above and state which share is over-valued and under-valued. ( 3 marks) Q5a. Suppose your graduate base salary is $120,000 p.a. and your marginal tax rate is 37%. You bought 10,000 shares of COH and 5,000 shares of ANZ just on 1/01/2019. You decide to sell them on 1/08/2021. Calculate your tax payable amount of each share separately, show your working in detail and state formula? (2 marks) Q5b. Assume constant growth model holds. The effective annualised return is 0.1021 and 0.2047 for ANZ and COH respectively. Find the share price of ANZ and COH at time 2021 if the average of the annual growth rate is expected to decline for ANZ and increase for COH from time 2021 onwards infinitely. You will need to consider calculating the annual growth rate in dividend for both shares using the formula (gt=Dt1Dt1) and show your working Additional Note: Assume all dividends were paid between between February and November months of each year Ready Zx Accessibility: Investigate Ready Zx2 Accessibility: Investigate 17C 17C Partly sunny