Paper, Order, or Assignment Requirements

Do questions on EXCEL

Question 1 (15 marks)

Download monthly adjusted close prices (close price adjusted for dividends and splits) between December 2010 to January 2016 from https://au.finance.yahoo.com for the following 10 Australian stocks and the S&P/ASX 200 index: TPG Telecom, Myer Holdings, Westpac Banking Corp, Woodside Petroleum, Fairfax Media, Village Roadshow, AGL Energy, Fortescue Metals, Woolworths Ltd and James Hardie.

Compute the monthly returns for each stock and for the ASX200. Compute the mean, variance, and standard deviation of each stock’s returns (both monthly and annual).

Regress each stock’s returns on the ASX200, computing: alpha, beta, R2 and corresponding t-statistics .

Hint: Regressions are discussed in Topics 2-4 and also in Chapter 33. Use the functions tintercept and tslope (add them into your spreadsheet) to test whether the alpha and beta are significant. (These functions are also given below.)

***********************VBA**************************

Function tintercept(yarray, xarray)

tintercept = Application.Index(Application.LinEst(yarray, xarray, , 1), 1, 2) / _

Application.Index(Application.LinEst(yarray, xarray, , 1), 2, 2)

End Function

Function tslope(yarray, xarray)

tslope = Application.Index(Application.LinEst(yarray, xarray, , 1), 1, 1) / _

Application.Index(Application.LinEst(yarray, xarray, , 1), 2, 1)

End Function

Question 2 (15 marks)

Consider the 10 stocks in Question 1 (ignore the ASX200)

Find two envelope portfolios using the techniques of Proposition 1 of Chapter 9. Assume that the historical means, variances, and covariances are also those expected to prevail in the future. Assume your own constant.

Use a data table to graph (σ, μ) frontier of the convex combinations of the two portfolios.

Find the global minimum variance portfolio (GMVP Chapter10.4 on page 259-261) of these ten assets.

Question 3 (10 marks)

Find the current (April 2016) equity market values for each stock using DatAnalysis Premium for each company (available at Deakin library). Considering the 10 stocks as a market portfolio, what are the value-weighted portfolio proportions of each stock? During the rest of this exercise, we will call this portfolio the benchmark portfolio. Assume that the benchmark portfolio of these 10 stocks as defined above is ex-ante optimal. If the risk-free rate is 2% per year, find the expected returns for each stock by solving the following equation where S is variance-covariance matrix.

Show that multiples of (λ can be any number) as defined below lead to the finding that the benchmark portfolio is optimal