Question

1 date share price benchmark 2 Mar 18 3 Feb-18 4 Jan-18 5 Dec-17 6 Nov-17 7 Oct-17 8 Sep-17 304.85 24,263.35 322.3 25,107.40 354.4 27,379.45 315.15 25,539.45 306.9 25,332.40 314.25 25,019.35 350 24.053.00 350.61 24,318.40 361.9 25,103.65 292.7 23,211.20 286.27 23,424.80 326.05 22,358.25 309.35 21,444.15 290.54 20,607.25 279.2 19.515.15 231.29 18,177.20 234.77 18,627.80 253.27 19,523.55 250.94 19.285.70 273.22 19,787.60 243.39 18,953.15 221.42 17,935.40 206.6 17,620.90 188.59 16,795.00 173.01 16,141.65 137.8 13,946.40 149.36 15,522.40 145.16 16,922.20 153.64 17,430.40 15173 17,354.50 145.94 17,216.30 137.89 17.146.55 165.79 18,729.85 168.77 18.296.10 176.46 18,721.35 167.89 18,338.10 163.15 18.206.65 172.39 19,691.20 172.49 19,843.75 154.41 18,736.65 141.99 18.513.15 136.9 17,045.05 111.77 15.392.25 114.41 15,740.40 108.22 15,267.60 108.21 15,241.90 11388 14,793.40 88.15 12 855.85 82.79 12,742.05 60.99 10,764.70 10 Jul-17 11 Jun-17 12 May-17 Apr-17 Mar-17 15 Feb-17 16 Jan-17 17 Dec-16 18 Nov-16 19 Oct-16 22 Jul-16 24 Apr-16 26 Mar-16 27 Feb-16 28 Jan-16 29 Dec-15 30 Nov-15 31 Oct-15 32 Sep-15 34 Jul-15 35 Jun-15 36 Мау-15 Apr-15 39 Feb-15 40 Jan-15 41 Dec-14 42 Nov-14 43 Oct-14 46 Jul-14 47 Jun-14 48 May-141 50 Mar-14 51 Feb-14 52 Jan-14 53 Dec-13 54 Nov-13 55 Oct-13 56 Sep-13 61.53 10.237.75 4.04 11,385.25 73.73 11,153.95 73.76 11.473.15 7 47 9.817 80 48.67 9,049.20 64.8 10,015.75 58 Jul-13 59 Jun-13 Jun-13 9222 11 617 25 97.46 12,475.65 100.33 12,561.55 85.78 11,361.85 62 Mar-13

From the following monthly stock prices assume rf of 0.5% per month and calculate

1.Monthly growth return

2.CAPM and beta

3.Average return

4.Variance

5.Covariance

6. Assume a credit rating and calculate a VAT assuming debt to equity structure

Answer 1

Please see the table below:

Steps to be followed:

1. Calculate the monthly return on stock = R_S = (S_i+1/ S_i)-1
2. Calculate the monthly return on market = R_m = (M_i+1/M_i) -1
3. Towards the end, calculate

• average using AVERAGE Function of excel
• variance of data in each column using Var.p function of excel
• Covariance
• Beta = Covariance (R_s, R_m)

Month	Share price	Benchmark	Return on Stock	Return on Benchmark
	Si	Mi	RS = (Si+1/ Si)-1	Rm = (Mi+1/Mi) -1
