Excel assignment
ACST6003: Principles of Finance ASSIGNMENT – S1, 2020 Due date and time: Friday May 15, 2020 at 11:59pm via iLearn Instructions – Read Carefully Before Attempting the Assignment • Provide your answers in the worksheets contained within the acst6003-assignment.xlsx file 1. Each worksheet is named after a question you need to answer, e.g. answer question 7 in worksheet Q7 • When presenting or commenting on your results, format excel cells to round off numbers to 3 decimal places but use whole numbers (not rounded off) in subsequent computations • When asked to comment on your results it is sufficient to write 3-4 lines of comment • Fill out the Assignment cover sheet with your details (no need to sign) • Your assignment submission needs to include only two files: 1. Filled out Assignment cover sheet 2. Completed acst6003-assignment.xlsx file • Assignment is to be submitted electronically via iLearn Total number of marks: 30 Total number of questions: 12 Data Description Excel file acst6003-assignment.xlsx contains the following data: • Annual yields on 30 day bank accepted bills (prices worksheet) • All Ordinaries Share price index (prices worksheet) • Share prices of Insurance Australia Group Limited (IAG) and Macquarie Group Limited (MQG) (prices worksheet) • Dividends for IAG (dividends worksheet) • Dividends for MQG (dividends worksheet) Questions 1. Calculate monthly returns for the All Ordinaries (????), IAG (????????) and MQG (????????) using the following formula ???? = 100 × � ???? ????−1 − 1�. Compute a monthly 30 day bank accepted bill rate as follows ?????? = ?????????????? 12 . (2 marks) 2. Compute ??(??), ?? and CV for all four return/yield series over the 1/09/2000 - 1/02/2020 time period. Comment on all computed quantities in regards to the risk return trade-off. (2 marks) 3. Compute excess returns for the market, i.e. market risk premium as ?????? = ???? − ??????, where ???? is the return on the All Ords computed in question 1, and ?????? represents the monthly return on the 30 day bank accepted bill rate. Also compute excess return for each IAG and MQG as follows ?????????? = ???????? − ?????? and ?????????? = ???????? − ??????. Use the 1/09/2000 – 1/02/2020 time period. (2 marks) 4. Obtain CAPM betas for both IAG and MQG using excess returns by estimating the following regressions ?????????? = ???????? + ?????????????? + ???????? ?????????? = ???????? + ?????????????? + ???????? where ???????? and ???????? are the random error terms. (2 marks) 5. Present and comment on the two beta coefficients estimated in question 4. What do they imply about the amounts of systematic risk? (2 marks) 6. Compute the expected returns for IAG and MQG using the CAPM, i.e. ??(????????) = ?????? + ????????�??(????) − ??????� ??�????????� = ?????? + ????????�??(????) − ??????� where • ?????? is the average of the monthly risk free rate computed in question 2 • ??(????) is the average of the market return computed in question 2 Comment and compare the two computed monthly expected returns. (4 marks) 7. Annualise the monthly expected returns computed in question 6 by calculating the effective annual rate (EAR). Comment on your results. (2 marks) 8. Calculate annual growth rates for dividends for AIG (????????) and for MQG �????????� using the formula ???? = ???? ????−1 − 1 (2 marks) 9. Compute the average annual dividend growth rate for IAG and MQG, ??�?????? and ??�?????? using the following formula ??� = 1 ?? ∑????. Comment on the two average dividend growth rates. (2 marks) 10. Assuming the zero dividend growth model what should be the share price? Use the last annual dividend available (2019) and the ?? = ?????? computed in question 7. Given the last share prices for IAG and MQG (1/02/2020) how well does the zero growth dividend model explain current share prices? Which company is underpriced and which company overpriced according to the zero growth dividend model (calculate percent difference between the model and actual price, i.e. 100 × �???????????? ?????????????? − 1�)? (4 marks) 11. Assuming constant dividend growth model what should the share prices be? Use the last annual dividend available (2019) for D, ?? = ?????? from question 7, and ?? = ??� 5 from question 9 in the computation. Given the last share prices for IAG and MQG (1/02/2020) how well does the constant growth dividend model explain current price? Which company is underpriced and which company overpriced according to the zero growth dividend model (calculate percent difference between the model and actual price, i.e. 100 × �???????????? ?????????????? − 1�)? (4 marks) 12. Why was it inappropriate to use historical dividend growth rates (computed in question 9) as the input in the constant dividend growth model (question 12), i.e. why did we assume that the growth rates will be 1/5 of their historical average? (2 marks) Data Description Excel file acst6003-assignment.xlsx contains the following data: Annual yields on 30 day bank accepted bills (prices worksheet) All Ordinaries Share price index (prices worksheet) Share prices of Insurance Australia Group Limited (IAG) and Macquarie Group Limited (MQG) (prices worksheet) Dividends for IAG (dividends worksheet) Dividends for MQG (dividends worksheet) Questions 1. Calculate monthly returns for the All Ordinaries ,(?-?.), IAG ,,?-???.. and MQG (,?-???.) using the following formula ,?-?.=100×,,,?-?.-,?-?−1..−1.. Compute a monthly 30 day bank accepted bill rate as follows ,?-??.=,,?-????... (2 marks) 2. Compute ?,?., ? and CV for all four return/yield series over the 1/09/2000 - 1/02/2020 time period. Comment on all computed quantities in regards to the risk return trade-off. (2 marks) 3. Compute excess returns for the market, i.e. market risk premium as ,?-?-?.=,?-?.−,?-??., where ,?-?. is the return on the All Ords computed in question 1, and ,?-??. represents the monthly return on the 30 day bank accepted bill rate. ... (2 marks) 4. Obtain CAPM betas for both IAG and MQG using excess returns by estimating the following regressions ,?-???-?.=,?-???.+,?-???.,?-?-?.+,?-???. ,?-???-?.=,?-???.+,?-???.,?-?-?.+,?-???. where ,?-???. and ,?-???. are the random error terms. (2 marks) 5. Present and comment on the two beta coefficients estimated in question 4. What do they imply about the amounts of systematic risk? (2 marks) 6. Compute the expected returns for IAG and MQG using the CAPM, i.e. ?(,?-???.)=,?-??.+,?-???.,?,,?-?..−,?-??.. ?,,?-???..=,?-??.+,?-???.,?,,?-?..−,?-??.. where ,?-??. is the average of the monthly risk free rate computed in question 2 ,?(?-?.) is the average of the market return computed in question 2 Comment and compare the two computed monthly expected returns. (4 marks) 7. Annualise the monthly expected returns computed in question 6 by calculating the effective annual rate (EAR). Comment on your results. (2 marks) 8. Calculate annual growth rates for dividends for AIG ,,?-???.. and for MQG ,,?-???.. using the formula ,?-?.=,,?-?.-,?-?−1..−1 (2 marks) 9. Compute the average annual dividend growth rate for IAG and MQG, ,,?.-???. and ,,?.-???. using the following formula ,?.=,1-?.∑,?-?.. Comment on the two average dividend growth rates. (2 marks) (4 marks)