Security Analysis & Portfolio Finance ,Excel question about efficient frontier

Fin3507Project1.xlsx

READ instruction sheet

There are three sheets in the EXCEL file.

Complete the problems in each excel sheet, all calculations must be done within the excel file and show your calculation in order to receive credit.

Must enter your final answers in the designated BOX to receive FULL credit.

This is an individual project so each student must submit:

a hard copy of of completed excel work (including graphs) in class by 10/5/2017;

AND an e-copy of the completed excel file via blackboard by 10/5/2017 at 2:00pm. Checking your e-copy is for potential partial credits.

1. Consider the table below that summarizes the probability distributions of the two risky funds, a stock fund and a bond fund:
		Stock Fund	Bond Fund	Expected return	Variance
Scenario	Probability	Return	Return	of Stock Fund	of Stock Fund
Severe recession	0.05	-43%	-12%	-0.0215	0.00834
Mild resession	0.25	-17%	12%	-0.0425	0.00406
Normal growth	0.4	14%	9%	0.056	0.00282
Boom	0.3	31%	4%	0.093	0.01413
				0.085	0.02936
Calculation of mean return and variance for the stock fund using Excel.
Show all the calculations in this excel, MUST enter your final answers (in decimal numbers) in the BOX below:
Keep 4-digit decimal places, e.g., 0.1234.
Mean (Expected) return is	0.085
Variance is	0.0294

2. Consider the table below that summarizes the probability distributions of the two risky funds, a stock fund and a bond fund:
		Stock Fund	Bond Fund	Expected value	Expected value
Scenario	Probability	Return	Return	of Stock Fund	of Bond Fund	Covariance
Severe recession	0.05	-43%	-12%	-0.02150	-0.00600	0.00233
Mild resession	0.25	-17%	12%	-0.04250	0.03000	-0.00287
Normal growth	0.4	14%	9%	0.05600	0.03600	0.00181
Boom	0.3	31%	4%	0.09300	0.01200	0.00182
				0.08500	0.07200	0.00310
Calculate the value of the covariance between the stock and bond funds using Excel.
Show all the calculations in this excel, MUST enter your final answers (in decimal) in the BOX below:
Keep 5-digit decimal places.
Covariance is	0.0031

3. Consider the data below for two risky funds, a stock fund (S) and a bond fund (B):
					T-Bill rate
10%	5%	15%	6%	0.3	3%
A. Fill in columns C, D, and E, the expected return ©, volatility (D), and Reward-to-Variability ratio (D) of portfolios consisting of the stock fund and bond fund.
Enter your answers in decimal numbers, and keep 4-digit decimal places.
Weights in the	Weights in the	Portfolio	Portfolio	Reward to
stock fund	bond fund	expected return	volatility	Variability Ratio
-0.6	1.6
-0.5	1.5
-0.4	1.4
-0.3	1.3
-0.2	1.2
-0.1	1.1
0	1
0.1	0.9
0.2	0.8
0.3	0.7
0.4	0.6
0.5	0.5
0.6	0.4
0.7	0.3
0.8	0.2
0.9	0.1
1	0
1.1	-0.1
1.2	-0.2
1.3	-0.3
1.4	-0.4
1.5	-0.5
1.6	-0.6
B. Make an excel graph of the efficient frontier using the data in the table above, like the graph in my Topic 4 lecture note.
Clearly label the X-axis (standard deviation) and Y-axis (expected return)
C. Using the formula for the tangent portfolio weights in Topic 4, find the proportions (weights) of stock fund and bond fund of the optimal risky portfolio.
Show all the calculations in this excel, MUST enter your final answers (in %) in the BOX below:
Keep 2-digit decimal places in %, e.g., 12.15%
Weight on the stock fund			%
Weight on the bond fund			%

)

(

]

)

(

)

(

[

]

)

(

[

]

)

(

[

)

(

]

)

(

[

]

)

(

[

Cov