Stock Valuation - Excel

Document Sample
Stock Valuation - Excel Powered By Docstoc
					                                                                      Stock Expected Return




EXPECTED RETURN COMMON STOCK

         INPUTS

         LAST DIVIDEND                                    $ 1.25 Dollar Amount
         PRICE OF STOCK                                   $ 45.00 Dollar Amount
         EXPECTED GROWTH RATE                               9.00% Decimal

         OUTPUT

         EXPECTED RETURN                                   11.8%




    0%    11.8%
    1%       3.8%
                                                                     EXPECTED RETURNS AT DIFFERENT GROWTH RATES
    2%       4.8%
    3%       5.8%
    4%       6.8%                            25.0%

    5%       7.8%
    6%       8.8%                            20.0%
    7%       9.8%
                           EXPECTED RETURN




    8%     10.8%
    9%     11.8%                             15.0%
   10%     12.8%
   11%     13.8%                             10.0%
   12%     14.8%
   13%     15.8%
   14%     16.8%                             5.0%
   15%     17.8%
   16%     18.8%                             0.0%
   17%     19.8%                                     0%                5%               10%                 15%   20%   25%
   18%     20.8%                                                                              GROWTH RATE
   19%     21.8%
   20%     22.8%
                        Stock Price - Zero Growth




PRICE OF COMMON STOCK - ZERO GROWTH MODEL


INPUTS

LAST DIVIDEND            $    3.00 Dollar Amount
EXPECTED RETURN (ROR)        6.00% Decimal

OUTPUT

PRICE OF STOCK           $ 50.00
                                           Stock Price - Constant Growth




      PRICE OF COMMON STOCK - CONSTANT GROWTH MODEL


      INPUTS

      LAST DIVIDEND                            $   1.17 Dollar Amount
      EXPECTED RETURN (ROR (k)                   13.60% Decimal
      GROWTH RATE (g)                             8.10% Decimal
                                               k must > g
      OUTPUT

      PRICE OF STOCK                           $ 23.00



 9%   $   21.27
10%   $   61.58
11%   $   40.34                                         STOCK PRICE AT DIFFERENT RORs
12%   $   30.00
13%   $   23.88                  $70.00
14%   $   19.83
15%   $   16.96                  $60.00
16%   $   14.81
17%   $   13.15                  $50.00
18%   $   11.82
                   STOCK PRICE




19%   $   10.73                  $40.00
20%   $    9.83
21%   $    9.07                  $30.00


                                 $20.00


                                 $10.00


                                    $-
                                          5%       7%      9%    11%       13%   15%   17%   19%   21%
                                                                 REQUIRED RATE OF RETURN
                                    Stock Price - Variable Growth




         PRICE OF STOCK - VARIABLE GROWTH MODEL


INPUTS

LAST YEAR (e.g., 1996)           1996                      1997          1998         1999      2000         2001
LAST DIVIDEND                   $ 1.17
GROWTH RATE (g)                           30.00%                         30.00%       30.00%     8.10%       8.10% Decimal
EXPECTED RETURN (ROR) (k)        13.60% Decimal

OUTPUT

DIVIDENDS                                           $       1.52     $    1.98    $    2.57    $ 2.78    $    3.00
PRICE                                                                                          $ 54.61

CASH FLOWS                                          $       1.52     $    1.98    $    2.57    $ 57.39

PRESENT VALUE                    $39.09




              9%       $39.09
             10%   $   114.83                                              STOCK PRICE AT DIFFERENT RORs
             11%   $    74.92
             12%   $    55.48
                                                           $140.00
             13%   $    43.98
             14%   $    36.38                              $120.00
             15%   $    30.99
             16%   $    26.96                              $100.00
                                          PRICE OF STOCK




             17%   $    23.84                               $80.00
             18%   $    21.36
             19%   $    19.33                               $60.00
             20%   $    17.64
                                                            $40.00
             21%   $    16.22
                                                            $20.00

                                                               $-
                                                                     6%                  11%                 16%             21%
                                                                                         REQUIRED RATE OF RETURN
     REQUIRED RATE OF RETURN

				
DOCUMENT INFO
Shared By:
Stats:
views:317
posted:2/28/2008
language:English
pages:4