Question

Stocks A and B have the following probability distributions of expected future returns: PROBABILITY: 0.1, 0.2, 0.4, 0.2, 0.1 Stock A: 8%, 5,13, 21,29 Stock B. 36%, 0, 22, 25, 36. Calculate the expected rate of return, rB, for Stock B (rA = 12.50%.) Do not round intermediate calculations. Round your answer to two decimal places. Calculate the standard deviation of expected returns, ?A, for Stock A (?B = 19.68%.) Do not round intermediate calculations. Round your answer to two decimal places. Now calculate the coefficient of variation for Stock B. Round your answer to two decimal places.

Answer 1

Formula sheet

A1	B	C	D	E	F	G	H	I	J
2			Rate of Return if the state occurs
3		Probability	Stock A	Stock B
4		0.1	-0.18	0.05
5		0.2	-0.07	0.03
6		0.4	0.12	0.06
7		0.2	0.2	0.02
8		0.1	0.25	-0.01
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 Stock A, rA	=Sum of product of probability and return in each state
19			=SUMPRODUCT(C4:C8,D4:D8)	=SUMPRODUCT(C4:C8,D4:D8)
20
21		Hence Expected return of Stock A is	=D19
22
23		Simillarly expected return for other funds can be calculated as follows:
24
25			=D3	=E3
26		Expected Return	=SUMPRODUCT($C$4:$C$8,D4:D8)	=SUMPRODUCT($C$4:$C$8,E4:E8)
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(C4:C8,(D4:D8-D26)^2)	=SUMPRODUCT(C4:C8,(D4:D8-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 can be calculated as follows:
47
48			=D25	=E25
49		Expected Return	=D26	=E26
50		Variance	=SUMPRODUCT($C$4:$C$8,(D4:D8-D49)^2)	=SUMPRODUCT($C$4:$C$8,(E4:E8-E49)^2)
51		Standard Deviation	=SQRT(D50)	=SQRT(E50)
52
53		Coefficicent of variation can be calculated as:
54		Coefficient of variation	=Standard Deviation / Expected Return
55
56			=D48	=E48
57		Expected Return	=D49	=E49
58		Variance	=D50	=E50
59		Standard Deviation	=D51	=E51
60		Coefficient of variation	=D59/D57	=E59/E57	=E59/E57
61