jagomart
digital resources
picture1_Stocks Portfolio Excel 43125 | Capmmodel


 182x       Filetype XLS       File size 0.14 MB       Source: msuweb.montclair.edu


File: Stocks Portfolio Excel 43125 | Capmmodel
the capital asset pricing model 2000 p lebel i evaluating individual stock and market risk whether to buy or sell a stock is more complicated than looking at the implied ...

icon picture XLS Filetype Excel XLS | Posted on 16 Aug 2022 | 3 years ago
Partial file snippet.
                                         The Capital Asset Pricing Model
        ©2000                                                                                 P. LeBel
      I. Evaluating Individual Stock and Market Risk
        Whether to buy or sell a stock is more complicated than looking at the implied and actual 
        stock market price.  Apart from inflation and taxes, one critical variable is the given equity 
        rate of return.  Usually, this refers to the overall sector or industry, rate of return against which 
        one can evaluate a particular stock, or group of sector stocks.  Stocks as a whole embody two 
        types of variability:  one is the level of risk associated with a particular stock and the other is the 
        level of risk associated with the market as a whole.  The former is known as diversifiable risk 
        since one can make a decision to buy or sell an individual stock.  While one also can make a 
        decision whether to buy or sell any stock portfolio as a whole, it is useful in the first instance to 
        compare the individual diversifiable rate of return from the system wide rate of return.  Given 
        one's risk preferences, one then has a consistent basis as to whether to hold any stocks or 
        other assets for a given time period. 
        A. Portfolio Selection
        Selection of a particular portfolio begins with a reference portfolio.  This can be an index of stocks 
        such as the Dow-Jones industrial average, the S&P 500 portfolio of stocks, the Russell 2000,
        the Russell 5000, or any number of other stock market indices.  Each of these portfolios reflects 
        a basket of assets whose value is traced over time.  While the overall correlation among these 
        indices is expected to be positive, because each index has stocks reflecting different characteristics, 
        there will be a different level of volatility, and a different corresponding rate of return for each.  
        With this caveat, one can then proceed to the selection of an individual portfolio.  We review here 
        some better known portfolio models, each of which enable us to consider what equity rate of return
        might be considered in the preceding formulas.  Most of them build on Harry M. Markowitz'
        Portfolio Selection (New Haven:  Yale University Press, 1959)
         B. The Basic CAPM, or Capital Asset Pricing Model
             One of the more popular models used in stock portfolio construction is the capital asset pricing 
        model,  or CAPM for short.  This was first developed by William F. Sharpe, in "Capital Asset Prices:  
        A Theory of Market Equilibrium Under Conditions of Risk," Journal of Finance, 19 (September 1964), 
        pp. 425-42.  Sharpe's approach uses a linear regression model of individual stocks to generate 
        deviations from the excess returns of a portfolio of assets.  A portfolio's excess rate of return is 
        calculated in reference to a risk-free rate of return.  The risk-free rate is usually a benchmark asset 
        such as the interest rate on U.S. Treasury securities. The idea of equilibrium is that when one
         regresses the excess rate of return of individual stocks against the excess return of a market portfolio, 
        the resulting characteristic line should go through the origin to reflect the fact that investors can 
        diversify stock portfolios in such a way that they can eliminate any level of unsystematic risk.  
        It is this orthogonal regression expectation that forms the basis of the efficient market hypothesis,
        i.e., that all stocks embody all relevant information about present and future conditions such that 
        economic agents have valued individual stocks according to their underlying level of systematic risk.
                                                  Page 1
                                                                   Figure 1
                                              The Basic Capital Asset Pricing Model
               Excess Return on Stock
              0.08
              0.06
                    f(x) = 0.004717391304348 x − 0.052717391304348
              0.04  R² = 0.816001524944981
              0.02
              0.00
                    4   0    9   8    6    4   3    3   1    1    0   1    2   3    4    4   5    5    6   7    0   1    3    5
                    1   1    0   0    0    0   0    0   0    0    0   0    0   0    0    0   0    0    0   0    1   1    1    1
             -0.02 .    .    .   .    .    .   .    .   .    .    .   .    .   .    .    .   .    .    .   .    .   .    .    .
                    0   0    0   0    0    0   0    0   0    0    0   0    0   0    0    0   0    0    0   0    0   0    0    0
                    -   -    -   -    -    -   -    -   -    -
             -0.04
             -0.06
             -0.08
                                                                                     Excess Return on Market Portfolio
                            Time Series Sample for Estimation of the Capital Asset Pricing Model
                                  Portfolio:         Stock:                Characteristic Line:
                      Quarter Excess Returns     Excess Returns            Estimated Individual Returns
                            15       -0.14            -0.06           -0.06                         (Van Horne, 1989, p.108)
                             4       -0.10            -0.05           -0.04                 SUMMARY OUTPUT
                            14       -0.09            -0.06           -0.04
                             9       -0.08            -0.02           -0.04          Regression Statistics
                             3       -0.06            -0.04           -0.03          Multiple R     0.9208
                            16       -0.04            -0.02           -0.02           R Square      0.8479
                             6       -0.03            0.00            -0.01       Adj. R Square     0.8410
                            23       -0.03            -0.01           -0.01      Standard Error     0.0147
                             8       -0.01            -0.01           0.00        Observations              24
                            13       -0.01            0.01            0.00             F        122.687782395
                            10       0.00             0.04            0.00
                            22       0.01             -0.01           0.01                        Coefficients   t Stat
                             5       0.02             0.02            0.01            Intercept         0.0011   0.3681
                            21       0.03             0.01            0.01          X Variable 1        0.4561 11.0765
                            12       0.04             -0.01           0.02
                            24       0.04             0.02            0.02
                             1       0.05             0.04            0.02
                            20       0.05             0.03            0.02
                            18       0.06             0.02            0.03
                             7       0.07             0.02            0.03
                             2       0.10             0.05            0.05
                            19       0.11             0.04            0.05
                            11       0.13             0.07            0.06
                            17       0.15             0.07            0.07
                                                               Page 2
           Estimation Procedure:
         1  Compile time series data on excess returns on the market portfolio and on excess returns 
           of an individual stock. Excess returns for an individual stock refer to the rate of return relative
            to the risk-free rate of return.  The risk-free rate of return generally is based on a widely held 
           asset such as the rate on government securities. Excess returns for a market portfolio are 
           calculated in the same way as for an individual stock. The Excess Return for the market portfolio 
           can be computed using a basket of representative stocks such as the S&P 500, or the Dow Jones
           Industrial Average.  
         2  Sort the data set of portfolio and individual stock excess returns by the portfolio excess returns, 
           which is to be portrayed along the X-axis.
         3  Compute a linear regression on the Excess Return of an individual stock as a function of the 
           Excess Return of the Market Portfolio.
         4  Beta, or the slope of the CAPM regression line, defines the responsiveness of the excess returns
            for security j in excess of the risk-free rate relative to those of the market.  Beta measures systematic, 
           or unavoidable, risk. If Beta is equal to 1.00, then excess returns for the stock vary proportionally 
           with excess returns for the market portfolio. A Beta higher than 1.00 means that excess returns vary
            by a greater amount than variation in excess returns for the market portfolio.  Stocks with a Beta 
           greater than one are considered to be aggressive, while those with a Beta less than 1 are 
           considered to be conservative.  In general, higher returns to a portfolio require that the individual 
           Beta values be in excess of 1.
         5  In the case of the sample above, the individual stock has less unavoidable risk than the market overall.
         6  Selection of the risk free rate in the CAPM depends on the maturity of the security used in the 
           calculation of the required rate of return.  Ideally, one should match the maturity of the risk free 
           security with the time horizon of the firm's equity-financed investment, a practice that does not 
           hold true in most instances.  Van Horne (1989) uses an intermediate rate such as the yield on 
           1 or 2 year Treasury bills (Van Horne, 1989, p. 378).
         7  The required rate of return on equity capital may be sensitive to the underlying CAPM relationship.  
           Biases include whether the true relationship is linear or non-linear, and whether the market basket 
           of securities is representative of  the market as a whole.  If the S&P has rates of return that are less 
           than a broader basket such as the Russell 2000 or the Russell 5000, then use of the S&P as the 
           market excess return measure will be downward biased, as will the true required rate of return 
           for equity capital.
        Components of the Capital Asset Pricing Model:  Alpha, Beta, and Variability:
             If the alpha of a stock were greater than zero, agents would recognize expected return greater
        than that required for the systematic risk involved.  They would buy the stock, which would raise
        its price and lower its expected return.  This would lower the characteristic line until the stock 
        provides the same expected return as other stocks with that systematic risk.  At this point the 
        vertical intercept of the characteristic line would be zero.  Conversely, if the alphs of a stock is 
        negative, agents will sell it, causing the market price to decline and the expected return to rise, 
        until the characteristic line passes through zero once again. (cf. Van Horne, 1989, p. 91)
             While market sales and purchases tend to revert the alpha (or intercept) of a given stock
        through the origin, beta measures the the unavoidable, or systematic risk of a stock in comparison
        to the market as a whole.  Stocks with a beta (or slope) greater than one have a higher level of 
        systematic risk, meaning that movements in excess returns for the individual stock are greater than 
        for the market portfolio as a whole.  If the CAPM characteristic line is accurate, one cannot diversify
        the level of systematic risk of the stock. 
             The third component of the CAPM is variability.  This is reflected in the degree of dispersion 
                                               Page 3
           of data used in estimating a stock's characteristic line.  The greater the dispersion of points,
           the greater will be the unsystematic risk of the stock.  One can reduce unsystematic risk by 
           changing the mix of stocks in a portfolio.  The level of portfolio risk is inversely related to the 
           number of stocks, as illustrated in the following:
                                                                   Figure 2           
                                                     Systematic RiskUnsystematic Risk
                                  Systematic, Unsystematic, and Total Market Risk
                                                   1         0.6                1.4320                0.6
                                                   2         0.6                1.2923
                   1.6                             3         0.6                1.1760
                                                   4         0.6                1.0792
                                                   5         0.6                0.9987
                                                   6         0.6                0.9317
                   1.4                             7         0.6                0.8760
                                                   8         0.6                0.8297
                                                   9         0.6                0.7911
                   1.2                           10          0.6                0.7590
                                                 11          0.6                0.7323
                                                 12          0.6                0.7101
                                                 13          0.6                0.6916
                     1                           14          0.6                0.6762
                                                 15          0.6                0.6634
                                                 16          0.6                0.6527
                   0.8                           17          0.6                0.6439
                                                 18          0.6                0.6365
                                           Unsystematic risk
                                                 19          0.6                0.6304
                   0.6                           20          0.6                0.6253
                                                 21          0.6                0.6210
                          Total Risk             22          0.6                0.6175
                                                 23          0.6                0.6146
                   0.4                           24          0.6                0.6121
                                           Systematic risk
                                                 25          0.6                0.6101
                                                 26          0.6                0.6084
                   0.2                           27          0.6                0.6070
                                                 28          0.6                0.6058
                                                 29          0.6                0.6048
                     0                           30          0.6                0.6040
                       1     2    3     4    5    6     7    8    9    10   11    12   13   14    15   16    17   18   19    20
                Unsystematic risk can be reduced by increasing the number of stocks in a portfolio. As this occurs,
            the relationship between excess returns on an individual stock will correlate more closely with the
            excess returns in the market portfolio.  As shown in Figure 2, as the number of stocks increases,
            the degree of unsystematic risk converges to the level of systematic risk.  
           Calculating the Required Rate of Return of a Stock in a Portfolio
                If financial markets are efficient and investors can efficiently diversify, unsystematic risk becomes
            minor.  The major risk is the systematic component.  The greater the beta of a stock, the greater the 
           risk of that stock, and the greater the required rate of return.  If unsystematic risk is diversified out,
            the required rate of return for a given stock is defined as:
                 R =i+(R −i)β               where: Rm = the expected return for themarket portfolio
                   j        m      j                 Bj = the beta coefficient for stock j
                                                     I = the risk-free rate of return
                                                         Examples:
                           Risk free rate of return =           5.00%            5.00%            5.00%             5.00%
                                                                      Page 4
The words contained in this file might help you see if this file matches what you are looking for:

...The capital asset pricing model p lebel i evaluating individual stock and market risk whether to buy or sell a is more complicated than looking at implied actual price apart from inflation taxes one critical variable given equity rate of return usually this refers overall sector industry against which can evaluate particular group stocks as whole embody two types variability level associated with other former known diversifiable since make decision an while also any portfolio it useful in first instance compare system wide s preferences then has consistent basis hold assets for time period selection begins reference be index such dowjones industrial average russell number indices each these portfolios reflects basket whose value traced over correlation among expected positive because reflecting different characteristics there will volatility corresponding caveat proceed we review here some better models enable us consider what might considered preceding formulas most them build on harr...

no reviews yet
Please Login to review.