Reference no: EM13478733
PART 1
|
weekly closing price
|
|
|
weekly return
|
|
|
|
date
|
CBA.AX
|
PWR
|
DJS.AX
|
ASX 200
|
CBA.AX
|
PWR
|
DJS.AX
|
ASX 200
|
|
9/01/2012
|
49.94
|
22.04
|
2.37
|
4181
|
0.0086851
|
0.014733
|
-0.028689
|
0.0176463
|
|
3/01/2012
|
49.51
|
21.72
|
2.44
|
4108.5
|
0.0058919
|
0.0083565
|
0.0295359
|
0.012794
|
|
28/12/2011
|
49.22
|
21.54
|
2.37
|
4056.6
|
-0.011845
|
-0.0146386
|
-0.024691
|
-0.02024
|
|
19/12/2011
|
49.81
|
21.86
|
2.43
|
4140.4
|
0.0138408
|
0.0454328
|
-0.116364
|
-0.00452
|
|
12/12/2011
|
49.13
|
20.91
|
2.75
|
4159.2
|
0.0059378
|
0.0009574
|
0.0299625
|
-0.010421
|
|
5/12/2011
|
48.84
|
20.89
|
2.67
|
4203
|
-0.014329
|
0.0116223
|
-0.076125
|
-0.019823
|
|
28/11/2011
|
49.55
|
20.65
|
2.89
|
4288
|
0.0916501
|
0.0943296
|
0.0783582
|
0.0762242
|
|
21/11/2011
|
45.39
|
18.87
|
2.68
|
3984.3
|
-0.0490258
|
-0.0253099
|
-0.154574
|
-0.046134
|
|
14/11/2011
|
47.73
|
19.36
|
3.17
|
4177
|
-0.0375076
|
0.0104384
|
-0.033537
|
-0.027813
|
|
7/11/2011
|
49.59
|
19.16
|
3.28
|
4296.5
|
0.0114216
|
-0.1255135
|
0.0218069
|
0.0035972
|
|
31/10/2011
|
49.03
|
21.91
|
3.21
|
4281.1
|
-0.0186149
|
0.0101429
|
-0.088068
|
-0.016585
|
|
24/10/2011
|
49.96
|
21.69
|
3.52
|
4353.3
|
0.0506835
|
0.0637567
|
0.1139241
|
0.0510394
|
|
17/10/2011
|
47.55
|
20.39
|
3.16
|
4141.9
|
0.0010526
|
0.0164506
|
0.0639731
|
-0.015146
|
|
10/10/2011
|
47.5
|
20.06
|
2.97
|
4205.6
|
0.027027
|
0.0437045
|
0.0102041
|
0.0102573
|
|
3/10/2011
|
46.25
|
19.22
|
2.94
|
4162.9
|
0.0153677
|
0.0228845
|
0.0243902
|
0.0384922
|
|
26/09/2011
|
45.55
|
18.79
|
2.87
|
4008.6
|
0.0512347
|
-0.0126117
|
0.1434263
|
0.0270035
|
|
19/09/2011
|
43.33
|
19.03
|
2.51
|
3903.2
|
-0.0493638
|
0.0106213
|
-0.019531
|
-0.059334
|
|
12/09/2011
|
45.58
|
18.83
|
2.56
|
4149.4
|
-0.0385995
|
0.0590551
|
-0.082437
|
-0.010799
|
|
5/09/2011
|
47.41
|
17.78
|
2.79
|
4194.7
|
-0.0023148
|
-0.0089186
|
-0.003571
|
-0.01136
|
|
29/08/2011
|
47.52
|
17.94
|
2.8
|
4242.9
|
0.0031666
|
-0.0099338
|
0.0332103
|
0.0102143
|
|
22/08/2011
|
47.37
|
18.12
|
2.71
|
4200
|
0.0306789
|
0.1143911
|
0.0423077
|
0.0019323
|
|
15/08/2011
|
45.96
|
16.26
|
2.6
|
4191.9
|
-0.053542
|
-0.0818746
|
0.04
|
0.0046254
|
|
8/08/2011
|
48.56
|
17.71
|
2.5
|
4172.6
|
0.049719
|
0.0207493
|
-0.056604
|
0.0163687
|
|
1/08/2011
|
46.26
|
17.35
|
2.65
|
4105.4
|
-0.0610919
|
-0.0631749
|
-0.070175
|
-0.072142
|
|
25/07/2011
|
49.27
|
18.52
|
2.85
|
4424.6
|
-0.0247427
|
-0.0613279
|
-0.059406
|
-0.038736
|
|
18/07/2011
|
50.52
|
19.73
|
3.03
|
4602.9
|
0.0305998
|
0.0313643
|
0.0133779
|
0.0289259
|
|
11/07/2011
|
49.02
|
19.13
|
2.99
|
4473.5
|
-0.0531196
|
-0.0668293
|
-0.231362
|
-0.038928
|
|
4/07/2011
|
51.77
|
20.5
|
3.89
|
4654.7
|
-0.0028891
|
-0.0019474
|
0.040107
|
0.0138308
|
|
27/06/2011
|
51.92
|
20.54
|
3.74
|
4591.2
|
0.0154508
|
0.0554985
|
-0.057935
|
0.0184335
|
|
20/06/2011
|
51.13
|
19.46
|
3.97
|
4508.1
|
0.0325121
|
0.0345561
|
0.039267
|
0.0051729
|
|
14/06/2011
|
49.52
|
18.81
|
3.82
|
4484.9
|
-0.0014116
|
0.0037353
|
-0.020513
|
-0.016922
|
|
6/06/2011
|
49.59
|
18.74
|
3.9
|
4562.1
|
0.0016158
|
-0.0173047
|
0.0025707
|
-0.004582
|
|
30/05/2011
|
49.51
|
19.07
|
3.89
|
4583.1
|
0.2084452
|
-0.0265442
|
-0.058111
|
-0.021541
|
|
23/05/2011
|
40.97
|
19.59
|
4.13
|
4684
|
-0.2146828
|
-0.0075988
|
-0.016667
|
-0.010186
|
|
16/05/2011
|
52.17
|
19.74
|
4.2
|
4732.2
|
0.0081159
|
0.0005068
|
-0.016393
|
0.0044148
|
|
9/05/2011
|
51.75
|
19.73
|
4.27
|
4711.4
|
-0.0167205
|
0.0076609
|
-0.004662
|
-0.006662
|
|
2/05/2011
|
52.63
|
19.58
|
4.29
|
4743
|
-0.020108
|
-0.0968635
|
-0.031603
|
-0.016628
|
|
27/04/2011
|
53.71
|
21.68
|
4.43
|
4823.2
|
0.0103461
|
0.0231241
|
-0.030635
|
-0.018438
|
|
18/04/2011
|
53.16
|
21.19
|
4.57
|
4913.8
|
0.0180008
|
0.0100095
|
0.0178174
|
0.0127161
|
|
11/04/2011
|
52.22
|
20.98
|
4.49
|
4852.1
|
-0.0165725
|
-0.0683837
|
0.0181406
|
-0.017913
|
|
4/04/2011
|
53.1
|
22.52
|
4.41
|
4940.6
|
0.0068259
|
-0.0118473
|
0.0022727
|
0.016208
|
|
28/03/2011
|
52.74
|
22.79
|
4.4
|
4861.8
|
0.0169688
|
0.0124389
|
0.0185185
|
0.0251339
|
|
21/03/2011
|
51.86
|
22.51
|
4.32
|
4742.6
|
0.0306041
|
0.0392428
|
0.0261283
|
0.0251167
|
|
14/03/2011
|
50.32
|
21.66
|
4.21
|
4626.4
|
-0.0106174
|
-0.0304387
|
0.0047733
|
-0.003961
|
|
7/03/2011
|
50.86
|
22.34
|
4.19
|
4644.8
|
-0.0339981
|
-0.0071111
|
-0.021028
|
-0.045125
|
|
28/02/2011
|
52.65
|
22.5
|
4.28
|
4864.3
|
-0.0082878
|
0.0098743
|
0.0118203
|
0.005748
|
|
21/02/2011
|
53.09
|
22.28
|
4.23
|
4836.5
|
-0.0204797
|
-0.0614996
|
-0.023095
|
-0.020297
|
|
14/02/2011
|
54.2
|
23.74
|
4.33
|
4936.7
|
-0.0166909
|
-0.0100083
|
0.053528
|
0.0114323
|
|
7/02/2011
|
55.12
|
23.98
|
4.11
|
4880.9
|
0.038824
|
0.0165324
|
-0.059497
|
0.0037428
|
|
31/01/2011
|
53.06
|
23.59
|
4.37
|
4862.7
|
0
|
0
|
0
|
0
|
|
Expected Return
|
0.0001656
|
-0.0003573
|
-0.010323
|
|
Standard Deviation
|
0.0524833
|
0.0450622
|
0.0629435
|
|
Variance
|
|
0.0028119
|
0.0020729
|
0.0040444
|
|
|
|
|
|
|
COVARIANCE
|
|
|
|
|
CBA.AX
|
PWR
|
DJS.AX
|
ASX 200
|
|
CBA.AX
|
0.002754497
|
|
|
|
|
PWR
|
0.000696669
|
0.002031
|
|
|
|
DJS.AX
|
0.000852572
|
0.000608
|
0.003962
|
|
|
ASX 200
|
0.000624687
|
0.000573
|
0.00102
|
0.000718
|
|
|
|
|
|
|
CORRELATION
|
|
|
|
|
CBA.AX
|
PWR
|
DJS.AX
|
ASX 200
|
|
CBA.AX
|
1
|
|
|
|
|
PWR
|
0.294573285
|
1
|
|
|
|
DJS.AX
|
0.258082823
|
0.214269
|
1
|
|
|
ASX 200
|
0.444220925
|
0.474876
|
0.604982
|
1
|
|
|
|
|
|
|
|
|
|
|
|
stock
|
utility
|
|
|
|
|
CBA.AX
|
-0.00258895
|
|
A=
|
2
|
|
PWR
|
-0.00238794
|
|
|
|
|
DJS.AX
|
-0.01428537
|
|
|
|
Question:
Your first task is to select three stocks of your choice that are listed on the stock exchanges in one of the following countries: Australia, Singapore, Hong Kong or Malaysia. The stocks should be chosen from different industry groups. Visit the Yahoo Finance website or any other source that provides historical stock price data. Extract the weekly closing share prices (for example every Friday) for the most recent 52 weeks (period ending 1 July 2011 or later). Extract also the weekly closing values of the corresponding stock market index (i.e, the ASX 200 in Australia, the Straits Times Index in Singapore, the Hang Seng Index in Hong Kong, or the Kuala Lumpur Composite Index in Malaysia - choose only ONE) over the same time period. Using this data, calculate the weekly returns for each stock and the index. (5 marks)
Using the weekly returns, calculate and explain the following:
- mean return;
- standard deviation and variance;
- covariance between pairs of stocks, and between each stock and the market index; and,
- correlation coefficient for pairs of stocks, and for each stock and the index.
Based on your calculations, prepare an assessment of the risk-return profiles for each stock and the market index. Use charts or graphs as you deem necessary to augment your discussion (10 marks).
Assume your utility function is given by: U = E(r) - 1/2 A 2 (where A is your degree of risk aversion). Based on your calculations for the three stocks, which stock is you preferred investment? (You may choose a value for A assuming that the value of A can be between 1 and 5).
PART 2
|
mean return
|
-0.003505094
|
-0.002183719
|
0.0001656
|
-0.002547
|
|
standard deivation
|
0.038489618
|
0.037080018
|
0.0530272
|
0.0371527
|
|
variance
|
0.001481451
|
0.00137493
|
0.002812
|
0.00138
|
|
Portfolio weights
|
|
|
|
|
|
|
|
|
CBA.AX
|
PWR
|
DJS.AX
|
Constraint
|
VAR R(p)
|
|
|
|
|
0.301746
|
0.499158
|
0.199095861
|
0.999999991
|
0.32999999
|
|
|
|
|
|
|
|
|
|
|
|
|
|
weight efficient
|
|
|
|
|
|
|
|
cba
|
pwr
|
djs
|
con1
|
con2
|
var
|
|
|
|
1
|
0
|
0
|
1
|
40917
|
0.33
|
|
|
|
|
|
|
|
|
|
|
|
|
weight optimal
|
|
|
|
|
|
|
|
cba
|
pwr
|
djs
|
Con
|
er
|
var
|
sd
|
slope
|
|
0.283147
|
0.482338
|
0.234515721
|
1
|
-0.00255
|
0.001371
|
0.3703
|
-2.57
|
|
Stocks
|
Expected Return
|
VAR
|
COV
|
PAIR
|
Rf=
|
0.092706
|
|
CBA.AX
|
0.0001656
|
0.0028119
|
0.000696669
|
1,2
|
|
|
|
PWR
|
-0.0003573
|
0.0020729
|
0.000852572
|
1,3
|
|
|
|
DJS.AX
|
-0.010323
|
0.0040444
|
0.000608
|
2,3
|
|
|
|
risk-free rate =
|
0.092706
|
Question :
This section requires you to construct a portfolio using the stocks you selected in Part 1.
Combine the stocks using equal weights. Calculate the mean return and variance for your portfolio. Compare these with the returns and variances of your individual stocks. What do you observe? Explain using concepts you have learnt in the course.
Once you have done this, extract the average weekly Treasury bill rate (a proxy for the risk free rate) from the financial data sources in the country that you chose. (For example, if you selected Australia, you can use the Treasury bill rate, or equivalently the 180-day bank accepted bill (BAB) rate, which are available from the Reserve Bank of Australia website:
http: //www.rba.gov.au/statistics/tables/index.html#interest_rates) over your sample period. Note that reported yields are usually annualised figures so you will need to convert the yields to weekly numbers before calculating the average rate. Use this rate as a proxy for the risk-free rate.
Your next task is to calculate the minimum variance portfolio and the efficient frontier of risky portfolios. To do this you are encouraged to make use of the Solver tool in MS Excel. Refer to Chapter 7 appendix A in the textbook to assist you.
Plot the minimum variance portfolio and the efficient frontier of risky portfolios, along with the individual stocks, the market index and the equal weighted portfolio on a graph.
Determine the weights for the tangent portfolio (optimal risky portfolio) along with its risk and return. What do you observe? Explain using concepts you have learnt in the course. (25 marks)
PART 3
This part of your assignment requires you to undertake security risk analysis.
For each of your three stocks, estimate a 'Security Characteristic Line' using the regression tool in MS Excel. Remember to use excess returns based on the data you collected in Part 1, and the risk-free rate you extracted in Part 2.
Based on the regression output, comment on the risk profile of each stock and make comparisons between them. Your discussion should include, but not be restricted to the following: beta; total risk (variance); systematic risk; and non-systematic risk.