Roland is considering investing in four different two-factor portfolios. He can obtain the monthly returns of securities A, B, C and D for the years 2012 to 2017 (The attached file (Stock returns 2012-17) gives 61 monthly returns for three securities along with the returns of the NZX all share index for the period from April 2012 to April 2017).

In any of the possible two-factor portfolios, the weight of each security in the portfolio will be 50%. The four possible portfolio combinations are A&B; B&C; A&C; C&D; A&D and B&D.

Required:

Determine, using the appropriate Excel function (see f_x) the average monthly return, the standard deviation, and variance for each of the companies. (Use the 61months returns data in the calculations and use the Excel functions identified as Variance P and Standard Deviation P.) (4 marks)

Determine, using the appropriate Excel function the covariance between securities A&B; B&C; A&C; C&D; A&D and B&D. (Use the 61 months returns data in the calculations and use the Excel function COVARIANCE P.)

(4 marks)

Calculate using the two-factor portfolio equations, the portfolio returns and risks (both standard deviation and variance) for the following portfolios:

A and B

B and C

A and C

C and D

A and D

B and D (12 marks)

By computing the ratio of () select the best investment that Roland should undertake, assuming Roland is risk averse. Explain the rationale that you have used in making this choice.

(4 marks)

Draw a portfolio graph (showing Risk on the X-axis and Return on the Y-axis) for the investment portfolio that you have recommended in (d) above for a range of investment weights that Roland could choose from (i.e. Roland could invest 0% in one company and 100% in the other or 5% in one company and 95% in the other and so on). Determine from the portfolio graph, the minimum risk that Roland could obtain for this portfolio and the respective weightings that should be invested in each of the securities in the portfolio.

(6 marks)

Determine the betas for Securities A and B, which are trading companies, security C, a company in the healthcare sector, and security D, a power generation company by regressing the returns of each of the companies on the returns for the NZX ALL Share Index (the first column in the spreadsheet).

(Regression Calculation: Go to Data Analysis - far right under Data- and choose regression. If Data Analysis does not appear it must be added, it will be available in Excel. Go to Options under File and choose Add-ins and then Data Analysis the company returns constitute the Y input and the index returns the X input. Alternatively, the slope found in f(x) also represents Beta).

Explain what the values of the betas (the slope coefficients in the regression) indicate and discuss the factors that might explain the differences in the values of the betas of the three companies.

(10 marks)

Should Roland decide to invest in all the four companies in equal proportions? Compute the return and risk of the portfolio?

(5 marks)

