Docstoc

Wsj Historical T Bill Rates - Excel

Document Sample
Wsj Historical T Bill Rates - Excel Powered By Docstoc
					                             SAMPLE SOLUTION
2.)
       PE ratio of WMT    Relative PE ratio Average PE ratio

             35.2                1.98               17.78
      The PE ratio of WMT is about twice as much as the
      market. WMT is an agressive stock. Investors perceive
      WMT to be either lower risk or higher growth or both
      than an average stock.

      WMT # of shares WMT # of shares
3.)     outstanding in      actually       The difference is due to stock splits.
      1988, as reported  outstanding in
       by Valueline (in 1988 (in millions)
           millions)
            4,525              566




4.)     Growth rate of      Growth rate of     Growth rate of
       dividends in the    dividends in the   dividends in the
         past 5 years       past 10 years     first 5 of the last
                                                   10 years
           18.00%               21.5%               25.10%

      The growth rate of dividends during the last five years has been less than the growth rate
      during the first five years of the last ten years to give an average of 21.5% per year over
      the last ten years.




                                                          11
5.)                  Price per Share    Stock Splits   Dividends ($)
                    ($) for WMT as of w/applicable      declared per
           Year     1/1/19XX or 20XX date & ratio share for WMT for
                       (from WSJ)    (from Valueline) the year per VL
            88             26.00                            0.02
            89             31.38                            0.03                     VL reports dividends
            90             44.88         2/90 2:1           0.04                     per current share,
            91             30.25                            0.04                     and therefore,
            92             58.88                            0.05                     rewrites the historical
            93             64.00         2/93 2:1           0.07                     information.
            94             25.00                            0.09
            95             21.25                            0.10
            96             22.25                            0.11
            97             22.75                            0.14
            98             39.44                            0.16
            99             81.44         3/99 2:1           0.20
            00             69.13                            0.24
            01             53.13                            0.28


                  WMT Price Per Share on Jan 1, (1988-2001) as per WSJ

                                                                                           90
                                                    2:1 Stock Split, 03/99
                                                                                           80




                                                                                                      Price per Share in $
                  2:1 Stock Split, 2/90                                                    70
                                                                                           60
                                                                                           50
                                                                                           40
                                                                                           30
                                                                                           20
                                                                                           10
       2:1 Stock Split, 2/93                                                               0
      88    89     90   91     92    93    94 95 96              97   98   99   00    01
                                          January 1


                    WMT Dividends per Year (1988-2000) as per VL

                                                                                      0.30
                                                                                                Annual Dividends in $




                                                2:1 Stock Split, 3/99
                                                                                      0.25
                                                                                      0.20
           2:1 Stock Split, 2/90
                                                                                      0.15
                        2:1 Stock Split, 2/93
                                                                                      0.10
                                                                                      0.05
                                                                                      0.00
      88 89 90 91 92 93 94 95 96 97 98 99 00 01
                         Year




                                                            12
6.)   The table and graphs below are based on the following assumptions: (1) the value of one share of WMT common stock at the time of
      purchase (January 1, 1988) is equal to the price of the stock at that point in time; (2) the subsequent value of the one share purchased
      is equal to the number of shares held (adjusted for splits and stock dividends) times the current market price; (3) the dividends
      received are assumed to be received entirely at year end, rather than quarterly as usually is the case.

                        1               2                       3                         4                 5                 6
                Price per Share    Stock Splits  Assuming 1 share purchased Value ($) of one Dividends ($) per Dividends ($) received
                ($) for WMT as     w/applicable as of 1/1/88, number of shares share purchased share for WMT on Dec. 31 on 1 share
                 of Jan 1 (from    date & ratio  held (adjusted for stock splits) 1/1/88 as of Jan for the year (from purchased 1/1/88
       Year           WSJ)      (from Valueline)            on Jan 1                      1             Valueline)    adjusted for splits
        88           26.00                                      1                      $26.00             $0.02              0.16
        89           31.38                                      1                       31.38              0.03              0.24
        90           44.88          2/90 2:1                    1                       44.88              0.04              0.32
        91           30.25                                      2                       60.50              0.04              0.32
        92           58.88                                      2                      117.75              0.05              0.40
        93           64.00          2/93 2:1                    2                      128.00              0.07              0.56
        94           25.00                                      4                      100.00              0.09              0.72
        95           21.25                                      4                       85.00              0.10              0.80
        96           22.25                                      4                       89.00              0.11              0.88
        97           22.75                                      4                       91.00              0.14              1.12
        98           39.44                                      4                      157.75              0.16              1.28
        99           81.44          3/96 2:1                    4                      325.75              0.20              1.60
        00           69.13                                      8                      553.00              0.24              1.92
        01           53.13                                      8                      425.00              N/A               N/A
                                                                8


                 Value of 1 Share of WMT Purchased on 1/1/88                         Dividends Received at the Year-End from 1 Share
                                                                                                  Purchased on 1/1/88
                                                                                                                                            Dividends
                                                                                                                    2.50
                                             600                                                                                            Received

                                             500                                                                    2.00




                                                                                                                           Dividends in $
                                                   Value in $




                                             400                                                                    1.50
                                             300
                                                                1 Share Value                                       1.00
                                             200
                                             100                                                                    0.50

                                             0                                                                      0.00
      88   90     92     94   96   98   00                                      88   90   92   94   96    98   00
                       1/1/XXXX                                                            12/31/XXXX




                                                                           13