Mar-13	85.78	11,361.85
Apr-13	100.33	12,561.55	16.96%	10.56%
May-13	97.46	12,475.65	-2.86%	-0.68%
Jun-13	92.22	11,617.25	-5.38%	-6.88%
Jul-13	64.80	10,015.75	-29.73%	-13.79%
Aug-13	48.67	9,049.20	-24.89%	-9.65%
Sep-13	57.47	9,617.80	18.08%	6.28%
Oct-13	73.76	11,473.15	28.35%	19.29%
Nov-13	73.73	11,153.95	-0.04%	-2.78%
Dec-13	74.04	11,385.25	0.42%	2.07%
Jan-14	61.53	10,237.75	-16.90%	-10.08%
Feb-14	60.99	10,764.70	-0.88%	5.15%
Mar-14	82.79	12,742.05	35.74%	18.37%
Apr-14	88.15	12,855.55	6.47%	0.89%
May-14	113.88	14,793.40	29.19%	15.07%
Jun-14	108.21	15,241.90	-4.98%	3.03%
Jul-14	108.22	15,267.60	0.01%	0.17%
Aug-14	114.41	15,740.40	5.72%	3.10%
Sep-14	111.77	15,392.25	-2.31%	-2.21%
Oct-14	136.90	17,045.05	22.48%	10.74%
Nov-14	141.99	18,513.15	3.72%	8.61%
Dec-14	154.41	18,736.65	8.75%	1.21%
Jan-15	172.49	19,843.75	11.71%	5.91%
Feb-15	172.39	19,691.20	-0.06%	-0.77%
Mar-15	163.15	18,206.65	-5.36%	-7.54%
Apr-15	167.89	18,338.10	2.91%	0.72%
May-15	176.46	18,721.35	5.10%	2.09%
Jun-15	168.77	18,296.10	-4.36%	-2.27%
Jul-15	165.79	18,729.85	-1.77%	2.37%
Aug-15	137.89	17,146.55	-16.83%	-8.45%
Sep-15	145.94	17,216.30	5.84%	0.41%
Oct-15	151.73	17,354.50	3.97%	0.80%
Nov-15	153.64	17,430.40	1.26%	0.44%
Dec-15	145.16	16,922.20	-5.52%	-2.92%
Jan-16	149.36	15,522.40	2.89%	-8.27%
Feb-16	137.80	13,946.40	-7.74%	-10.15%
Mar-16	173.01	16,141.65	25.55%	15.74%
Apr-16	188.59	16,795.00	9.01%	4.05%
May-16	206.60	17,620.90	9.55%	4.92%
Jun-16	221.42	17,935.40	7.17%	1.78%
Jul-16	243.99	18,953.15	10.19%	5.67%
Aug-16	273.22	19,787.60	11.98%	4.40%
Sep-16	250.94	19,285.70	-8.15%	-2.54%
Oct-16	253.27	19,523.55	0.93%	1.23%
Nov-16	234.77	18,627.80	-7.30%	-4.59%
Dec-16	231.29	18,177.20	-1.48%	-2.42%
Jan-17	279.20	19,515.15	20.71%	7.36%
Feb-17	290.54	20,607.25	4.06%	5.60%
Mar-17	309.35	21,444.15	6.47%	4.06%
Apr-17	326.05	22,358.25	5.40%	4.26%
May-17	286.27	23,424.80	-12.20%	4.77%
Jun-17	292.70	23,211.20	2.25%	-0.91%
Jul-17	361.90	25,103.65	23.64%	8.15%
Aug-17	350.61	24,318.40	-3.12%	-3.13%
Sep-17	350.00	24,053.00	-0.17%	-1.09%
Oct-17	314.25	25,019.35	-10.21%	4.02%
Nov-17	306.90	25,332.40	-2.34%	1.25%
Dec-17	315.15	25,539.45	2.69%	0.82%
Jan-18	354.40	27,379.45	12.45%	7.20%
Feb-18	322.30	25,107.40	-9.06%	-8.30%
Mar-18	304.85	24,263.35	-5.41%	-3.36%
Average return	Excel function = average (select entire column)		2.88%	1.50%
Variance	Excel function = Var.p(select the entire column)		1.52%	0.46%
Covariance	Excel function = Covariance.P(Column 1, Column 2)			0.73%
Beta	= Covariance / Variance of the return on benchmark			1.58

Part (1)

Based on the table above, monthly growth / return of the stock and the market are shown in the last two columns of the table respectively.

Part (2)

Beta = 1.58 as calculated in the table above

Rf = 0.5% per month

Rm = 1.50% per month

Cost of equity per month = Rf + beta x (Rm - Rf) = 0.5% + 1.58 x (1.50% - 0.50%) = 2.08% per month

Annual cost of equity as per CAPM, Ke = 12 x cost of equity per month = 12 x 2.08% = 24.94% per annum

Part(3), Part (4 ) and Part (5) ; Pleasee the last few rows of the table above

Part (6)

Let's assume target debt to equity structure as 1:1

D/E = 1 / 1; Hence D / (D + E) = 1 / (1 + 1) = 0.5

E/(D + E) = 1 - 0.5 = 0.5

Assume a BB / Ba2 rating. Spread corresponding to this rating = 2.38%

Cost of debt, Kd = risk free rate + spread = 12 x risk free rate per month + spread = 12 x 0.5% + 2.38% = 8.38% per annum

Tax rate, assume = 30%

WACC = D / (D + E) x Kd x (1 - T) + E / (D + E) x Ke = 0.5 x 8.38% x (1 - 30%) + 0.5 x 24.94% = 15.40%