Calculate the 95 % confidence interval for the daily returns for MKT over this period.

ECON 1095 QUANTITATIVE METHODS IN FINANCE

 

Assignment 2 is due Sunday 2nd June and contributes 25% to the assessment of this course.

INSTRUCTIONS

Please up load one (and one only) either word or pdf file.  For the excel sections please just take screen shots of your work to show some of your workings then cut and paste into your document.  For the maths sections, if you prefer you can hand write and scan and add to word doc.

 

QUESTION 1

An investor is considering putting additional funds into the Australian share market; to assist with this decision she analyses the continuous daily returns for S&P/ASX 200 – PRICE INDEX (MKT) from 19 February 2018 to 19 February 2019 using Excel’s Data Analysis/Descriptive Statistics[1].  This data is available in ECON1095 Data Sem 1 2019.xlsx.

  • Calculate the 95 % confidence interval for the daily returns for MKT over this period.
  • The investor decides that she will only put extra money into the Australian share market if she can rule out negative returns. The average daily returns for the sample period should be above zero.  Therefore, the question is, ‘have the daily returns for MKT been far enough above zero for the investor to be confident that they will not go below this level?’  Test to see whether the daily returns on MKT are less than or equal to zero using a level of significance of a = 0.05.  That is, conduct the following test, Ho: m <= 0, H1: m >0.  Would she invest further in the Australian share market using this rule?
  • Test to see whether the daily returns on MKT are normally distributed using the Jarque-Bera test. This should be done in Excel, by calculating the Jarque-Bera statistic using the formulas from the notes and Excel’s Data Analysis/Descriptive Statistics.[2]  Using the results of this test, comment on the accuracy of the probabilities you calculated in parts (a) and (b) of this question.

(3     marks)

QUESTION 2

  • Suppose a hypothetical individual’s Utility (U) can be explained through their consumption of two goods (X1 & X2) such that:

 

Use linear regression to estimate this individual’s utility function which is represented by U = b0X1b1X2b2.  Do this in excel using the matrix formulas =  (X¢X)-1X¢y for the coefficients and V() = 2(X¢X)-1 for the standard errors.  Check your answers using excels regression function.

 

  • With the utility function estimated in part (a) and the budget constraint: (i.e. total budget is $2,000 and the price of X1 is $6.5 and the price of X2 is $4.5), use the Lagrangian function to find the optimal values of X1 and X2.  That is, find the values of X1 and X2 that maximise this individual’s utility subject to the budget constraint[3].  What is the value of l and what is its interpretation?

 

Check your answer using the solver in excel.  To do this open a new spread sheet and insert names for X1 and X2 in cells B2 and C2 and the starting values for these variables of 1 in both cells B3 and C3 and name these cells Xone and Xtwo.   In cell B6 type the formula for the utility function, = b0(Xone^b1)*(Xtwo^b2) and name this UU.  In cell B9 type the formula for the budget, =6.5*Xone+4.5*Xtwo and name this BB.  Next, go to the solver and set the target cell UU equal to maximum by changing Xone and Xtwo.  Then add the constraint that BB = 2000, then solve.  When given the solver results ask for the sensitivity report as this gives l.

(6 marks)

 

 

QUESTION 3 (please use EXCEL for this question).

Although the conclusion from QUESTION 1 may have been to not put additional funds into the Australian share market, with the lower value of the Australian dollar the investor is ‘bullish’ about the 32 Australian listed Basic Materials shares.  Therefore, she decides that a portfolio of these types of shares could present profit opportunities in the future.  Using data on the Unadjusted Share Prices and the same sample period as earlier, follow the instructions below to construct an efficient frontier for the proportions of your funds that need to be allocated to the different Australian Basic Materials shares.  There are additional instructions in Mathematical Programming notes.

 

Calculate the average continuous daily returns, then convert to average yearly returns by multiplying each by your sample period (n).  Transpose this block of cells and name the average returns Ret.  Use the covariance command from EXCEL’s Data Analysis Tools to find the variance-covariance matrix for the daily returns.  This matrix is symmetrical, so the missing elements can be easily filled in.  Name this matrix Mvac.  Convert Mvac into the variance-covariance matrix for yearly returns by highlighting the cells and entering =n*Mvac [Ctrl]+[Shft]+[Enter]. Name this block Vac.  Enter the initial guesses for the optimal weights for the shares and name this block of cells Wts.  Transpose these weights and name this block Twts.  Find the expected return for the portfolio using =MMULT (Wts, Ret) [Ctrl]+[Shft]+[Enter].  Call this cell Pret.  Finding the variance of portfolio returns requires three stages.  First, highlight the appropriate cells and enter =MMULT (Wts,Vac) [Ctrl]+[Shft]+[Enter].  Name this block Tvac.  Second, highlight a single cell and enter =MMULT (Tvac, Twts) [Ctrl]+[Shft]+[Enter].  Call this cell Pvar.  Next, find the portfolio risk, or square root of the portfolio variance and name this cell Prsk.  To ensure that the portfolio weights sum to one, enter 1’s and name this block Unit.  To find the expression for the sum of the weights by entering =MMULT (Unit, Twts) [Ctrl]+[Shft]+[Enter].  Name this cell Wtcn.

 