7.)                                                          Assuming 1 share                             Price per
                                 Stock Splits with        purchased as of 1/1/88,      Value ($) of one share ($) as
            Price per Share      applicable date &         number of shares held       share purchased would have
           ($) for WMT as of        ratio (from         (adjusted for stock splits) as   1/1/88 as of   been reported
      Year Jan 1 (from WSJ)         Valueline)                  of 1/1/19XX               1/1/19XX         by VL
       88         26.00                                               1                      26.00          3.25
       89         31.38                                               1                      31.38          3.92
       90         44.88                2/90 2:1                       1                      44.88          5.61
       91         30.25                                               2                      60.50          7.56
       92         58.88                                               2                     117.75         14.72
       93         64.00                2/93 2:1                       2                     128.00         16.00
       94         25.00                                               4                     100.00         12.50
       95         21.25                                               4                      85.00         10.63
       96         22.25                                               4                      89.00         11.13
       97         22.75                                               4                      91.00         11.38
       98         39.44                                               4                     157.75         19.72
       99         81.44                3/96 2:1                       4                     325.75         40.72
       00         69.13                                               8                     553.00         69.13
       01         53.13                                               8                     425.00         53.13
                                                                      8
8.)
            Value ($) of one Capital Gains Yield         Dividends ($) received for Dividend Yield for
           share purchased for the assuming 1           the year assuming 1 share the year assuming WMT Total
           1/1/88 as of Jan 1 share purchase as              purchased 1/1/88            1 share       Yield for the
      Year                        of 1/1/88                                         purchased as of        Year
       88         26.00             0.2067                         $0.16                 0.0062           0.2129
       89         31.38             0.4303                          0.24                 0.0076           0.4379
       90         44.88             0.3482                          0.32                 0.0071           0.3553
       91         60.50             0.9463                          0.32                 0.0053           0.9516
       92        117.75             0.0870                          0.40                 0.0034           0.0904
       93        128.00            -0.2188                          0.56                 0.0044          -0.2144
       94        100.00            -0.1500                          0.72                 0.0072          -0.1428
       95         85.00             0.0471                          0.80                 0.0094           0.0565
       96         89.00             0.0225                          0.88                 0.0099           0.0324
       97         91.00             0.7335                          1.12                 0.0123           0.7458
       98        157.75             1.0650                          1.28                 0.0081           1.0731
       99        325.75             0.6976                          1.60                 0.0049           0.7025
       00        553.00            -0.2315                          1.92                 0.0035          -0.2280
       01        425.00              N/A                            N/A                    N/A             N/A

                            Dividend Yield,Capital Gain Yield, and Total Yield


         1.2000
         1.0000
         0.8000
         0.6000                                                                                 Total Yield
         0.4000                                                                                 Dividend Yield
         0.2000                                                                                 Capital Gain Yield
         0.0000
        -0.2000        88   89    90    91   92   93    94    95   96   97   98   99   00

        -0.4000
                                                       Year



                                                                                                                       14
