Question

1. There are three columns of data: a monthly date, a closing price for an individual stock, and the market close.
   1. Calculate a monthly return series from the closing monthly prices of both the market and the individual security.
   2. Calculate the arithmetic mean return and the standard deviation of the monthly return for both series.
   3. Calculate the geometric mean return for both series.
   4. Calculate the beta of the individual security.

Date   Closing Price   Market Closing Price   Security Return   Market Return
12/1/2003   43.73   1111.92      
1/1/2004   42.43   1131.13      
2/1/2004   43.05   1144.94      
3/1/2004   43.56   1126.21      
4/1/2004   43.8   1107.3      
5/1/2004   44.47   1120.68      
6/1/2004   43.93   1140.84      
7/1/2004   38.17   1101.72      
8/1/2004   38.91   1104.24      
9/1/2004   35.06   1114.58      
10/1/2004   35.59   1130.2      
11/1/2004   34.63   1173.82      
12/1/2004   36.68   1211.92      
1/1/2005   36.55   1181.27      
2/1/2005   37.7   1203.6      
3/1/2005   36.95   1180.59      
4/1/2005   38.52   1156.85      
5/1/2005   39.57   1191.5      
6/1/2005   37.26   1191.33      
7/1/2005   39.05   1234.18      
8/1/2005   39.26   1220.33      
9/1/2005   38.79   1228.81      
10/1/2005   38.42   1207.01      
11/1/2005   38.59   1249.48      
12/1/2005   36.44   1248.29      
1/1/2006   37.41   1280.08      
2/1/2006   37.94   1280.66      
3/1/2006   38.13   1294.87      
4/1/2006   38.21   1310.61      
5/1/2006   40.09   1270.09      
6/1/2006   39.46   1270.2      
7/1/2006   40.81   1276.66      
8/1/2006   41.1   1303.82      
9/1/2006   41.26   1335.85      
10/1/2006   43.15   1377.94      
11/1/2006   43.54   1400.63      
12/1/2006   44.86   1418.3      
1/1/2007   44.51   1438.24      
2/1/2007   43.4   1406.82      
3/1/2007   44.94   1420.86      
4/1/2007   48.87   1482.37      
5/1/2007   49.62   1530.62      
6/1/2007   49.31   1503.35      
7/1/2007   49.12   1455.27      
8/1/2007   50.69   1473.99      
9/1/2007   54.5   1526.75      
10/1/2007   58.57   1549.38      
11/1/2007   59.22   1481.14      
12/1/2007   58.52   1468.36      
1/1/2008   56.26   1378.55      
2/1/2008   55.74   1330.63      
3/1/2008   58.42   1322.7      
4/1/2008   56.5   1385.59      
5/1/2008   54.95   1400.38      
6/1/2008   50.21   1280      
7/1/2008   49.75   1267.38      
8/1/2008   50.3   1282.83      
9/1/2008   51.45   1164.74      
10/1/2008   42.86   968.75      
11/1/2008   45.99   896.24      
12/1/2008   44.42   903.25      
              
Arithmetic mean              
Geometric mean               
Standard deviation              
              
Covariance              
Beta              

Answer 1

Calculate the Security return and Market returns from 01-01-2004
Formula for Monthly Returns	=(P2/P1-1)*100	P2= Price of current months
		P1= Price of Previous month

