# Modelling Techniques for Finance

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

[order_calculator]