9.)
                 Total Annual Total Yields
                  Yields for for the Market
         Year       WMT         Portfolio
          88        0.2129        0.1681
          89        0.4379        0.3149
          90        0.3553       -0.0317
          91        0.9516        0.3055
          92        0.0904        0.0767
          93       -0.2144        0.0999
          94       -0.1428        0.0131
          95        0.0565        0.3743
          96        0.0324        0.2307
          97        0.7458        0.3336
          98        1.0731        0.2858
          99        0.7025        0.2104    The market portfolio used is the Large Cap
          00       -0.2280       -0.0911    portfolio of SBBI.



                          Yearly Market & WMT Total Returns Compared

                                                               1.20
                                                               1.00
                                                               0.80                    Total Annual Yields for
                                                                        Total Return
                                                               0.60                    WMT

                                                               0.40                    Total Yields for the Market
                                                                                       Portfolio
                                                               0.20
                                                               0.00
       88 89 90 91 92 93 94 95 96 97 98 99 00                  -0.20
                                                               -0.40
                               Year



10.)              Total Risk    Correlation   Average     Coefficient
                  (Standard     Coefficient   Returns         of
         Item     Deviation)        (r)        (Arith.)    Variation
        WMT         0.4401       0.5921        0.3133       1.4047
        Market      0.1509                     0.1762       0.8565

        Because the correlation coefficient between the market and WMT is less than 1, WMT does not move
        perfectly with the market. However, because the value is positive it can be said that in general WMT
        moves in the same direction as the market. In absolute terms, WMT is more risky than is the market
        (WMT S.D. > Market S.D.) over the period covered. The coefficient of variation shows that WMT has
        more risk per unit of return.




                                                          15
11.)   The total risk of a security, or a portfolio, is composed of two types of risk--systematic
       (nondiversifiable) and unsystematic (diversifiable) risk.

       Total RiskWMT (s2) = Systematic Risk + Unsystematic Risk =          0.1937
          Systematic Risk =       0.0679
          Unsystematic Risk =     0.1258

       By not including WMT in a well-diversified portfolio, the holder of this stock is giving up the
       opportunity to eliminate 0.1258, WMT's unsystematic risk, measured as variance.


12.)                                Dividends      Total Cash
        Date as of Initial & Final Received         Flows on
           12/31      Cashflows       12/31           12/31
            87          -$26.00       $0.00          -$26.00
            88                         0.16            0.16
            89                         0.24            0.24
            90                         0.32            0.32
            91                         0.32            0.32
            92                         0.40            0.40
            93                         0.56            0.56
            94                         0.72            0.72
            95                         0.80            0.80
            96                         0.88            0.88
            97                         1.12            1.12
            98                         1.28            1.28
            99                         1.60            1.60
            00           425.00        1.92           426.92
       Internal Rate of Return (IRR) =                0.2464

          The first date, 12/31/1987 is actually 1/1/1988, the date of purchase

       This analysis assumes that one share of WMT was purchased on 1/1/1988 and held until
       12/31/2000 (or 1/1/2001). The amounts received, and subsequently the IRR obtained, fully reflect
       all stock splits. All taxes and transaction costs are ignored. In addition, the above computations
       assume that all dividends were received at year end, rather than quarterly. Finally, it is assumed
       that all dividends received were reinvested at 0.2464, the internal rate of return.

       WMT's IRR is the discount rate that equates the present value of the future cash flows (dividends
       and selling price) to the purchase price.




                                                     16
13.) Geometric Average Rates of Returns

           Geometric       Geometric
            Average         Average
           ReturnWMT       Returnmarket
             0.2467          0.1668

       SUMMARY OUTPUT

            Regression Statistics
       Multiple R          0.5921369
       R Square            0.3506261
       Adjusted R Square 0.2915921
       Standard Error      0.3704502
       Observations               13

       ANOVA
                                df              SS         MS         F     Significance F
       Regression                     1     0.815082717 0.8150827 5.9393929   0.03299266
       Residual                      11     1.509566718 0.1372333
       Total                         12     2.324649436

                           Coefficients Standard Error t Stat P-value Lower 95%   Upper 95%
       Intercept            0.0090286 0.161699948 0.0558355 0.9564741 -0.34687077 0.364927966
       X Variable 1         1.7273107 0.708760398 2.437087 0.0329927 0.167338828 3.287282641

