2) 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.

A B D E 1 Return Risk Sharpe Ratio Stock Allocation 0% 2. 3 Bond Allocation 100% 95% 90% 5% 4 10% 5 6 7 15% 20% 85% 80% 25% 8 9 30% 35% 75% 70% 65% 60% 10 11 12 40% 45% 50% 55% 50% 45% 40% 13 14 35% 15 16 17 55% 60% 65% 70% 75% 80% 30% 25% 18 19 85% 20% 15% 10% 5% 20 90% 21 95% 100% 0% 22 23 24 25 26 27 28 29 30 31 32 33