Question

Calculate the Hurdle rate /WACC for HCL Technologies

Calculate the cost of equity capital:
1. Take historical monthly adjusted closing prices for the company from yahoo finance for 3-5 years
2. Calculate the monthly returns Ln(Pt/Pt-1)
3. Take historical monthly adjusted closing prices for market index and calculate monthly returns
4. Run a regression with dependent variable as target company monthly returns and independent variable as market returns
5. The beta (coefficient of market return) is the beta levered for the firm
6. Calculate the cost of equity capital using CAPM model
7. Risk free rate can be taken as ytm of government bond.

8. Calculate cost of debt (wither ytm of corporate bond) or synthetic rating
Calculate EBIT/Interest expense.Add credit default spread to risk free rate for cost of debt.

9. Calculate market value of debt from book value of debt for target company
10. Calculate market value of equity for target company
Calculate the cost of capital for the firm:
11. Calculate WACC using the WACC formula

Answer 1

Please refer to the below output of regression:

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.42313567
R Square	0.179043795
Adjusted R Square	0.178372531
Standard Error	0.01015903
Observations	1225
ANOVA
	df	SS	MS	F	Significance F
Regression	1	0.027528	0.027528	266.7262	2.17E-54
Residual	1223	0.126221	0.000103
Total	1224	0.153749
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	4.87666E-05	0.00029	0.167991	0.866618	-0.00052	0.000618	-0.00052	0.000618
X Variable 1	0.276086673	0.016905	16.33176	2.17E-54	0.242921	0.309252	0.242921	0.309252
Beta	0.28

Here Beta= X Variable 1 coefficient in the above table

Steps 1-5 given in the question are followed in the excel. Please note:

1) There are certain data points which need to be deleted from the excel for which the value is NULL (indicated as Excluded data in excel sheet) as excel cannot run regression on non integer values

2) The regression has to be run by going to the data analysis function in the data category of excel and selecting regression from the list of options. Here Y variable has to be the stock price as this is the dependent variable

The Beta as per regression is= 0.28

CAPM cost of equity= Rf + Beta*(Rm-Rf)

Where Rf=risk free rate, Rm= Market return

Cost of equity = 6.44%+ 0.28*(15%-6.44%)

=8.84%

The market risk premium is long term average return on the equity markerts which can be googled for SENSEX

The risk free rate can also be found as Indian govt bond yeild on 10 yr bond through google search