You are given two stocks, MDC and IBM. In the Excel file (FIN 219_project 1_data.xisx), you have the closing prices for each month from March 2009 to February 2014 for both stocks. Do the following practice in an Excel file. 1. Calculate the returns for both stock in each month during this five-year period by using rt+1=ln(PtPt+1)= 2. Calculate the average monthly return and standard deviation of monthly returns for each stock. Using only the information (average monthly return \& standard deviation), which stock would you have preferred to own over this five-year period? 3. Calculate the correlation coefficient () for the two sets of returns by using the "CORREL" function in Excel. 4. If the historical returns (from 2.), standard deviations (from 2.), and correlation (from 3.) fairly represent the future, calculate the expected return and standard deviation of a portfolio consisting of 50% invested in each stock. Would you now prefer to choose the portfolio or an individual stock? (I.e., which one would you choose from the following three: a 50-50 portfolio by MCD \& IBM; MCD only; and IBM only?) 5. Using the Excel Solver, find the weights for each stock that would result in a minimum portfolio standard deviation. - At the end of your group report file (i.e., the Excel file), report each group member's contribution (in percentage). You are given two stocks, MDC and IBM. In the Excel file (FIN 219_project 1_data.xisx), you have the closing prices for each month from March 2009 to February 2014 for both stocks. Do the following practice in an Excel file. 1. Calculate the returns for both stock in each month during this five-year period by using rt+1=ln(PtPt+1)= 2. Calculate the average monthly return and standard deviation of monthly returns for each stock. Using only the information (average monthly return \& standard deviation), which stock would you have preferred to own over this five-year period? 3. Calculate the correlation coefficient () for the two sets of returns by using the "CORREL" function in Excel. 4. If the historical returns (from 2.), standard deviations (from 2.), and correlation (from 3.) fairly represent the future, calculate the expected return and standard deviation of a portfolio consisting of 50% invested in each stock. Would you now prefer to choose the portfolio or an individual stock? (I.e., which one would you choose from the following three: a 50-50 portfolio by MCD \& IBM; MCD only; and IBM only?) 5. Using the Excel Solver, find the weights for each stock that would result in a minimum portfolio standard deviation. - At the end of your group report file (i.e., the Excel file), report each group member's contribution (in percentage)
