THIS NEEDS TO BE DONE IN AN ATTACHMENT. ATTACH SPREAD SHEET

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. Identify the following in the Excel spreadsheet submission:

a) Optimal risky portfolio (i.e. the optimal combination of stocks and bonds)

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

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. LAST COLUMN IS SHARPE RATIO

E rtn Stock	0.1
Vol. of Stock	0.15
E rtn Bonds	0.06
Vol. of Bonds	0.08
Correleation	0.25
Risk free return	0.03
Stock Allocation	Bond Allocation	Return	Risk	Sharpe Ratio
0%	100%	0.06
5%	95%	0.062
10%	90%	0.064
15%	85%	0.066
20%	80%	0.068
25%	75%	0.07
30%	70%	0.072
35%	65%	0.074
40%	60%	0.076
45%	55%	0.078
50%	50%	0.08
55%	45%	0.082
60%	40%	0.084
65%	35%	0.086
70%	30%	0.088
75%	25%	0.09
80%	20%	0.092
85%	15%	0.094
90%	10%	0.096
95%	5%	0.098
100%	0%	0.1