Question: For this assignment, please use excel file. The file contains the monthly returns of 4 stocks over the 10 year period -- January 1997 --

For this assignment, please use excel file. The file contains the

For this assignment, please use excel file. The file contains the monthly returns of 4 stocks over the 10 year period -- January 1997 -- December 2006. In this file, the expected monthly return for each stock is calculated using excel function AVERAGE, for each stock, the variance of monthly returns is calculated using Excel function VAR, and the covariance between the returns of each pair of stocks is calculated using Excel function COVAR.

Assume that the yearly risk free rate is 2% (A monthly risk free rate of 0.001652).

Find the global minimum variance portfolio. What is the expected return and variance of return of this portfolio?

Date Expected Monthly Return Expected Monthly Return PG 0.010848 Microsoft 0.014854 BAC 0.011589 Exxon 0.012043 PG Microsoft BAC Exxon Variance Variance 0.004478 0.012820 0.005611 0.002820 PG Microsoft BAC Exxon 2/3/97 0.039059 -0.043935 0.109771 -0.025167 3/3/97 -0.044853 -0.05994 -0.070109 0.074816 4/1/97 0.099732 0.325186 0.088252 0.051471 5/1/97 0.096787 0.02085 -0.024705 0.053613 6/2/97 0.024472 0.018853 0.102423 0.033628 7/1/97 0.080347 0.118736 0.102398 0.048801 8/1/97 -0.124958 -0.064783 -0.160399 -0.041224 9/2/97 0.037519 0.000737 0.041554 0.046828 10/1/97 -0.011808 -0.017673 -0.033679 -0.040667 11/3/97 0.120612 0.088456 0.00429 -0.000424 12/1/97 0.047318 -0.086777 0.018687 0.002969 1/2/98 -0.014954 0.1546 -0.015199 -0.030867 2/2/98 0.08301 0.135859 0.143693 0.082024 3/2/98 -0.005965 0.056354 0.070731 0.060887 4/1/98 -0.022802 0.007077 0.042156 0.080578 5/1/98 0.020264 -0.058919 -0.006672 -0.029546 6/1/98 0.085766 0.277427 0.020991 0.012323 7/1/98 -0.125554 0.014388 0.039885 -0.015754 8/3/98 -0.036133 -0.127216 -0.279162 -0.062932 9/1/98 -0.070372 0.147283 -0.063083 0.079193 10/1/98 0.251503 -0.03807 0.074941 0.014029 11/2/98 -0.012154 0.152324 0.133442 0.05321 12/1/98 0.042346 0.136981 -0.07112 -0.024924 1/4/99 -0.001647 0.261679 0.111743 -0.039378 2/1/99 -0.015122 -0.142261 -0.023267 -0.047105 3/1/99 0.094361 0.194093 0.088614 0.06 4/1/99 -0.039286 -0.092688 0.013567 0.177287 5/3/99 -0.00478 -0.007789 -0.096287 -0.033565 6/1/99 -0.056297 0.117754 0.141424 -0.034418 7/1/99 0.031383 -0.048622 -0.0946 0.029164 8/2/99 0.095943 0.078932 -0.088303 -0.001259 9/1/99 -0.055278 -0.021842 -0.072515 -0.03657 10/1/99 0.122319 0.02233 0.156916 -0.025524 11/1/99 0.029724 -0.016316 -0.090737 0.077569 12/1/99 0.014433 0.282236 -0.136175 0.015581 1/3/00 -0.074977 -0.161694 -0.034898 0.028843 2/1/00 -0.130615 -0.086859 -0.050499 -0.086191 3/1/00 -0.354114 0.18888 0.152988 0.035574 4/3/00 0.057826 -0.34365 -0.06549 -0.003782 5/1/00 0 113029 - 103039 141377 0 07814 Covariance Cov(PG, Microsoft) Cov(PG, BAC) Cov(PG, Exxon) Cov(Microsoft, BAC) Cov(Microsoft, Exxon) Cov(BAC, Exxon) -0.000649 0.000683 0.000433 0.001681 0.000804 0.000757 Date Expected Monthly Return Expected Monthly Return PG 0.010848 Microsoft 0.014854 BAC 0.011589 Exxon 0.012043 PG Microsoft BAC Exxon Variance Variance 0.004478 0.012820 0.005611 0.002820 PG Microsoft BAC Exxon 2/3/97 0.039059 -0.043935 0.109771 -0.025167 3/3/97 -0.044853 -0.05994 -0.070109 0.074816 4/1/97 0.099732 0.325186 0.088252 0.051471 5/1/97 0.096787 0.02085 -0.024705 0.053613 6/2/97 0.024472 0.018853 0.102423 0.033628 7/1/97 0.080347 0.118736 0.102398 0.048801 8/1/97 -0.124958 -0.064783 -0.160399 -0.041224 9/2/97 0.037519 0.000737 0.041554 0.046828 10/1/97 -0.011808 -0.017673 -0.033679 -0.040667 11/3/97 0.120612 0.088456 0.00429 -0.000424 12/1/97 0.047318 -0.086777 0.018687 0.002969 1/2/98 -0.014954 0.1546 -0.015199 -0.030867 2/2/98 0.08301 0.135859 0.143693 0.082024 3/2/98 -0.005965 0.056354 0.070731 0.060887 4/1/98 -0.022802 0.007077 0.042156 0.080578 5/1/98 0.020264 -0.058919 -0.006672 -0.029546 6/1/98 0.085766 0.277427 0.020991 0.012323 7/1/98 -0.125554 0.014388 0.039885 -0.015754 8/3/98 -0.036133 -0.127216 -0.279162 -0.062932 9/1/98 -0.070372 0.147283 -0.063083 0.079193 10/1/98 0.251503 -0.03807 0.074941 0.014029 11/2/98 -0.012154 0.152324 0.133442 0.05321 12/1/98 0.042346 0.136981 -0.07112 -0.024924 1/4/99 -0.001647 0.261679 0.111743 -0.039378 2/1/99 -0.015122 -0.142261 -0.023267 -0.047105 3/1/99 0.094361 0.194093 0.088614 0.06 4/1/99 -0.039286 -0.092688 0.013567 0.177287 5/3/99 -0.00478 -0.007789 -0.096287 -0.033565 6/1/99 -0.056297 0.117754 0.141424 -0.034418 7/1/99 0.031383 -0.048622 -0.0946 0.029164 8/2/99 0.095943 0.078932 -0.088303 -0.001259 9/1/99 -0.055278 -0.021842 -0.072515 -0.03657 10/1/99 0.122319 0.02233 0.156916 -0.025524 11/1/99 0.029724 -0.016316 -0.090737 0.077569 12/1/99 0.014433 0.282236 -0.136175 0.015581 1/3/00 -0.074977 -0.161694 -0.034898 0.028843 2/1/00 -0.130615 -0.086859 -0.050499 -0.086191 3/1/00 -0.354114 0.18888 0.152988 0.035574 4/3/00 0.057826 -0.34365 -0.06549 -0.003782 5/1/00 0 113029 - 103039 141377 0 07814 Covariance Cov(PG, Microsoft) Cov(PG, BAC) Cov(PG, Exxon) Cov(Microsoft, BAC) Cov(Microsoft, Exxon) Cov(BAC, Exxon) -0.000649 0.000683 0.000433 0.001681 0.000804 0.000757

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