Date	Security Closing Price	Market Closing Price	Security Return	Market Return
12-01-2003	43.73	1111.92	NA	NA
01-01-2004	42.43	1131.13	-2.97%	1.73%
02-01-2004	43.05	1144.94	1.46%	1.22%
03-01-2004	43.56	1126.21	1.18%	-1.64%
04-01-2004	43.8	1107.3	0.55%	-1.68%
05-01-2004	44.47	1120.68	1.53%	1.21%
06-01-2004	43.93	1140.84	-1.21%	1.80%
07-01-2004	38.17	1101.72	-13.11%	-3.43%
08-01-2004	38.91	1104.24	1.94%	0.23%
09-01-2004	35.06	1114.58	-9.89%	0.94%
10-01-2004	35.59	1130.2	1.51%	1.40%
11-01-2004	34.63	1173.82	-2.70%	3.86%
12-01-2004	36.68	1211.92	5.92%	3.25%
01-01-2005	36.55	1181.27	-0.35%	-2.53%
02-01-2005	37.7	1203.6	3.15%	1.89%
03-01-2005	36.95	1180.59	-1.99%	-1.91%
04-01-2005	38.52	1156.85	4.25%	-2.01%
05-01-2005	39.57	1191.5	2.73%	3.00%
06-01-2005	37.26	1191.33	-5.84%	-0.01%
07-01-2005	39.05	1234.18	4.80%	3.60%
08-01-2005	39.26	1220.33	0.54%	-1.12%
09-01-2005	38.79	1228.81	-1.20%	0.69%
10-01-2005	38.42	1207.01	-0.95%	-1.77%
11-01-2005	38.59	1249.48	0.44%	3.52%
12-01-2005	36.44	1248.29	-5.57%	-0.10%
01-01-2006	37.41	1280.08	2.66%	2.55%
02-01-2006	37.94	1280.66	1.42%	0.05%
03-01-2006	38.13	1294.87	0.50%	1.11%
04-01-2006	38.21	1310.61	0.21%	1.22%
05-01-2006	40.09	1270.09	4.92%	-3.09%
06-01-2006	39.46	1270.2	-1.57%	0.01%
07-01-2006	40.81	1276.66	3.42%	0.51%
08-01-2006	41.1	1303.82	0.71%	2.13%
09-01-2006	41.26	1335.85	0.39%	2.46%
10-01-2006	43.15	1377.94	4.58%	3.15%
11-01-2006	43.54	1400.63	0.90%	1.65%
12-01-2006	44.86	1418.3	3.03%	1.26%
01-01-2007	44.51	1438.24	-0.78%	1.41%
02-01-2007	43.4	1406.82	-2.49%	-2.18%
03-01-2007	44.94	1420.86	3.55%	1.00%
04-01-2007	48.87	1482.37	8.74%	4.33%
05-01-2007	49.62	1530.62	1.53%	3.25%
06-01-2007	49.31	1503.35	-0.62%	-1.78%
07-01-2007	49.12	1455.27	-0.39%	-3.20%
08-01-2007	50.69	1473.99	3.20%	1.29%
09-01-2007	54.5	1526.75	7.52%	3.58%
10-01-2007	58.57	1549.38	7.47%	1.48%
11-01-2007	59.22	1481.14	1.11%	-4.40%
12-01-2007	58.52	1468.36	-1.18%	-0.86%
01-01-2008	56.26	1378.55	-3.86%	-6.12%
02-01-2008	55.74	1330.63	-0.92%	-3.48%
03-01-2008	58.42	1322.7	4.81%	-0.60%
04-01-2008	56.5	1385.59	-3.29%	4.75%
05-01-2008	54.95	1400.38	-2.74%	1.07%
06-01-2008	50.21	1280	-8.63%	-8.60%
07-01-2008	49.75	1267.38	-0.92%	-0.99%
08-01-2008	50.3	1282.83	1.11%	1.22%
09-01-2008	51.45	1164.74	2.29%	-9.21%
10-01-2008	42.86	968.75	-16.70%	-16.83%
11-01-2008	45.99	896.24	7.30%	-7.48%
12-01-2008	44.42	903.25	-3.41%	0.78%

Now formulae for Arithmatic mean (AM) and standard deviation:

In Excel Sheet

Arithmatic Mean	Value	Formula in excel
Return of Security, Rs	0.13%	=Average(number1, number2,…)
Standard deviation, ss	0.046	=STDEV.S(number1, number2,…)
Standard deviation, Rm	-0.27%	=Average(number1, number2,…)
Standard deviation, sm	0.037	=STDEV.S(number1, number2,…)

Geometric mean of Security	=PRODUCT(Return1, Return2,……..Return n)^(1/COUNT(Return1, Return2,……..Return n))
As, Product of security return=	-8.573E-102	(-ve)value
No of returns=	60
Geomeric mean can not be calculated for negative product

Geometric mean of Market	=PRODUCT(Return1, Return2,……..Return n)^(1/COUNT(Return1, Return2,……..Return n))
Product of Market return=	4.709E-99
No of returns=	60
GM	0.023

Now for calculation of beta formula is :

In Excel; CoVariance=COVARIANCE.S(arrey1,arrey2) (arrey1 for stock returns and arrey 2 for market returns)

CoVariance=0.000788

Variance=COVARIANCE.S(arrey2,arrey2) (both arreys for market returns)

Variance= 0.001377

Beta of stock=0.000788/0.001377= 0.57