Return Data for a sample of companies drawn from NZX from April 2012 to April 2017.
Period	All Share index	Security A	Security B	Security C	Security D
2017/4/1	0.00653	0.01616	-0.01854	-0.02597	0.03093
2017/3/1	0.00409	-0.09898	-0.16111	-0.10465	0.00867
2017/2/1	0.01655	0.04566	-0.10891	-0.01527	0.00523
2017/1/2	0.02464	0.06829	-0.02227	-0.08070	0.04364
2016/12/1	-0.00228	0.04061	0.04133	-0.02730	-0.01434
2016/11/1	-0.00916	-0.04523	0.02268	-0.02333	-0.06376
2016/10/3	-0.05440	-0.11445	-0.04902	0.02389	-0.08025
2016/9/1	-0.00510	-0.03320	0.00493	0.01384	-0.04142
2016/8/1	0.00690	0.04329	0.10929	0.03214	0.00297
2016/7/1	0.06533	0.11774	0.12132	0.01083	0.05477
2016/6/1	-0.02016	0.05802	-0.01449	0.02593	-0.05333
2016/5/2	0.03208	0.04643	0.08661	-0.02174	0.04167
2016/4/1	0.01009	-0.03945	0.07932	-0.04828	-0.01220
2016/3/1	0.08370	0.02460	0.07132	0.08209	0.05466
2016/2/1	0.00983	0.12673	0.04603	0.00000	0.00323
2016/1/1	-0.02436	-0.08348	-0.08430	0.00375	-0.05344
2015/12/1	0.03674	0.14792	0.04242	0.04297	0.06678
2015/11/2	0.01901	-0.03421	-0.06516	-0.06227	-0.01127
2015/10/1	0.07026	0.11685	0.13505	0.09200	0.01471
2015/9/1	-0.01112	0.00451	-0.03864	-0.03846	0.02000
2015/8/3	-0.04471	-0.09221	-0.10014	0.01167	-0.07121
2015/7/1	0.03387	0.11416	-0.00277	-0.01908	0.02377
2015/6/1	-0.02019	-0.03524	-0.11097	-0.09028	-0.04394
2015/5/1	0.00926	0.02483	0.02399	0.04348	0.05769
2015/4/1	-0.00731	-0.00449	-0.04000	0.03759	-0.01887
2015/3/2	-0.00757	0.05701	-0.01434	-0.02206	0.01274
2015/2/2	0.02341	-0.01636	0.07033	-0.04561	-0.00633
2015/1/1	0.03156	0.04136	-0.01263	-0.08654	0.05333
2014/12/1	0.02652	0.14167	0.04901	0.00645	0.07143
2014/11/3	0.00680	0.04348	-0.01565	-0.00322	-0.02269
2014/10/1	0.02527	0.00291	-0.01793	0.01634	0.01237
2014/9/1	0.00608	0.03614	-0.04872	-0.01290	0.17184
2014/8/1	0.01070	-0.04871	-0.01322	-0.02208	-0.03012
2014/7/1	0.00516	-0.04121	0.01711	0.02258	0.01840
2014/6/2	-0.00714	0.03409	-0.00244	-0.10145	0.00000
2014/5/1	-0.01037	-0.03030	-0.09592	0.01770	0.06304
2014/4/1	0.01804	0.01397	0.01454	0.05280	0.01770
2014/3/3	0.03005	0.01994	0.01361	-0.07736	0.02727
2014/2/3	0.02369	0.03846	0.03887	-0.03324	0.00000
2014/1/1	0.02904	-0.00295	0.09832	-0.03476	-0.03084
2013/12/2	-0.01208	0.07619	-0.05732	0.01081	-0.05417
2013/11/1	-0.02338	0.05705	-0.05639	0.00543	-0.04000
2013/10/1	0.02656	0.01706	0.02962	0.00546	0.01010
2013/9/2	0.05323	0.10150	0.10907	-0.02918	0.04211
2013/8/1	0.00066	-0.06667	0.06137	-0.03827	-0.04427
2013/7/1	0.02203	0.13546	0.00420	0.10112	0.11937
2013/6/3	-0.01578	-0.00791	0.01709	-0.07532	-0.02632
2013/5/1	-0.02233	-0.01172	-0.03704	-0.02532	-0.01085
2013/4/1	0.04333	0.08475	0.07681	0.13181	-0.04357
2013/3/1	0.04939	0.03509	-0.08266	0.03561	0.01474
2013/2/1	0.00352	0.00000	-0.03906	0.04012	-0.03455
2013/1/1	0.03278	0.05069	0.15663	0.08361	0.08370
2012/12/3	0.00405	0.04327	0.06070	-0.01967	-0.02366
2012/11/1	0.02330	-0.02347	0.12186	-0.03175	0.02876
2012/10/1	0.03227	0.01914	0.00180	0.07143	0.04388
2012/9/3	0.04567	0.02956	0.07946	0.05000	0.03589
2012/8/1	0.03432	0.05455	0.09787	0.07692	0.00000
2012/7/2	0.04270	0.00785	0.02174	0.04000	0.02956
2012/6/1	-0.02536	-0.04500	-0.05155	-0.00794	-0.01456
2012/5/1	-0.01901	0.00000	-0.01222	-0.05618	0.08707
2012/4/2	0.03936	0.04167	0.02720	0.02299	0.02989