14.)
                                          Difference from             Possible Reasons for the Differences Between Betas
        Source for Beta       Beta        My Calculation
       Our Calculation       1.7273             N/A       1.) Different market portfolio used for comparison (ex. NYSE Composite,
       Valueline             1.1500           -0.5773     DJIA, Nasdaq Composite, Wilshire Index, etc)
                                                          2.) Different time period used for measurement (different starting / ending
                                                          dates used, longer / shorter periods of observation, etc).
                                                          3.) Frequency of observations (daily, quarterly, yearly, etc).
                                                          4.) Precision of measurements taken.


       Based on our calculation of beta, WMT's risk is more than that of the average stock. This means that WMT would, in
       general, be classified aggressive. Valueline's beta for WMT suggests that WMT's risk is slightly above that of the market.
`
15.) WMT's Alpha =         0.0090

       Alpha is the point where the regression line between the market's returns and a company's (or portfolio's) returns crosses the
       Y-axis. Based on WMT's regression line, when the market earns 0% return, WMT will earn 0.90%.

16.) The coefficient of determination, R-square, for the regression model is = 0.3506

       The R-square indicates that the regression model is fairly robust.
       35% of the variability in WMT returns can be explained by the market returns.

17.) The characteristic line for WMT is

       WMT return = 0.0090 + 1.73 x Return on the market portfolio

18.) Historical Growth Rate of Dividends =                0.2301




                                                                    17
19.)   Required Rate of Return for WMT (rWMT)

       RF = Risk Free Rate (T-Bill rate with 30 day maturity from WSJ, Jan 2, 2001) =         0.025
       rm = Market Return (arithmetic average ) =       0.1762

       Because we do not have much faith in our calculated beta,
       we will use the beta reported by VL to estimate the
       required rate of return.
       rWMT =      0.1988

20.)   Intrinsic Value of WMT as of January 1, 2001 using Valueline projected cash flows

              g (2001-2006) = 0.1230

       D1 (2001)    D2 (2002     D3 (2003) D4 (2004) D5 (2005) D6 (2006) Selling Price
        $0.28        $0.31         0.35      0.40      0.45      0.50       77.50

             V0 =        27.30

       The above analysis assumes that the dividends from 2001 to 2006 will grow at a constant rate (computed as
       "g (2001-2006)" above.) The expected dividend at time 1 (D1) is given in the Valueline chart, as are D2 and
       D6. As before, all dividends are assumed to be collected at year end. The selling price used is the average
       selling price projected by Valueline for 2006 year end.

21.)   Market Price as of January 2, 2001 is $41.44

       From the analysis above, it can be said that WMT is over-priced. Ideally, a holder of any shares of WMT
       should sell immediately.

       This assumes, however, that the above analyses are correct in assessing the value of WMT. This more
       than likely is not the case, because of the assumptions and estimates that were necessarily made in
       calculating WMT's intrinsic value. The cash flows used are merely estimates created by either Valueline or
       myself (explained in detail in #14). A problem related to this is that even though dividend streams tend to be
       relatively stable, actual future dividend disbursements are independent of those in the past. They are
       completely at the discretion of the board of directors and therefore could vary largely from the estimated
       values.

       Another weakness evident in our intrinsic evaluation is that the test period (13 years) used to calculate beta,
       and subsequently the discount rate, is too long. Similarly, the sample frequency (yearly) is too large. The
       sample has only 13 data points. More reliable results could be obtained if the data used consisted of monthly
       or daily returns and over a shorter period of time, say 5 years.

22.)   Wealth as of December 31, 2000 assuming a $100 investment in WMT on January 1, 1988

                = $1,757.55

       Wealth as of December 31, 2000 assuming a $100 investment in the market on January 1, 1988

                = $743.25

       We assumed that all dividends received from WMT are reinvested in WMT and market dividends are
       reinvested in the market portfolio. Also, that there are no taxes and transaction costs associated with these
       investments.




                                                      18

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:114
posted:11/19/2010
language:English
pages:8
Description: Wsj Historical T Bill Rates document sample