Common Stock Beta Rate of Return and Valuation Template

							                                                      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                                          Average Portfolio Beta
Stock Std. Deviation                                                Stock            Proportion
S&P 500 Std. Deviation                                                 1
Correlation                                                            2
                                                                       3
Covariance                                    0.000                    4
Variance S&P 500                             0.00%                     5
Beta                                      #DIV/0!                      6
                                                                       7
                                                                       8
                                                                       9
                                                                      10
                                                                     Total                0.00
                                                                 Average Beta             0.00


Required Rates of Return
Capital Asset Pricing Model                                Capital Asset Pricing Model
(1) Using Beta Calculated Above                            (2) Using User Supplied Beta
Risk free rate                                             Risk free rate
Exp. market return                                         Exp. market return
Beta                        #DIV/0!                        Beta

   Market risk premium          0.0%                           Market risk premium          0.0%
   Stock risk premium        #DIV/0!                           Stock risk premium           0.0%
    Required return          #DIV/0!                            Required return             0.0%


Expected Rates of Return


                                                      Page 1
                                                        Sheet1



Dividend Growth Model                                        Dividend Growth Model
(1) Past Dividend (D0) Given                                 (2) Expected Dividend (D1) Given
     Past dividend (D0)                                        Expected dividend (D1)
   Dividend growth rate                                          Dividend growth rate
    Current stock price                                           Current stock price

  Expected dividend (D1)         $0.00                            Expected return        #DIV/0!
     Expected return           #DIV/0!


Common Stock Valuation (constant growth rate)
(1) Past Dividend (D0) Given                                 (2) Expected Dividend (D1) Given
Past dividend (D0)                                           Expected dividend (D1)
Dividend growth rate                                         Dividend growth rate
Required Return                                              Required Return

Expected dividend (D1)           $0.00                       Stock value                 #DIV/0!
Stock value                    #DIV/0!


Common Stock Valuation (non-constant growth rate)
(1) Past Dividend (D0) Given                                 (2) Expected Dividend (D1) Given
Past dividend (D0)                                           Expected dividend (D1)
Abnormal div. growth rate                                    Abnormal div. growth rate
Abnormal growth period                   (1-10 years)        Abnormal growth period
Constant div. growth rate                                    Constant div. growth rate
Required Return                                              Required Return

PV of abnormal dividends        #N/A                         PV of abnormal dividends     #N/A
PV of constant dividends       #DIV/0!                       PV of constant dividends    #DIV/0!
Stock value                     #N/A                         Stock value                  #N/A




                                                        Page 2
       Sheet1




Beta




       Page 3
               Sheet1




(1-10 years)
                        1   1    0
                            2    0
                            3    0
                            4    0
                            5    0
                            6    0
                            7    0
                             8   0
                             9   0
                            10   0




               Page 4
Sheet1




Page 5
             Sheet1




1    1   0
     2   0
     3   0
     4   0
     5   0
     6   0
     7   0
     8   0
     9   0
    10   0




             Page 6