Beta Estimation.xlsx

	A	B	C	D	E	F	G	H	I	J	K
1	Current riskfree rate =		6.50%		Risk premium for stocks=		5.50%		Current stock price=	₹35.00
2	Number of periods of data=		53		Riskfree rate during period =		6.00%		Current Annual DPS =	₹0.00
3	Time period	Index	Price(Stock)	DPS(Stock)	Split Factor	Index Level	Return(Stock)	Return(Mkt)	(R(jt)-Rj)^2	(R(mt)-R(m))^2	(R(jt)-R(j))
4	1	1	₹30.75		1	179.63					(R(mt)-R(m))
5	2	1	₹28.00		1	181.19	-8.94%	0.87%	0.01108734828	0.000006005224244	0.000258034906
6	3	1	₹27.75	₹0.00	1	180.66	-0.89%	-0.29%	0.0006147501281	0.0001976886707	0.0003486102919
7	4	1	₹27.50		1	179.83	-0.90%	-0.46%	0.000618745362	0.0002474121228	0.0003912609148
8	5	1	₹26.38		1	189.55	-4.07%	5.41%	0.003202751398	0.001841780836	-0.002428737562
9	6	1	₹26.00	₹0.00	1	191.85	-1.44%	1.21%	0.0009162992866	0.0000009978561528	-0.00003023797085
10	7	1	₹28.25		1	190.92	8.65%	-0.48%	0.004994655663	0.0002554438614	-0.00112953713
11	8	1	₹26.00		1	188.63	-7.96%	-1.20%	0.009122466	0.0005349796347	0.002209147693
12	9	1	₹25.75	₹0.00	1	182.08	-0.96%	-3.47%	0.0006492797458	0.002103060452	0.001168535218
13	10	1	₹25.50		1	189.82	-0.97%	4.25%	0.000654045923	0.000984309884	-0.0008023614314
14	11	1	₹28.50		1	202.17	11.76%	6.51%	0.01035946868	0.002908074225	0.005488725157
15	12	1	₹29.25	₹0.00	1	211.28	2.63%	4.51%	0.0001092068009	0.001150974366	0.0003545338185
16	13	1	₹28.25		1	211.78	-3.42%	0.24%	0.002505364663	0.00007688740336	0.0004388974634
17	14	1	₹27.75		1	226.92	-1.77%	7.15%	0.001126589001	0.003642630333	-0.002025770784
18	15	1	₹26.00	₹0.00	1	238.90	-6.31%	5.28%	0.006229731278	0.001735460792	-0.003288077611
19	16	1	₹28.50		1	235.52	9.62%	-1.41%	0.006446205089	0.0006392428942	-0.002029948472
20	17	1	₹27.25		1	247.35	-4.39%	5.02%	0.003567103435	0.001528356928	-0.002334910544
21	18	1	₹23.00	₹0.00	1	250.84	-15.60%	1.41%	0.02952516622	0.000008847574513	-0.0005111028351
22	19	1	₹18.25		1	236.12	-20.65%	-5.87%	0.04945612135	0.004874539131	0.01552661582
23	20	1	₹23.00		1	252.93	26.03%	7.12%	0.05973546057	0.003606908092	0.01467856656
24	21	1	₹19.50	₹0.00	1	231.32	-15.22%	-8.54%	0.02823727246	0.009326485905	0.01622820149
25	22	1	₹20.25		1	243.98	3.85%	5.47%	0.000510577172	0.00190046344	0.0009850549471
26	23	1	₹23.00		1	249.22	13.58%	2.15%	0.01438485649	0.0001069589534	0.001240398805
27	24	1	₹21.00	₹0.00	1	242.17	-8.70%	-2.83%	0.01057238537	0.00155419922	0.004053590148
28	25	1	₹31.25		1	274.08	48.81%	13.18%	0.2230008465	0.01455204721	0.05696594463
29	26	1	₹38.00		1	284.20	21.60%	3.69%	0.04005378459	0.0006650442512	0.005161156768
30	27	1	₹38.50	₹0.00	1	289.20	1.32%	1.76%	0.000007331579471	0.00004170796121	-0.00001748671588
31	28	1	₹47.00		1	288.36	22.08%	-0.29%	0.04198959866	0.0001971114463	-0.002876913367
32	29	1	₹43.88		1	290.10	-6.64%	0.60%	0.006764826019	0.00002601969161	0.0004195458102
33	30	1	₹44.00	₹0.00	1	304.10	0.27%	4.83%	0.0001724192902	0.001378202281	-0.0004874717008
34	31	1	₹47.75		1	318.66	8.52%	4.79%	0.004811043906	0.001350115149	0.002548619874
35	32	1	₹52.75		1	329.80	10.47%	3.50%	0.007893693134	0.0005675742218	0.002116661696
36	33	1	₹57.75	₹0.00	1	321.83	9.48%	-2.42%	0.006228547312	0.00124617722	-0.002786013958
37	34	1	₹26.00		1.5	251.79	-32.47%	-21.76%	0.1159681103	0.05233365403	0.0779040112
38	35	1	₹21.50		1	230.30	-17.31%	-8.53%	0.03569927086	0.009309157614	0.01822992428
39	36	1	₹26.50	₹0.00	1	247.08	23.26%	7.29%	0.04695566376	0.003810149807	0.01337565375
40	37	1	₹25.50		1	257.07	-3.77%	4.04%	0.002873113593	0.0008583244385	-0.001570370533
41	38	1	₹28.25		1	267.82	10.78%	4.18%	0.00845987036	0.0009414053582	0.002822085627
42	39	1	₹29.38	₹0.00	1	258.89	4.00%	-3.33%	0.0005824700506	0.001978324895	-0.001073459362
43	40	1	₹31.00		1	261.33	5.51%	0.94%	0.001542444486	0.0000029248587	-0.00006716719566
44	41	1	₹30.75		1	262.16	-0.81%	0.32%	0.0005726496774	0.00006334589052	0.0001904599794
45	42	1	₹36.13	₹0.00	1	273.50	17.50%	4.33%	0.02531082635	0.001031755607	0.005110243341
46	43	1	₹34.75		1	272.02	-3.82%	-0.54%	0.00292259055	0.0002737836387	0.000894515218
47	44	1	₹28.00		1	261.52	-19.42%	-3.86%	0.0441462911	0.002473587833	0.01044986739
48	45	1	₹27.50	₹0.00	1	271.91	-1.79%	3.97%	0.001137222286	0.0008176283266	-0.0009642744187
49	46	1	₹24.75		1	278.97	-10.00%	2.60%	0.01342483348	0.0002199110883	-0.001718217024
50	47	1	₹20.25		1	273.30	-18.18%	-2.03%	0.03907887115	0.0009897215445	0.00621909967
51	48	1	₹23.75	₹0.00	1	277.72	17.28%	1.62%	0.02464081234	0.00002537771508	0.0007907765265
52	49	1	₹26.00		1	297.47	9.47%	7.11%	0.006220675404	0.003597566547	0.004730675822
53	50	1	₹25.75		1	288.86	-0.96%	-2.89%	0.0006492797458	0.001606339811	0.001021256042
54	51	1	₹26.25	₹0.00	1	294.87	1.94%	2.08%	0.00001261593705	0.00009352539127	0.00003434982458
55	52	1	₹29.00		1	309.64	10.48%	5.01%	0.0079025559	0.001517476134	0.003462938055
56	53	1	₹32.50		1	320.52	12.07%	3.51%	0.01098808596	0.0005761202483	0.002516040304
57	54	0	₹29.00	₹0.00	1	317.98	0.00%	0.00%	0	0	0
58	55	0	₹30.50		1	346.08	0.00%	0.00%	0	0	0
59	56	0	₹30.00		1	351.45	0.00%	0.00%	0	0	0
60	57	0	₹32.25	₹0.00	1	349.15	0.00%	0.00%	0	0	0
61	58	0	₹33.75		1	340.36	0.00%	0.00%	0	0	0
62	59	0	₹34.75		1	345.99	0.00%	0.00%	0	0	0
63	60	0	₹34.50	₹0.00	1	353.40	0.00%	0.00%	0	0	0
64
65		REGRESSION STATISTICS AND RISK PARAMETERS
66		RISK AND PERFORMANCE MEASURES
67		Intercept (Alpha) =		-0.45%
68		Slope (Beta)=		1.778808243
69		Rf(1- Beta) =		-0.38%
70		Intercept-Rf(1-Beta)=		-0.07%
71
72		VARIANCE STATISTICS
73		Variance of the stock=		0.01911051362
74		Variance of the market =		0.002779917412
75		Systematic variance=		0.008796100049
76		Unsystematic variance=		0.01031441357
77		R squared =		46.03%
78
79
80		USING BETA
81		In estimating expected returns:
82		Riskfree Rate =		6.50%
83		Historical return premium=		5.50%
84		Expected return=		16.28%
85
86		In forecasting prices:
87		Current price=		₹35.00
88		Annualized DPS=		₹0.00
89		Predicted prices:
90		- One year from now		₹40.70
91		- Two years from now		₹47.33
92		- Three years from now		₹55.03
93		- Four years from now		₹63.99
94		- Five years from now		₹74.41
95
96
97
98
99
100