You are looking to combine stock & bond portfolios. The risk and return data are as follows:

Risk-free rate = 3.0%

E(rtn) stocks = 10%

Volatility stocks = 15%

E(rtn) bonds = 6%

Volatility bonds = 8%

The correlation between stocks & bonds is 0.25.

Complete the Excel table (see below) showing varying proportions of stocks & bonds in 5 percentage point increments. (10 pts)

Download table by clicking here

When calculating return, risk and Sharpe Ratio, you MUST use absolute cell references for the risk, return and correlation of stocks and bonds, and the risk-free rate. Do NOT hard-code any numbers in your calculations.

Upload completed table in Excel below:

Using the table from above answer the following:

a) What is the optimal risky portfolio (e.g. the optimal combination of stocks & bonds)?

b) What is portfolio risk, return, and Sharpe Ratio of the optimal risky portfolio?

Home Insert Draw Page Layout Formulas Data Review View + Share A X Cut Calibri (Body) AutoSum 12 . ab Wrap Text General . Copy r Fill - Paste A E Merge & Center $ % ) 4.0 00 .00 0 Conditional Format Cell Formatting as Table Styles Insert Delete Format Format Clear Sort & Find & Filter Select E13 X fx A B C D E F G H J L . N O P Q Return Risk Sharpe Ratio Stock Allocation 08 Bond Allocation 100% 95% 90% 1 2 3 4 5 6 7 8 9 10 85% 80% 75% 708 5% 108 158 208 258 308 35% 408 45% 508 55% 608 65% 70% 65% 608 55% 508 45% 408 358 30% 25% 208 75% 808 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 85% 908 95% 100% 15% 10% 5% 08 Sheet1 + 100 40002 100