Question

Below are returns on the stock A and S&P500 index. All numbers are in decimals (-0.0222 is equivalent to -2.22%).

A	S_P500
-0.0222	0.0032
-0.0048	-0.0058
0.1333	0.0434
0.0765	0.1081
-0.0161	-0.0121
0.1250	0.1400
0.0145	0.0368
-0.0475	-0.0454
0.0430	0.0577
-0.0260	-0.1374
0.0071	0.0064
0.0249	0.0186
0.0850	0.0215
-0.0624	-0.0752
0.0933	0.0365
0.0456	0.0528
-0.0632	-0.0131
0.0450	0.0009
0.0200	0.0017
0.0280	0.0985

Assuming normal distribution, what is the probability that the next stock A return will be less than 0?

Please write an answer in decimals. For example, 12% would be 0.12.
Also, round your answer to the second decimal.

Answer 1

A	B	C	D	E	F	G	H
2
3			A	S_P500
4			-2.22%	0.32%
5			-0.48%	-0.58%
6			13.33%	4.34%
7			7.65%	10.81%
8			-1.61%	-1.21%
9			12.50%	14.00%
10			1.45%	3.68%
11			-4.75%	-4.54%
12			4.30%	5.77%
13			-2.60%	-13.74%
14			0.71%	0.64%
15			2.49%	1.86%
16			8.50%	2.15%
17			-6.24%	-7.52%
18			9.33%	3.65%
19			4.56%	5.28%
20			-6.32%	-1.31%
21			4.50%	0.09%
22			2.00%	0.17%
23			2.80%	9.85%
24		Mean Return	2.50%	1.69%	=AVERAGE(E4:E23)
25		St. Deviation	5.73%	6.23%	=STDEV.S(E4:E23)
26
27		Since the return of the stock is normally distributed, therefore
28		Norm.dist function of excel can be used to calculated the probability as follows:
29
30		Probability that the return is less than zero	=P(X<0%)
31			0.33	=NORM.DIST(0%,D24,D25,TRUE)
32
33		Hence the probability that the return is less than zero is	0.33
34