Question

Consider the following assets available for investment:

1. A stock index fund

2. A corporate bond fund

3. A utility fund

4. A global fund

5. A treasury fund

You have research on the funds that has projected out the expected returns (in percent) for the funds

along with the probabilities of those returns occurring. The following table shows the expectations:

	probability	SIF	CBF	UF	GF	TF
Rec	.15	-18	5	-5	-20	3
N Rec	.2	-7	3	-3	-10	3
norm	.3	12	6	5	15	3
n boom	.2	20	2	10	25	3
boom	.15	25	-1	15	35	3

Your assignment is to analyze the risk and return metrics of these assets and answer the following questions:

1. What is the expected return of each asset?

2. What is the standard deviation of each asset?

3. What is the Sharpe Ratio of each asset, using the treasury fund as the risk free asset? (Note, the treasury fund will not have Sharpe ratio because it is the risk free asset)

4. Using the stock index fund as the “market”, what is the Beta of each asset?

5. If you were only going to invest in one of these assets, would it be more appropriate to use standard deviation or Beta as your measure of risk, and why?

6. Which asset, if held as a single investment, gives you the best reward for the risk you have taken?

7. Diagram the Security Market Line (assuming the stock index fund is the market and the treasury fund is the risk free asset), plotting the assets in their appropriate places.

8. Which assets are considered “underpriced” and which are considered “overpriced” based on the SML?

Answer 1

Formula sheet

A1	B	C	D	E	F	G	H	I
2				Rate of Return if the state occurs
3		State	Probability	SIF	CBF	UF	GF	TF
4		Rec	0.15	-0.18	0.05	-0.05	-0.2	0.03
5		N Rec	0.2	-0.07	0.03	-0.03	-0.1	0.03
6		norm	0.3	0.12	0.06	0.05	0.15	0.03
7		n boom	0.2	0.2	0.02	0.1	0.25	0.03
8		boom	0.15	0.25	-0.01	0.15	0.35	0.03
9
10		1)
11		Calculation of Expected Return for each Stock:
12		Expected return is given by following formula:
13
14
15
16
17
18		Expected return of SIF, rs	=Sum of product of probability and return in each state
19			=SUMPRODUCT(D4:D8,E4:E8)	=SUMPRODUCT(D4:D8,E4:E8)
20
21		Hence expected return of SIF is	=D19
22
23		Simillarly expected return for other funds can be calculated as follows:
24
25			=E3	=F3	=G3	=H3	=I3
26		Expected Return	=SUMPRODUCT($D$4:$D$8,E4:E8)	=SUMPRODUCT($D$4:$D$8,F4:F8)	=SUMPRODUCT($D$4:$D$8,G4:G8)	=SUMPRODUCT($D$4:$D$8,H4:H8)	=SUMPRODUCT($D$4:$D$8,I4:I8)
27
28		2)
29		Variance and standard deviation of stocks can be calculated as follows:
30		Variance and standard deviation of stocks can be calculated from following formula:
31
32
33
34
35
36
37
38
39		Variance of Stock A, VarA	=Sum of product of probability and square of excces return in each state
40			=SUMPRODUCT(D4:D8,(E4:E8-D26)^2)	=SUMPRODUCT(D4:D8,(E4:E8-D26)^2)
41
42		Standard Deviation of Stock A is	=Sqrt (Variance of Stock A)
43			=SQRT(D40)	=SQRT(D40)
44
45
46		Simillarly Variance and Standard deviation of Stock B and Calculated:
47
48			=D25	=E25	=F25	=G25	=H25
49		Expected Return	=D26	=E26	=F26	=G26	=H26
50		Variance	=SUMPRODUCT($D$4:$D$8,(E4:E8-D49)^2)	=SUMPRODUCT($D$4:$D$8,(F4:F8-E49)^2)	=SUMPRODUCT($D$4:$D$8,(G4:G8-F49)^2)	=SUMPRODUCT($D$4:$D$8,(H4:H8-G49)^2)	=SUMPRODUCT($D$4:$D$8,(I4:I8-H49)^2)
51		Standard Deviation	=SQRT(D50)	=SQRT(E50)	=SQRT(F50)	=SQRT(G50)	=SQRT(H50)
52
53		3)
54
55		Calculation of Sharpe Ratio:
56		Sharpe ratio divides the average portfolio's excess return by the standard deviation of return over the period.
57		Sharpe Ratio	=(rp-rf)/?p
58		Where, rp is average portfolio return, rf is risk free rate and ?p is standard deviation of return.
59
60			=D48	=E48	=F48	=G48	=H48
61		Expected Return	=D49	=E49	=F49	=G49	=H49
62		Variance	=D50	=E50	=F50	=G50	=H50
63		Standard Deviation	=D51	=E51	=F51	=G51	=H51
64		Sharpe Ratio	=(D61-$H$61)/D63	=(E61-$H$61)/E63	=(F61-$H$61)/F63	=(G61-$H$61)/G63		=(G61-$H$61)/G63
65
66		4)
67
68		Beta can be calculated using following formula:
69		Beta of stock i, ?i	=Cov(Rm,Ri)/?m2
70		Where Cov(Rm,Ri) is the covariance of market return (Rm) and stock return (Ri) and ?m2 is the variance of the market.
71
72		Co-variance between A and B can be calculated as follows:
73
74
75
76
77
78		Cov(A,B)	=SUMPRODUCT($D$4:$D$8,$E$4:$E$8-$D$26,F4:F8-E$26)	=SUMPRODUCT($D$4:$D$6,$E$4:$E$6-$D$26,F4:F6-E$26)
79
80		Covariance matrix can be calculated as follows:
81		Covariance matrix
82			=D60	=E60	=F60	=G60	=H60
83		SIF	=SUMPRODUCT($D$4:$D$8,$E$4:$E$8-$D$26,E4:E8-D$26)	=SUMPRODUCT($D$4:$D$8,$E$4:$E$8-$D$26,F4:F8-E$26)	=SUMPRODUCT($D$4:$D$8,$E$4:$E$8-$D$26,G4:G8-F$26)	=SUMPRODUCT($D$4:$D$8,$E$4:$E$8-$D$26,H4:H8-G$26)	=SUMPRODUCT($D$4:$D$8,$E$4:$E$8-$D$26,I4:I8-H$26)
84
85		Now Beta Can be calculated as follows:
86			=D82	=E82	=F82	=G82	=H82
87		Covariance with market	=D83	=E83	=F83	=G83	=H83
88		Variance of Market	=$D$50	=$D$50	=$D$50	=$D$50	=$D$50
89		Beta	=D87/D88	=E87/E88	=F87/F88	=G87/G88	=H87/H88	=H87/H88
90