*Need help on problem #4 and 5 (how to calculate the standard deviation of the portfolio and the minimum portfolio standard deviation). Please include how you got to the answers!!!

\begin{tabular}{|c|c|c|c|c|} \hline & \multicolumn{2}{|c|}{ Closing Prices } & \multirow[b]{2}{*}{ r(MDC) } & \multirow[b]{2}{*}{ r(IBM) } \\ \hline Date & MDC & IBM & & \\ \hline 3/1/09 & 26.3 & 88.42 & & \\ \hline 4/1/09 & 28.86 & 94.19 & 9.29% & 6.32% \\ \hline 5/1/09 & 26.12 & 97.5 & 9.98% & 3.45% \\ \hline 6/1/09 & 25.61 & 95.79 & 1.97% & 1.77% \\ \hline 7/1/09 & 29.97 & 108.19 & 15.72% & 12.17% \\ \hline 8/3/09 & 32.09 & 108.8 & 6.83% & 0.56% \\ \hline 9/1/09 & 29.76 & 110.24 & 7.54% & 1.31% \\ \hline 10/1/09 & 27.94 & 111.16 & 6.31% & 0.83% \\ \hline 11/2/09 & 25.63 & 116.97 & 8.63% & 5.09% \\ \hline 12/1/09 & 26.79 & 121.19 & 4.43% & 3.54% \\ \hline 1/4/10 & 29 & 113.31 & 7.93% & 6.72% \\ \hline 2/1/10 & 29.76 & 118.25 & 2.59% & 4.27% \\ \hline 3/1/10 & 30.1 & 119.26 & 1.14% & 0.85% \\ \hline 4/1/10 & 33.31 & 119.96 & 10.13% & 0.59% \\ \hline 5/3/10 & 27.5 & 117.08 & 19.17% & 2.43% \\ \hline 6/1/10 & 23.62 & 115.42 & 15.21% & 1.43% \\ \hline 7/1/10 & 25.52 & 120.02 & 7.74% & 3.91% \\ \hline 8/2/10 & 23.54 & 115.66 & 8.08% & 3.70% \\ \hline 9/1/10 & 25.67 & 126 & 8.66% & 8.56% \\ \hline 10/1/10 & 22.77 & 134.89 & 11.99% & 6.82% \\ \hline 11/1/10 & 22.27 & 133.47 & 2.22% & 1.06% \\ \hline 12/1/10 & 25.66 & 138.47 & 14.17% & 3.68% \\ \hline 1/3/11 & 27.57 & 152 & 7.18% & 9.88% \\ \hline 2/1/11 & 23.61 & 1 & 15.51% & 0.32% \\ \hline 3/1/11 & 22.8 & & 3.49% & \\ \hline 4/1/ & 26.26 & & & \\ \hline & & & & \\ \hline & & & & \\ \hline 7/1/11 & & & & \\ \hline 8/1/11 & & & -13 & \\ \hline 9/1/11 & 8 & & -14 & 1.71% \\ \hline 10/3/11 & 20.6 & & & .43% \\ \hline 11/1/11 & 16.64 & & -21 & .21% \\ \hline 12/1/11 & 1 & 3 & % & 2.21% \\ \hline 1/3/12 & 1 & 79 & 11.70% & 4.63% \\ \hline 2/1/12 & 2 & 9 & % & .51% \\ \hline 3/1/12 & 2 & & % & 88% \\ \hline 4/2/12 & 2 & 6 & 8.59% & 0.75% \\ \hline 5/1/12 & 2 & & 3.05% & 6.68% \\ \hline 6/1/12 & 31.05 & 9.17 & 12.91% & 1.38% \\ \hline 7/2/12 & 30.28 & 189.56 & 2.51% & 0.21% \\ \hline 8/1/12 & 33.22 & 189.27 & 9.27% & 0.15% \\ \hline 9/4/12 & & 201.51 & 10.48% & 6.27% \\ \hline 10/1/12 & & & 0.71% & \\ \hline 11/1/12 & & & -7.5 & \\ \hline 12/ & & & & \\ \hline & & & & \\ \hline & & & & \\ \hline 3/1 & & & & \\ \hline 4/1/13 & & & & .18% \\ \hline 5/1/13 & 79 & 76 & 1.32% & 3.14% \\ \hline 6/3/13 & 32.23 & 188.11 & 13.23% & 8.48% \\ \hline 7/1/13 & 31.37 & 191.98 & 2.70% & 2.04% \\ \hline 8/1/13 & 27.59 & 180.31 & 12.84% & 6.27% \\ \hline 9/3/13 & 29.75 & 183.19 & 7.54% & 1.58% \\ \hline 10/1/13 & 28.94 & 177.28 & 2.76% & 3.28% \\ \hline 11/1/13 & 29.96 & 178.7 & 3.46% & 0.80% \\ \hline 12/2/13 & 31 & 186 & 6.46% & 4.30% \\ \hline & 30 & 175 & 4.25% & 5.98% \\ \hline 2/3/14 & 31.19 & 185.17 & 1.81% & 5.24% \\ \hline \end{tabular} You are given two stocks, MDC and IBM. In the Excel file (FIN 219_project 1_data.xlsx), 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. \begin{tabular}{|c|c|c|} \hline MDC & & IBM \\ \hline \multicolumn{3}{|c|}{ Avg. Monthly Return: } \\ \hline 0.29% & & 1.25% \\ \hline \multicolumn{3}{|c|}{ Standard Deviation: } \\ \hline 10.18% & & 4.37% \\ \hline \multicolumn{3}{|c|}{ Coefficient of Variance: } \\ \hline 35.21 & & 3.48 \\ \hline \multicolumn{3}{|c|}{ Correlation: } \\ \hline & 0.382351937 & \\ \hline \multicolumn{3}{|c|}{ Portfolio: } \\ \hline \multicolumn{3}{|l|}{ Portfolio Weight: } \\ \hline MDC & 50% & \\ \hline IBM & 50% & \\ \hline Expected Return: & 0.77% & \\ \hline \end{tabular}