Question 4 (40 marks)
This question is to be done on a spreadsheet with the results pasted and submitted in a Word document. EASTS does not support spreadsheets. Make sure that you show all your workings – for example, do not simply put down the standard deviation but show how it was obtained. This does not mean giving the Excel algorithm. Please do not give cell formulae, cell references, etc, as the reader should be able to follow from a table – as you do in the text. Also please note using excel formula such as =STDEV() or =COVAR() is not acceptable).
a. Find the monthly opening and closing prices for the period 1 Jan 2014 – 31 Dec 2014 for Bendigo and Adelaide Bank Ltd (BEN.AX), and Origin Energy Ltd (ORG.AX) and the Market as proxied by the All Ordinaries index (^AORD). 3 marks
b. Calculate monthly holding period returns (%) for the period 1 Jan 2014 – 31 Dec 2014 for Bendigo and Adelaide Bank, Origin Energy and Market. The monthly holding period return is the percentage return (%) you would receive if you bought an asset on the first day of the month (opening price) and sold it on the last day of the month (closing price). (Use 'Close' rather than 'Adjusted Close' for the selling price and ignore any dividends). Show the formula and at least one sample calculation. 3 marks
c. Graph your results on one graph with returns on the y axis and time on the x axis.
d. Calculate the average monthly holding period return for Bendigo and Adelaide Bank, Origin Energy and Market. 1.5 marks
e. For Bendigo and Adelaide Bank, Origin Energy and Market, what is:
i. The annual holding period return; and 1.5 marks
ii. The standard deviation of the monthly holding period returns? 4.5 marks
f. Calculate the covariance of Bendigo and Adelaide Bank and Origin Energy over the year. 4 marks
g. If you decide to invest in a portfolio of two assets, calculate the expected portfolio return and risk if:
i. 10% of wealth is invested in Bendigo and Adelaide Bank and 90% in Origin Energy 5 marks
ii. 80% of wealth is invested in Bendigo and Adelaide Bank and 20% in Origin Energy 5 marks
h. Plot Bendigo and Adelaide Bank, Origin Energy and Market on a risk / return graph as well as the two portfolios you calculated in part g [See Fig 11.2 of text for example of graph]. 4.5 marks
i. Explain which asset or combination of assets would you invest. (Word limit 300 words) 5 marks