Determine the weights for the tangent portfolio

Question

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.