Please fill in the blanks using the data and instructions. Please also provide the excel formulas to be used in the blanks to get the answers (e.g. =SUM(A1:A10), etc).

Spreadsheet 6.1 Capital market expectations for stock and bond funds 1 Spreadsheet 6.2 2 Variance and standarad deviations of returns E(rp)=W1r1+W2r2 Spreadsheet 6.4 Covariance between the returns of the stock and bond funds Cov(rS,rB)=i=1Sp(i)[rS(i)E(rS)][rB(i)E(rB)] SB=SBCov(rS,rB) Spreadsheet 6.5 The investment obDortunitv set with the stock and bond funds E(rp)=W1r1+W2r2P2=(wBB)2+(wSS)2+2(wBB)(wSS)BS Graph Investment Opportunity Set 2. Ine weigncs or the munimum-vanance portiono are computed using the formula in footnote 1 . Spreadsheet 6.6 Investment opportunity set for stocks and bonds with various correlation coefficients Notes: 1. p=SQRT[(ColAC3)2+((1ColA)D3)2+2ColAC3(1ColA)D3] 2. The standard deviation is calculated from equation 6.6 using the weights of the miniumum-variance portfolio: 3. As the correlation coefficient grows, the minimum variance portfolio requires a smaller position in stocks (even a negative position for higher correlations), and its performance becomes less attractive. 4. Notice that with correlation of .5 or higher, minimum variance is achieved with a short position in stocks. The standard deviation is lower than that of bonds, but the mean is lower as well. 5. With perfect positive correlation (column G ), you can drive the standard deviation to zero by taking a large, short position in stocks. The mean return is then as low as 1.36%. \begin{tabular}{|l|r|r|r|} \hline Data & X & M & \multicolumn{1}{|l|}{ T-Bills } \\ \hline Mean (\%) & 15 & 10 & 5 \\ \hline Std. Dev. (%) & 50 & 20 & 0 \\ \hline Corr. Coeff. X and S & & 0.2 & \\ \hline \end{tabular} Portfolio Opportunity Set OptimalPf1.00WeightinX1.000.900.800.700.600.500.400.300.200.100.000.100.200.300.400.500.600.700.800.900.001.101.201.301.401.501.601.701.801.902.000.180.26WeightinS2.001.901.801.701.601.501.401.301.201.101.000.900.800.700.600.500.400.300.200.1015.000.100.200.300.400.500.600.700.800.901.000.820.74PfMean(%)5.005.506.006.507.007.508.008.509.009.5010.0010.5011.0011.5012.0012.5013.0013.5014.0014.5050.0015.5016.0016.5017.0017.5018.0018.5019.0019.5020.0010.9111.28PfStdDev(%)70.0064.4458.9253.4548.0442.7237.5232.5127.7823.5220.0017.6917.0918.3621.1725.0029.4634.3139.4044.6455.4360.9366.4672.0377.6283.2388.8794.51100.16105.8317.0617.59 wn=[E(rn)rf]s2+[E(rt)rf]n2[E(rs)rf+E(rs)rf]ssss[E(rs)rf]s2[E(rs)rf]nsss y=E(rp)rfE(rc)rfy=pc