Using this worksheet and the EXCEL Solver Tool find the minimum risk for the funds allocations for the various expected returns (I suggest performing the exercise for about ten different expected returns, chosen to ensure a solution can be found).  In each case you must constrain the weights so they are non-negative and sum to one.  Use these values to graph the Efficient Frontier with risk on the horizontal axis and returns on the vertical axis.

 

Write a brief report explaining how your portfolio changes as you try different expected returns.

(5 marks)

 

QUESTION 4[4]

One approach to testing the validity of the CAPM is to use the two-pass method.  At the first pass estimate the betas and the variance of the error terms for a number of firms.  This is to be done using the same sample period as in the other questions and the daily returns for the Unadjusted Prices for the Australian Basic Materials firms and the S&P200 Index (MKT).  At the second pass use these results to estimate the Security Market Line (SML).  If the model is a good fit this is interpreted as an indication that the CAPM successfully explains the relationship between Risk and Return.

 

First calculate the continuous returns on all of the Australian Basic Material shares and the market index.

 

Note that the cell references in the instructions below are indicative only; they are for a different sample size and the exact cell references you use will depend on how you construct your own worksheets.

 

At the first pass calculate the systematic risk or betas for all firms with respect the MKT.  Do this by estimating the market model: Rit = b0  +  b1RMt for all firms noting the estimated values of the slopes. To find all the betas go to cell EC64 and enter: =LINEST(EC3:EC62,$EB3:$EB62).  Now highlight EC64 and drag it to cell IR64.  To find the average returns for each firm j, the squared beta values and the variances of the error terms for each market model use the following Excel procedures (note, again the cell references are indicative only).

  • To find all the average returns go to cell EC65 and enter: = AVERAGE(EC3:EC62) Then click this cell and drag it to IR65.
  • To find the squares of the beta values go to cell EC66 and enter: = EC64^2 Then click this cell and drag it to IR66.
  • To find the variances of the error terms in all Market Models use the STEYX function to find their standard deviation and then square this value. Use the STEYX function to enter the Y values and then the X values. To do this, go to cell EC67 and enter the following:            =STEYX(EC3:EC62,$EB3:$EB62)^2  Click this cell and drag it to IR67.
  • Enter the four sets of values for , E(R), 2 and s2 into a separate worksheet. You can call these three variables BETA, ER, BETASQ and VAR. You may need to transpose rows to columns or columns to rows.  Copy the first five values of each of these variables into your submission so your answers can be checked.

For the second pass estimate the SML:  E(R) = g0 + g1 + uj and evaluate your results.

  • What should the values of the intercept and the slope for the SML represent?
  • To help determine whether factors other than systematic risk affect expected returns also estimate the following model: E(R) = g0 + g1 + g22 + g3s2j + uj
  • Interpret the coefficient of determination for both equations and do the same for the adjusted coefficients of determination.
  • Use t-tests to identify the variables which do not have a significant impact on the expected returns, and the variables that do.[5]
  • Briefly discuss your results.

(5 marks)

 

QUESTION 5

The investor would like to further investigate the 32 Australian Basic Materials shares.  She does this using the share returns, the MKT return and the Betas calculated in QUESTION 4, as well as the most recent AUSTRALIA BOND YIELD 10 Y – MIDDLE RATE as a measure of the risk-free rate (Rf).  Present the answers to parts (a), (b) and (c) of this question in a tabular form (cut and paste from a spreadsheet is fine).

  • Indicate which of these Basic Materials shares have outperformed, and which has underperformed the MKT?
  • Conduct hypothesis tests on the Betas of each share against unity to indicate whether the shares are ’passive’, ‘aggressive’ or ‘neutral’. This requires finding the standard error of the slope coefficients for each of your 32 market models.  To do this:
    • Square root the variances of your error terms to find the standard errors of regression for the market models.
    • Divide these standard errors of regression by the standard deviation of the market returns multiplied by the square root of the sample size. This will give you the standard error of each slope coefficient, which can be used to conduct your hypothesis tests.
  • Using the Security Market Line; E(Ri) = Rf + [E(RM) – Rf ]bi assuming the share performance over the last 12-months is the best indicator of what is likely to happen in the future, obtain the expected returns for each of the Basic Materials shares[6].
  • Drawing on information from QUESTIONS 1, 3, 4 & 5 write a brief paragraph discussion the prospects for Australian Basic Materials share performance.

(6 marks)

[1] She is only able to calculate returns from 20 February 2018.

[2] Note that when Excel calculates kurtosis it subtracts 3 from the value.

[3] Please do this algebraically, although there is no need to check the second order conditions. Also, it is fine to work with rounded numbers even though this will make the solution slightly inaccurate.

[4] Refer to the course notes: Mathematical Programming, Simple and Multiple Regression and Finance Applications for additional help.

[5] Use a level of significance of a = 0.05 for all tests.

[6] Be careful to ensure to use only data of the same frequency.