In: Finance
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
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