A financial analyst recorded the quarterly returns on investment for three shares and stored the data in file 2RETURN.xlsx. Using the data and performing the
A financial analyst recorded the quarterly returns on investment for three shares and stored the data in file 2RETURN.xlsx. Using the data and performing the calculations in EXCEL, answer the following questions.
A. a Calculate the mean and variance of each of the three shares.
b If you wish to construct a portfolio that maximises the expected return, what should you do?
c If you wish to construct a portfolio that minimises the risk, what should you do?
B. a Find the expected value and variance of the following portfolio:
Share 1 30%
Share 2 40%
Share 3 30%
b How do the expected value and variance of the portfolio compare with those of Part A?
C. a Find the expected value and variance of the following portfolio:
Share 1 10%
Share 2 10%
Share 3 80%
b How do the expected value and variance of the portfolio compare with those of Part A and Part B(a)?
AutoSave off) A 2RETURN(1) Protected View Search (Alt+O) File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. A62 EX fx A B D E F G H I J Share 3 1 Share 1 Share 2 -0.2959 0.5944 0.2228 2 3 0.0607 0.4545 0.0628 4 0.4003 0.0030 0.1335 5 -0.1818 -0.5507 -0.0987 6 0.3071 -0.3623 -0.0548 7 0.0629 0.3712 0.1802 8 0.5199 0.5047 0.3055 9 0.3579 0.2495 -0.3554 0.2288 10 0.0049 0.0067 11 -0.0397 -0.0851 0.4816 12 0.5301 0.1387 -0.0927 13 -0.1779 -0.5965 0.0864 14 -0.1145 -0.3814 -0.2167 15 -0.3430 1.5190 -0.0094 16 -0.0248 0,2094 0.0105 17 0.5028 0.0194 -0.0297 18 -0.0947 0.2687 -0.3641 19 -0.5085 -0.1298 0.1403 20 -0.1621 0.2333 -0.1149 21 0.1623 -0.2483 -0.1225 22 0.5064 0.7246 0.1448 23 -0.3709 0.3494 0.0444 24 -0.3821 0.4173 0.2888 0.3951 25 0.2513 -0.8652 26 0.3785 -0.6029 0.1946 27 -0.1119 0.9981 0.1272 28 0.2345 0.3143 -0.1181 29 0.2460 -0.1193 0.3486 30 0.2358 -0.3491 -0.0371 31 0.0100 -0.1242 0.1270 1000 ASTAR Sheet1 Sheet2 Sheet2 Sheet3 4 Ready Enable Editing K L M N O P Yash Gupta YG Q |||| R Comments S + Share x T -+ 116% AutoSave off) A 2RETURN(1) Protected View Search (Alt+O) File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. A62 X fx C A B E F G H I J D 0.1270 31 0.0100 -0.1242 32 -0.4669 -0.1542 -0.3715 33 0.6015 -0.0245 -0.5770 34 0.4461 -0.7783 -0.1586 0.3880 35 -0.2756 0.3921 36 0.2293 -0.4695 -0.4089 37 0.0336 -0.8110 -0.0374 38 -0.5268 0.1374 0.0127 39 -0.4701 0.0277 -0.3436 41 40 0.5468 -0.3929 0.1720 0.0834 -0.0338 0.0574 -0.3294 0.4740 0.2932 -0.0491 42 43 0.1541 -0.2755 44 -0.1973 0.7629 0.0108 45 -0.3449 -0.0599 -0.1525 -0.1256 0.7609 0.3817 46 47 0.2029 -0.3695 -0.0220 48 -0.4261 -0.2352 -0.2992 49 0.1148 0.2329 -0.3049 50 0.1218 0.8910 0.2914 51 0.4797 0.1115 0.2674 52 0.0743 0.2128 -0.2199 53 -0.3985 -0.0332 0.1636 54 -0.2809 0,3808 -0.0504 55 0.3704 0.4480 0.2167 56 -0.2501 -0.3872 -0.1814 57 0.0869 0.0242 -0.1399 58 -0.1084 -0.0641 -0.0282 59 0.0654 0.8207 -0.0070 60 -0.1901 0.0511 0.1183 61 0.2085 -0.9963 -0.1151 col Sheet1 Sheet2 Sheet3 4 > Ready + Enable Editing K L M N O P Yash Gupta YG Q |||| R Comments S + Share x T -+ 116% AutoSave off) A 2RETURN(1) Protected View Search (Alt+O) File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. A62 EX fx A B D E F G H I J Share 3 1 Share 1 Share 2 -0.2959 0.5944 0.2228 2 3 0.0607 0.4545 0.0628 4 0.4003 0.0030 0.1335 5 -0.1818 -0.5507 -0.0987 6 0.3071 -0.3623 -0.0548 7 0.0629 0.3712 0.1802 8 0.5199 0.5047 0.3055 9 0.3579 0.2495 -0.3554 0.2288 10 0.0049 0.0067 11 -0.0397 -0.0851 0.4816 12 0.5301 0.1387 -0.0927 13 -0.1779 -0.5965 0.0864 14 -0.1145 -0.3814 -0.2167 15 -0.3430 1.5190 -0.0094 16 -0.0248 0,2094 0.0105 17 0.5028 0.0194 -0.0297 18 -0.0947 0.2687 -0.3641 19 -0.5085 -0.1298 0.1403 20 -0.1621 0.2333 -0.1149 21 0.1623 -0.2483 -0.1225 22 0.5064 0.7246 0.1448 23 -0.3709 0.3494 0.0444 24 -0.3821 0.4173 0.2888 0.3951 25 0.2513 -0.8652 26 0.3785 -0.6029 0.1946 27 -0.1119 0.9981 0.1272 28 0.2345 0.3143 -0.1181 29 0.2460 -0.1193 0.3486 30 0.2358 -0.3491 -0.0371 31 0.0100 -0.1242 0.1270 1000 ASTAR Sheet1 Sheet2 Sheet2 Sheet3 4 Ready Enable Editing K L M N O P Yash Gupta YG Q |||| R Comments S + Share x T -+ 116% AutoSave off) A 2RETURN(1) Protected View Search (Alt+O) File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. A62 X fx C A B E F G H I J D 0.1270 31 0.0100 -0.1242 32 -0.4669 -0.1542 -0.3715 33 0.6015 -0.0245 -0.5770 34 0.4461 -0.7783 -0.1586 0.3880 35 -0.2756 0.3921 36 0.2293 -0.4695 -0.4089 37 0.0336 -0.8110 -0.0374 38 -0.5268 0.1374 0.0127 39 -0.4701 0.0277 -0.3436 41 40 0.5468 -0.3929 0.1720 0.0834 -0.0338 0.0574 -0.3294 0.4740 0.2932 -0.0491 42 43 0.1541 -0.2755 44 -0.1973 0.7629 0.0108 45 -0.3449 -0.0599 -0.1525 -0.1256 0.7609 0.3817 46 47 0.2029 -0.3695 -0.0220 48 -0.4261 -0.2352 -0.2992 49 0.1148 0.2329 -0.3049 50 0.1218 0.8910 0.2914 51 0.4797 0.1115 0.2674 52 0.0743 0.2128 -0.2199 53 -0.3985 -0.0332 0.1636 54 -0.2809 0,3808 -0.0504 55 0.3704 0.4480 0.2167 56 -0.2501 -0.3872 -0.1814 57 0.0869 0.0242 -0.1399 58 -0.1084 -0.0641 -0.0282 59 0.0654 0.8207 -0.0070 60 -0.1901 0.0511 0.1183 61 0.2085 -0.9963 -0.1151 col Sheet1 Sheet2 Sheet3 4 > Ready + Enable Editing K L M N O P Yash Gupta YG Q |||| R Comments S + Share x T -+ 116%