please solve the question with detail steps on excel and show the formulas used to get the answers. Date PG Microsoft BAC Exxon H Expected Monthly Return Expected Monthly Retur 0.010848 mut 0.014854...

Can you please answer a and b? Can you please show me the solution step by step in excel sheet? Thank you. For this assignment, please use excel file group_assignment_1_portfolios.xls posted on...

Need help with Part D please Group Assignment #1 For this assignment, please use excel file group_assignment_l_portfolios.xls posted on blackboard under the folder of Excel Files. The file contains...

Really hard excel quesiton... I love you if u can help in any way thanks . For this assignment, please use excel file group _assignment 1 portfolios.xls posted on blackboard under the folder of Excel...

Please answer question (d) F G H J Expected Monthly Return Expected Monthly Return PG 0.010848) Microsoft 0.014854 BAC 0.011589 Exxon 0.012043 000 PG Microsoft BAC Exxon Variance Variance 0.004478...

F Expected Monthly Return Expected Monthly Return PG 0.011263 Microsoft 0.025676 BAC 0.010176 Exxon 0.010008 Group Assignment #1 UNEC VOUAWN PG Microsoft BAC Exxon Variance Variance 0.007769 0.021581...

Use this excel to answer question Hello i would like help on Question D. It is based on the excel sheet above. Any help is good. I will give you thumbs up! Microsoft BAC Expected Monthly Return 8...

QUESTION: (b) Find the optimal investment portfolio in the risky assets. What are the mean and s.d. of the returns of this portfolio? For this assignment, please use excel file...

The distance between the first and the third nodes of a standing wave is 0.2 m, its maximum displacement is 0.02 m and its frequency is 40 Hz. The wave- functions of the two travelling waves which...

Determine the voltage V0 at resonance in the circuit in figure. a. 60? b. 35? c. 40? d. 30 1 mH ww 12 cce of V 10 uF

Question 1 ( 2 points ) Three years ago the Jones purchased a house for $ 6 7 5 , 0 0 0 . They made a down payment of 2 0 % and took out a mortgage with BMO for the balance. They amortized the...

This is the test class code that I need help with: @Test public void testTask3example() { MarkovModel model = new MarkovModel(2,"aabcabaacaac"); ModelMatcher match = new...