Question 1 (13 marks)

The spreadsheet that comes with this assignment includes price data for 10 Australian

stocks and the S&P/ASX 200.

For all 10 stocks:

1. Compute the returns for each stock and for the ASX200.

2. Compute the mean, variance, and standard deviation of each stock’s returns

(both monthly and annual).

3. Regress each stock’s returns on the ASX200, computing: alpha, beta, and R2.

Regressions are discussed in Chapter 2 and also in Chapter 33. Use the functions

tintercept and tslope in Chapter 2 (add them into your spreadsheet) to test whether

the alpha and beta are significant. (These functions are also given below.)

For the first four stocks: TPG, Suncorp, NAB, JB Hi-Fi

1. Compute the variance-covariance matrix of these four stocks.

2. Using the mean return of each stock as its expected future return, compute the

mean, variance and standard deviation of returns, covariance, and correlation

coefficients for the following portfolios:

a. Portfolio A: An equally-weighted portfolio of all four stocks

b. Portfolio B: TPG = 60%, SUN = 25%, NAB = 40%, JB = -25%

3. Draw the sigma/mean (standard deviation-return, using Data Table) frontier

for convex combinations of these two portfolios

4. Can you find a portfolio or stock which is superior to the portfolios in the

convex combination? If so, show it and explain how you found it. What does

this say about the efficiency of these two portfolios?

***********************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 (12 marks)

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

1. 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.

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

portfolios.

3. Find 40 to 50 different possible envelope portfolios using the techniques of

Proposition 2 of Chapter 9. Can you find a frontier portfolio will all-positive

proportions of assets using Solver? Show your findings of using Solver.

4. Find the global minimum variance portfolio (GMVP Chapter10.5 on page

299-300) of these ten assets.

Question 3 (15 marks)

1. Find the current equity market values for each stock using Morningstar

DatAnalysis for each company (available at Deakin library). Considering the

10 stocks as a 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.

2. 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 (meaning: 2%/12 per

month—remember that the data is monthly), find the expected returns for each

stock by solving the following equation where S is variance-covariance matrix.

3. Show that the combination of , , f S E r r implies that the benchmark

portfolio is optimal.

4. Show that multiples of E r (λ can be any number) as defined below also

lead to the finding that the benchmark is optimal.

5. Find a set of returns E r so that the weighted-average benchmark return of

the portfolio is 8% per year (using Solver).

**Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.**