Question: (a) Calculate each stocks average monthly return, its return variance and standard deviation and beta over the period 07/2015 to 05/2020, using the price information
(a) Calculate each stocks average monthly return, its return variance and standard deviation and beta over the period 07/2015 to 05/2020, using the price information of the FTSE All Share Index as a proxy for market portfolio. Summarize your results in a table, and briefly describe how these are calculated (Please do not use Excel functions as your explanation). (9 marks) (b) Construct three types of portfolios. Portfolio A consists of any 3 of the stocks, Portfolio B consists of any 6 of the stocks, and Portfolio C consists of all 10 stocks. Assuming that each stock has equal weight in the portfolio, calculate Portfolio A, B, and Cs average monthly returns, return variances and standard deviations, and betas. Summarize your results in a table, and briefly describe how these are calculated (Please do not use Excel functions as your explanation). (8 marks) (c) If you rank portfolio A, B and C according to their betas and return variances respectively, will the three portfolios have same ranking? Can you provide economic reasons as to why the two rankings should be (in)consistent with each other? (8 marks)
| Date | Unilever PLC (ULVR.L) | easyJet plc (EZJ.L) | Vodafone Group Plc (VOD.L) | BP p.l.c. (BP.L) | Rio Tinto plc (RIO.L) | Rolls-Royce Holdings plc (RR.L | GlaxoSmithKline plc (GSK.L) | The Royal Bank of Scotland Group plc (RBS.L) | United Utilities Group PLC (UU.L) | Legal & General Group Plc (LGEN.L) | FTSE All | UK 3 MONTHS TREASURY BILLS YIELD (EP) |
| 01/07/2015 | 2907 | 1644 | 241.85 | 395.45 | 2486.5 | 794 | 1396 | 342.2 | 892 | 260.7 | 3652.79 | 0.469 |
| 01/08/2015 | 2623 | 1683 | 226.7 | 360.5 | 2388 | 743.5 | 1340.5 | 336.8 | 855 | 252.7 | 3434.66 | 0.46 |
| 01/09/2015 | 2686 | 1777 | 208.45 | 334 | 2210 | 677 | 1266 | 315 | 925 | 238.1 | 3335.92 | 0.489 |
| 01/10/2015 | 2893 | 1750 | 214.25 | 386.4 | 2358.5 | 687.5 | 1405 | 317.6 | 988.5 | 261.6 | 3484.6 | 0.478 |
| 01/11/2015 | 2834 | 1653 | 224.25 | 385.25 | 2208.5 | 606.5 | 1355.5 | 302.4 | 966.5 | 271.5 | 3492.13 | 0.483 |
| 01/12/2015 | 2926.5 | 1740 | 221 | 354 | 1979.5 | 575 | 1373 | 302 | 935.5 | 267.8 | 3444.26 | 0.459 |
| 01/01/2016 | 3085 | 1548 | 223.9 | 376.1 | 1714 | 555 | 1439 | 252.7 | 956.5 | 243.5 | 3335.9 | 0.468 |
| 01/02/2016 | 3092 | 1506 | 219.7 | 351.45 | 1904 | 679 | 1399.5 | 223.9 | 925.5 | 227 | 3345.84 | 0.463 |
| 01/03/2016 | 3152.5 | 1519 | 221.2 | 350.15 | 1955.5 | 682 | 1412 | 222.7 | 923 | 235.2 | 3395.19 | 0.468 |
| 01/04/2016 | 3054.5 | 1473 | 219.2 | 373.75 | 2300.5 | 669.5 | 1459 | 230 | 939.5 | 223.1 | 3421.7 | 0.453 |
| 01/05/2016 | 3146 | 1533 | 230.85 | 357.25 | 1942.5 | 619 | 1445 | 246.5 | 970 | 239.4 | 3429.77 | 0.478 |
| 01/06/2016 | 3579 | 1086 | 227.65 | 438.15 | 2294 | 711.5 | 1604.5 | 171.6 | 1035 | 190.7 | 3515.45 | 0.442 |
| 01/07/2016 | 3533.5 | 1041 | 229.55 | 426.85 | 2461.5 | 791 | 1688 | 192.4 | 1017 | 205.7 | 3653.83 | 0.293 |
| 01/08/2016 | 3529 | 1105 | 229.85 | 427.9 | 2301 | 770 | 1639 | 201.5 | 971.5 | 210.3 | 3697.19 | 0.23 |
| 01/09/2016 | 3654.5 | 1007 | 221.75 | 450 | 2574.5 | 720 | 1643 | 178.8 | 1003 | 218.7 | 3755.34 | 0.217 |
| 01/10/2016 | 3418 | 937 | 224.8 | 483.7 | 2839.5 | 726.5 | 1618.5 | 189.1 | 940 | 209.4 | 3768.14 | 0.233 |
| 01/11/2016 | 3196 | 990 | 193.9 | 459.45 | 2990 | 676.5 | 1495.5 | 194.3 | 883 | 235.8 | 3692.4 | 0.203 |
| 01/12/2016 | 3292.5 | 1005 | 199.85 | 509.6 | 3158.5 | 668 | 1562 | 224.6 | 901 | 247.6 | 3873.22 | 0.218 |
| 01/01/2017 | 3227.5 | 950 | 194.35 | 472.85 | 3491 | 667.5 | 1528 | 221.8 | 917 | 234.9 | 3858.26 | 0.226 |
| 01/02/2017 | 3819 | 951 | 201.9 | 453.55 | 3297 | 787 | 1648 | 237.8 | 978.5 | 248.3 | 3953.42 | 0.213 |
| 01/03/2017 | 3939.5 | 1026 | 208.1 | 457.55 | 3209.5 | 754 | 1659.5 | 242.1 | 993.5 | 247.3 | 3990 | 0.217 |
| 01/04/2017 | 3972 | 1168 | 199.05 | 442.5 | 3062 | 812 | 1550 | 265.4 | 973.5 | 246.1 | 3962.49 | 0.165 |
| 01/05/2017 | 4331.5 | 1414 | 231.5 | 466.7 | 3102.5 | 867.5 | 1703.5 | 260 | 1029 | 251.8 | 4116.08 | 0.182 |
| 01/06/2017 | 4155 | 1359 | 217.75 | 442.8 | 3242 | 891 | 1635.5 | 247.2 | 867.5 | 258.3 | 4002.18 | 0.207 |
| 01/07/2017 | 4321.5 | 1236 | 222 | 445.8 | 3520 | 888 | 1514.5 | 248.6 | 897.5 | 268.4 | 4046.2 | 0.153 |
| 01/08/2017 | 4519 | 1196 | 221.4 | 445.15 | 3746.5 | 914 | 1533.5 | 251.9 | 910 | 260.2 | 4072.98 | 0.207 |
| 01/09/2017 | 4319 | 1217 | 208.8 | 477.3 | 3473 | 887 | 1488.5 | 268.3 | 854.5 | 259.9 | 4049.89 | 0.242 |
| 01/10/2017 | 4269.5 | 1339 | 215.6 | 510.2 | 3548.5 | 973 | 1357.5 | 282.5 | 833 | 267 | 4117.69 | 0.369 |
| 01/11/2017 | 4164 | 1406 | 224.3 | 489.1 | 3501.5 | 857 | 1279.5 | 274 | 820.5 | 267.2 | 4033.84 | 0.399 |
| 01/12/2017 | 4125.5 | 1464 | 235 | 522.7 | 3942 | 847 | 1322.5 | 278 | 829.5 | 273.3 | 4221.82 | 0.34 |
| 01/01/2018 | 4000 | 1660 | 224.6 | 501.1 | 3922 | 872 | 1320.4 | 287.7 | 738 | 270.6 | 4137.66 | 0.312 |
| 01/02/2018 | 3733.5 | 1679 | 203.75 | 475.15 | 3926 | 839.6 | 1307.2 | 267.7 | 666.2 | 262.6 | 3981.61 | 0.361 |
| 01/03/2018 | 3955.5 | 1604.5 | 194.22 | 479.25 | 3611 | 871.6 | 1394 | 258.8 | 715.4 | 257.8 | 3894.17 | 0.448 |
| 01/04/2018 | 4077.5 | 1590 | 211.6 | 538 | 3946 | 840 | 1461.4 | 270.2 | 743.2 | 270 | 4127.68 | 0.496 |
| 01/05/2018 | 4146.5 | 1709 | 191.82 | 576.3 | 4240 | 824.6 | 1524 | 275.1 | 775.8 | 269.8 | 4222.2 | 0.447 |
| 01/06/2018 | 4192 | 1673 | 183.82 | 578.3 | 4201 | 988.2 | 1529.8 | 256.1 | 763.2 | 266 | 4202.25 | 0.58 |
| 01/07/2018 | 4355 | 1619 | 186 | 573.3 | 4196.5 | 991 | 1581.6 | 255.4 | 719.2 | 262.7 | 4253.31 | 0.699 |
| 01/08/2018 | 4392 | 1525 | 164.5 | 547.3 | 3659 | 1010 | 1560.4 | 241.3 | 741.6 | 257 | 4106.14 | 0.744 |
| 01/09/2018 | 4216 | 1314 | 164.5 | 589.3 | 3880 | 987.4 | 1536.8 | 250 | 704 | 262.2 | 4127.91 | 0.731 |
| 01/10/2018 | 4146.5 | 1200 | 147.82 | 567.3 | 3803.5 | 839 | 1511.2 | 236.4 | 726.6 | 251.5 | 3904.23 | 0.715 |
| 01/11/2018 | 4243.5 | 1112.5 | 168.94 | 520 | 3559 | 849.6 | 1621.6 | 218.2 | 760.4 | 244.9 | 3823.34 | 0.701 |
| 01/12/2018 | 4108.5 | 1105 | 152.9 | 495.95 | 3730 | 830 | 1491.2 | 216.7 | 736.2 | 231 | 3675.06 | 0.727 |
| 01/01/2019 | 3984.5 | 1264 | 138.58 | 520.1 | 4187.5 | 884.4 | 1477.2 | 240.9 | 830.8 | 259.4 | 3825.62 | 0.698 |
| 01/02/2019 | 4008 | 1227.5 | 134.28 | 534.4 | 4336 | 955 | 1497.4 | 265.7 | 840.2 | 280.6 | 3888.81 | 0.628 |
| 01/03/2019 | 4395 | 1117.5 | 139.8 | 558.5 | 4461 | 903.2 | 1596.8 | 247 | 814.4 | 275.3 | 3978.28 | 0.769 |
| 01/04/2019 | 4657.5 | 1163.5 | 142 | 559 | 4465 | 914.6 | 1573.4 | 239.6 | 828.8 | 278.2 | 4067.98 | 0.752 |
| 01/05/2019 | 4837 | 871.2 | 129.42 | 540.1 | 4536.5 | 860 | 1527 | 213.7 | 797.4 | 256.6 | 3923.87 | 0.731 |
| 01/06/2019 | 4894.5 | 953.2 | 129.32 | 548.6 | 4880.5 | 840.4 | 1576.6 | 219.8 | 782.8 | 269.5 | 4056.88 | 0.74 |
| 01/07/2019 | 4955.5 | 964 | 150.08 | 545.7 | 4698 | 862 | 1702.6 | 217 | 788.6 | 261.8 | 4134.03 | 0.76 |
| 01/08/2019 | 5196 | 963.4 | 155.24 | 500.2 | 4149.5 | 771.2 | 1711.6 | 185.45 | 814 | 219.6 | 3953.02 | 0.752 |
| 01/09/2019 | 4890 | 1150 | 162 | 515.8 | 4209 | 792.4 | 1744.6 | 207.6 | 825.8 | 248.4 | 4061.74 | 0.775 |
| 01/10/2019 | 4622.5 | 1238 | 157.4 | 489.3 | 4014 | 709 | 1768.6 | 212.7 | 870.2 | 263.7 | 3993.46 | 0.76 |
| 01/11/2019 | 4578 | 1338 | 153.36 | 480.2 | 4201.5 | 711.6 | 1754 | 226.2 | 853.4 | 280.9 | 4066.73 | 0.759 |
| 01/12/2019 | 4350.5 | 1424.5 | 146.76 | 471.6 | 4503 | 683.2 | 1779 | 240.3 | 943.4 | 303 | 4196.47 | 0.771 |
| 01/01/2020 | 4533.5 | 1393 | 149.3 | 456.7 | 4084 | 670 | 1784 | 218.3 | 1013.5 | 305.5 | 4057.47 | 0.731 |
| 01/02/2020 | 4170 | 1100.5 | 134.36 | 396.15 | 3608 | 620 | 1562.2 | 178.65 | 944.2 | 260 | 3673.61 | 0.644 |
| 01/03/2020 | 4075.5 | 571 | 113 | 344.2 | 3718.5 | 340.6 | 1514.6 | 112.9 | 902.8 | 193.85 | 3107.42 | 0.274 |
| 01/04/2020 | 4102 | 603.2 | 112.14 | 313.1 | 3684.5 | 330 | 1661 | 110.55 | 902.2 | 204.6 | 3262.51 | 0.083 |
| 01/05/2020 | 4332 | 680 | 133.02 | 305.75 | 4313 | 271.6 | 1671.8 | 110.35 | 917.6 | 198.5 | 3363.67 | 0.08 |
Step by Step Solution
There are 3 Steps involved in it
1 Expert Approved Answer
Step: 1 Unlock
Question Has Been Solved by an Expert!
Get step-by-step solutions from verified subject matter experts
Step: 2 Unlock
Step: 3 Unlock
Study Help