Asgn7_Portfolio1

profileebrahimshirmo
Asgn7_Portfolio1_expoutcome.xlsx
Home>Business & Finance homework help>Accounting homework help>Asgn7_Portfolio1

instruction

Assignment 7: for three stock portfolio, 1. find out portfolio return and risk; display the feasible set 2. Use excel to identify portfolios of the efficient set, and display the efficient set in chart

Exp outcome

Assignment 7: for three stock portfolio, 1. find out portfolio return and risk; display the feasible set 2. Use excel to identify portfolios of the efficient set, and display the efficient set in chart

3stocks Data

RETURNS
Month Stock1 Stock2 Stock3 Stock4 1) Find the expected Return of each stock.  
1 0.061644 0.064894 0.127451 -0.0090215515 Stock1 Stock2 Stock3 Stock4
2 -0.003355 0.09799 0.086957 0.1112672099 1.44% 2.21% 4.09% 1.03% =AVERAGE(B3:B38)
3 0.220779 -0.12357 0.0976 0.0294310999
4 0.018617 0.001044 0.189781 0.0203369848
5 -0.106736 0.086614 -0.018405 -0.0037552357 2) Find the risk of each stock  
6 -0.038123 -0.045894 -0.14875 0.0018846953 Stock1 Stock2 Stock3 Stock4
7 -0.060976 0.001013 0.014706 0.0046787575 7.21% 7.61% 11.01% 2.34% =STDEV.P(B3:B38)
8 0.117273 -0.033079 0.297101 0.024197033 variance 0.0052019 =G9*G9
9 -0.116618 -0.060526 0.090503 -0.0036563071 3) Show the variance-covariance matrix  
10 -0.026403 0.082353 -0.097436 0.0025876264 Stock1 Stock2 Stock3 Stock4
11 -0.080949 -0.140625 -0.0625 0.0007038949 Stock1 0.0052018894
12 0.162963 0.063636 0.122424 0.0047362251 Stock2 -0.0002650352 0.0057841072
13 0.019108 0.068946 0.2 0.0703817791 Stock3 0.0021613314 0.0013724659 0.0121217733
14 0.0785 0.045455 0.126126 -0.0053196128 Stock4 0.0002911302 0.0002008775 0.0006854785 0.0005488217
15 0.011628 0.023018 0.0528 -0.012756444
16 -0.045977 0.1255 0.04943 -0.0029305981
17 -0.053373 0.075724 0.007246 0.0178134046 4) Show the correlation coefficients among the stocks.  
18 0.067093 -0.109731 -0.110647 -0.0169328375 Stock1 Stock2 Stock3 Stock4
19 0.02994 0.030698 0.05668 0.0155332028 Stock1 1
20 0.009535 -0.126697 -0.068965 0.0083271245 Stock2 -0.0483175465 1
21 0.023121 0.080311 0.112099 0.0002607902 Stock3 0.2721809568 0.1639080601 1
22 0.036723 0.005276 0.17037 -0.0137313692 Stock4 0.172302322 0.1127450554 0.2657633678 1
23 0.063433 0.086124 0.164557 0.0258624488
24 0.03856 -0.055066 0.125652 0.0284744889 5) Create potfolio weight table
25 -0.032178 0.049417 0.012077 0.0008351777 Portfolio No. Weight 1 Weight 2 Weight3 Port. Ret Port. Risk
26 0.05954 0.033408 -0.093079 0.0156047899 1 0.00% 0% 100%
27 0.094431 0.019397 0.069263 0.0193500678 2 0.00% 20% 80%
28 -0.022124 0.173784 0.137931 -0.0137030469 3 0.00% 40% 60%
29 -0.001267 0.034296 0.116883 0.0053121935 4 0.00% 60% 40%
30 -0.018182 0.045375 0.00062 0.0046337696 5 0.00% 80% 20%
31 -0.053241 0.028715 -0.112403 0.0027917139 6 0.00% 100% 0%
32 0.079315 0.069919 -0.117904 0.0164615695 7 20.00% 0% 80%
33 -0.084091 -0.039514 0.109703 0.0116302147 8 20.00% 20% 60%
34 0.037221 0.160127 0 0.01047634 9 20.00% 40% 40%
35 -0.044402 -0.068306 -0.09375 -0.0062128684 10 20.00% 60% 20%
36 0.075377 0.043988 -0.142069 0.0070331731 11 20.00% 80% 0%
12 40.00% 0% 60%
13 40.00% 20% 40%
14 40.00% 40% 20%
15 40.00% 60% 0%
16 60.00% 0% 40%
17 60.00% 20% 20%
18 60.00% 40% 0%
19 80.00% 0% 20%
20 80.00% 20% 0%
21 100.00% 0% 0%

Instruction: 1) Find the expected Return of each stock.   2) Find the risk of each stock   3) Show the variance-covariance matrix   4) Show the correlation coefficients among the stocks.   5) Create potfolio weight table 6) Find the feasible set.   7) Find the efficient set 8) Graph the feasible set and efficient set on the same chart 

image1.png

image2.png

image3.png