Sheet1
Hickman, Hunter, Byrd, Foundations of Corporate Finance. Copyright 1996
Common Stock Beta, Rate of Return and Valuation
On this sheet are formulas for solving: 1. Common stock beta 2. Average portfolio beta 3. Required return on common stock using the capital asset pricing model. 4. Expected return on common stock using the dividend growth model. 5. Common stock value using the dividend growth model (constant growth rate). 6. Common stock value using the dividend growth model (non-constant growth rate).
Instructions: 1. Enter data in shaded cells 2. Enter percentage values as decimals, e.g. 3% as .03.
Beta Calculations
Common Stock Beta Stock Std. Deviation S&P 500 Std. Deviation Correlation Covariance Variance S&P 500 Beta 0.000 0.00% #DIV/0! Average Portfolio Beta Stock 1 2 3 4 5 6 7 8 9 10 Total Average Beta Proportion
0.00 0.00
Required Rates of Return
Capital Asset Pricing Model
(1) Using Beta Calculated Above Risk free rate Exp. market return Beta #DIV/0! Market risk premium Stock risk premium Required return 0.0% #DIV/0! #DIV/0!
Capital Asset Pricing Model
(2) Using User Supplied Beta Risk free rate Exp. market return Beta Market risk premium Stock risk premium Required return 0.0% 0.0% 0.0%
Expected Rates of Return
Page 1
Sheet1
Dividend Growth Model
(1) Past Dividend (D0) Given Past dividend (D0) Dividend growth rate Current stock price Expected dividend (D1) Expected return $0.00 #DIV/0!
Dividend Growth Model
(2) Expected Dividend (D1) Given Expected dividend (D1) Dividend growth rate Current stock price Expected return #DIV/0!
Common Stock Valuation (constant growth rate)
(1) Past Dividend (D0) Given Past dividend (D0) Dividend growth rate Required Return Expected dividend (D1) Stock value $0.00 #DIV/0! (2) Expected Dividend (D1) Given Expected dividend (D1) Dividend growth rate Required Return Stock value #DIV/0!
Common Stock Valuation (non-constant growth rate)
(1) Past Dividend (D0) Given Past dividend (D0) Abnormal div. growth rate Abnormal growth period Constant div. growth rate Required Return PV of abnormal dividends PV of constant dividends Stock value #N/A #DIV/0! #N/A (1-10 years) (2) Expected Dividend (D1) Given Expected dividend (D1) Abnormal div. growth rate Abnormal growth period Constant div. growth rate Required Return PV of abnormal dividends PV of constant dividends Stock value #N/A #DIV/0! #N/A
Page 2
Sheet1
Beta
Page 3
Sheet1
(1-10 years) 1 1 2 3 4 5 6 7 8 9 10 0 0 0 0 0 0 0 0 0 0
Page 4
Sheet1
Page 5
Sheet1
1
1 2 3 4 5 6 7 8 9 10
0 0 0 0 0 0 0 0 0 0
Page 6