# How to Convert Historical Stock Prices to Returns by lqr10527

VIEWS: 162 PAGES: 10

How to Convert Historical Stock Prices to Returns document sample

• pg 1
```									Ch 03 Tool Kit                                                                                                                      5/26/2002

Chapter 3. Tool Kit for Risk and Return

The relationship between risk and return is a fundamental axiom in finance. Generally speaking, it is totally logical to assume
that investors are only willing to assume additional risk if they are adequately compensated with additional return. This idea is
rather fundamental, but the difficulty in finance arises from interpreting the exact nature of this relationship (accepting that risk
aversion differs from investor to investor). Risk and return interact to determine security prices, hence its paramount importance
in finance.

PROBABILITY DISTRIBUTION

The probability distribution is a listing of all possible outcomes and the corresponding probability.

Demand for the          Probability of this              Rate of Return on stock
company's products        demand occurring                  if this demand occurs
Martin Products          U.S. Water
Strong                     0.30                 100%                    20%
Normal                     0.40                   15%                   15%
Weak                      0.30                  -70%                   10%
1.00

EXPECTED RATE OF RETURN

The expected rate of return is the rate of return that is expected to be realized from an investment. It is determined as the weighted
average of the probability distribution of returns.

Demand for the          Probability of this                 Martin Products                                      U.S. Water
company's products        demand occurring          Rate of Return         Product                     Rate of Return           Product

Strong                   0.3             100%                             30%                       20%                   6%
Normal                   0.4              15%                             6%                        15%                   6%
Weak                    0.3             -70%                            -21%                       10%                   3%
1.0
EXPECTED RATE OF RETURN, r hat               =       15%                                              15%

MEASURING STAND-ALONE RISK: THE STANDARD DEVIATION

To calculate the standard deviation, there are a few steps. First find the differences of all the possible returns from the expected
return. Second, square that difference. Third, multiply the squared number by the probability of its occurrence. Fourth, find the sum
of all the weighted squares. And lastly, take the square root of that number. Let us apply this procedure to find the standard deviation
of Martin Products' returns.

Demand for the          Probability of this    Deviation from r hat  Squared deviation               Sq Dev * Prob.
company's products        demand occurring                                Martin Products
Strong                     0.3                       85%               72.25%                        21.68%
Normal                     0.4                       0%                 0.00%                         0.00%
Weak                      0.3                      -85%               72.25%                        21.68%
Sum:                      43.35%
Std. Dev. = Square root of sum                  65.84%          Sq. root can be
65.84%          found in two ways
Probability of this
demand occurring                                     U.S. Water
Strong                    0.3                      5%                    0.25%                    0.08%
Normal                    0.4                      0%                    0.00%                    0.00%
Weak                     0.3                      -5%                   0.25%                    0.08%
0.15%
Std. Dev. = Square root of sum                 3.87%            Sq. root can be
3.87%            found in two ways

MEASURING STAND-ALONE RISK: THE COEFFICIENT OF VARIATION
The coefficient of variation indicates the risk per unit of return, and is calculated by dividing the standard deviation by the expected
return.
Std. Dev.            Expected return                CV
Martin Products                   65.84%                     15%                    4.39
U.S. Water                         3.87%                     15%                    0.26

PORTFOLIO RETURNS

The expected return on a portfolio is simply a weighted average of the expected returns of the individual assets in the portfolio.
Consider the following portfolio.

Stock                         Portfolio weight       Expected Return
Microsoft                           0.25                 12.0%
General Electric                    0.25                 11.5%
Pfizer                              0.25                 10.0%
Coca-Cola                           0.25                  9.5%

Portfolio's Expected Return                               10.75%

PORTFOLIO RISK
Perfect Negative Correlation. The standard deviation of a portfolio is generally not a weighted average of individual standard
deviations--usually, it is much lower than the weighted average. The portfolio's SD is a weighted average only if all the securities in it
are perfectly positively correlated, which is almost never the case. In the equally rare case where the stocks in a portfolio are perfectly
negatively correlated, we can create a portfolio with absolutely no risk. Such is the case for the next example of Portfolio WM, a
portfolio composed equally of Stocks W and M.
Portfolio WM
Year                Stock W returns         Stock M returns (Equally weighted avg.)
1998                      40%                    -10%                   15%
1999                     -10%                     40%                   15%
2000                      35%                     -5%                   15%
2001                      -5%                     35%                   15%
2002                      15%                     15%                   15%
Average return                      15%                     15%                   15%
Standard deviation                22.64%                  22.64%                 0.00%
Correlation Coefficient                                                           -1.00

These two stocks are perfectly negatively correlated--when one goes up, the other goes down by the same amount. We could use
Excel's correlation function to find the correlation, but when exact positive or negative correlation occurs, an error message is given.
We demonstrate correlation in a later section.
Perfect Positive Correlation. Now suppose the stocks were perfectly positively correlated, as in the following example:

Year                Stock M returns          Stock M' returns         Portfolio MM'
1998                     -10%                     -10%                    -10%
1999                      40%                      40%                     40%
2000                      -5%                      -5%                     -5%
2001                      35%                      35%                     35%
2002                      15%                      15%                     15%
Average return                     15%                      15%                     15%
Standard deviation               22.64%                   22.64%                  22.64%
Correlation Coefficient                                                             1.00

With perfect positive correlation, the portfolio is exactly as risky as the individual stocks.

Partial Correlation. Now suppose the stocks are positively but not perfectly so, with the following returns. What is the portfolio's
expected return, standard deviation, and correlation coefficient?

Year                Stock W returns           Stock Y returns         Portfolio WY
1998                      40%                       28%                    34%
1999                     -10%                       20%                    5%
2000                      35%                       41%                    38%
2001                      -5%                      -17%                   -11%
2002                      15%                       3%                     9%
Average return                     15%                       15%                    15%
Standard deviation               22.64%                    22.57%                20.63%
Correlation coefficient                                                             0.67

Here the portfolio is less risky than the individual stocks contained in it.

We found the correlation coefficient by using Excel's "CORREL" function. Click the wizard, then Statistical, then CORREL, and
then use the mouse to select the ranges for stocks W and Y's returns. The correlation here is about what we would expect for two
randomly selected stocks. Stocks in the same industry would tend to be more highly correlated than stocks in different industries.

THE CONCEPT OF BETA
The beta coefficient reflects the tendency of a stock to move up and down with the market. An average-risk stock moves equally up
and down with the market and has a beta of 1.0. Beta is found by regressing the stock's returns against returns on some market index.
It is also useful to show graphs with individual stocks' returns on the vertical axis and market returns on the horizontal axis. The
slopes of the lines represent the stocks betas. We show a graph of the illustrative stocks in the screen to the right, and we use
regression to calculate betas below.                                         Beta Graphs
Returns on The Market and on Stocks L (for Low), A (for Average), and H (for High)
Year                     rM                   rL                    rA                     rH
2000                    10%                  10%                   10%                    10%
2001                    20%                  15%                   20%                    30%
2002                   -10%                  0%                   -10%                   -30%

Regression analysis is performed by following the command path: Tools => Data Analysis => Regression. This will yield the
Regression input box. If Data Analysis is not an option in your Tools menu, you will have to load that program. Click on the Add-Ins
option in the Tools menu. When the Add-Ins box appears, click on Analysis ToolPak and a check mark will appear next to the
Analysis ToolPak. Then, click OK and you will now be able to access Data Analysis. From this point, you must designate the Y input
range (stock returns) and the X input range (market returns). You can have the summary output placed in a new worksheet, or you
can have it shown directly in the worksheet, as we did here. The Regression dialog box for the regression of Stock H is as follows:

Note: When you get the menu box on the
screen, and the cursor blinking in the Y
Range slot, use the mouse to select the Y
range, and then click on the X range
box. Then fill in the X range the same
way.
Regression Output of Stock H Returns

SUMMARY OUTPUT

Regression Statistics                                          Beta Coefficient for Stock H = 2.00
Multiple R                               1
R Square                                 1
Standard Error                 1.38778E-17
Observations                             3

ANOVA
df                 SS                       MS                          F            Significance F
Regression                                      1       0.186666667               0.186666667                 9.69229E+32      2.04488E-17
Residual                                        1       1.92593E-34               1.92593E-34
Total                                           2       0.186666667

Coefficients          Standard Error              t Stat                  P-value             Lower 95%
Intercept                                     -0.1       9.08514E-18              -1.1007E+16               5.78378E-17               -0.1
X Variable 1                                  2.00       6.42417E-17             3.11324E+16                2.04488E-17                  2

Regression Output of Stock A Returns

SUMMARY OUTPUT
Beta Coefficient for Stock A is 1
Regression Statistics
Multiple R                                      1
R Square                                        1
Standard Error                                  0
Observations                                    3

ANOVA
df                  SS                      MS                      F                Significance F
Regression                                      1         0.046666667              0.046666667           #NUM!                 #NUM!
Residual                                        1                   0                        0
Total                                           2         0.046666667

Coefficients          Standard Error               t Stat                 P-value             Lower 95%
Intercept                                        0                    0                    65535         #NUM!                               0
X Variable 1                                  1.00                    0                    65535         #NUM!                               1
Regression Output of Stock L Returns

SUMMARY OUTPUT
The beta coefficient for Stock L is .5
Regression Statistics
Multiple R                                        1
R Square                                          1
Standard Error                          2.08167E-17
Observations                                      3

ANOVA
df                  SS                     MS                        F             Significance F
Regression                                       1        0.011666667             0.011666667                2.6923E+31       1.22693E-16
Residual                                         1        4.33334E-34             4.33334E-34
Total                                            2        0.011666667

Coefficients           Standard Error             t Stat                  P-value           Lower 95%
Intercept                                      0.05        1.36277E-17            3.66899E+15               1.73513E-16                0.05
X Variable 1                                    0.5        9.63625E-17            5.18874E+15               1.22693E-16                 0.5

CALCULATING THE BETA COEFFICIENT FOR AN ACTUAL COMPANY
Now we show how to calculate beta for an actual company, Wal-Mart Stores.

Step 1. Acquire Data

We downloaded stock prices and dividends from http://finance.yahoo.com for Wal-Mart, using its ticker symbol
WMT. We also downloaded data for the S&P 500 Index (^SPX), which contains 500 actively traded large stocks.

Step 2. Calculate Returns
We used the percentage change in adjusted prices (which already reflect dividends) for Wal-Mart to calculate
returns. We used the percentage change for the S&P 500 Index as the market return.

Now go to the bottom of the data, row 310.

Market Level (S&P 500                            Wal-Mart Adjusted
Date                    Index)                Market Return          Stock Price            Wal-Mart Return
August-01                    1,294.0                 -3.5%                  47.976                   -14.0%
July-01                   1,341.0                 -3.3%                  55.814                    14.5%
June-01                    1,386.8                 -2.6%                  48.725                    -5.6%
May-01                     1,424.2                 13.9%                  51.596                     0.0%
April-01                   1,250.3                 -0.6%                  51.586                     2.5%
March-01                    1,257.4                -11.1%                  50.350                     1.0%
February-01                    1,414.5                 -4.2%                  49.868                   -11.8%
January-01                    1,476.0                  1.5%                  56.548                     6.9%
December-00                    1,454.7                 -5.1%                  52.890                     1.9%
November-00                     1,532.3                 -2.2%                  51.891                    15.0%
October-00                    1,566.8                 -7.6%                  45.117                    -5.7%
September-00                    1,696.0                 -2.5%                  47.852                     1.2%
August-00                    1,739.6                  0.5%                  47.302                   -13.8%
July-00                   1,731.7                 -0.9%                  54.875                    -4.1%
June-00                    1,746.7                  5.0%                  57.234                     0.1%
May-00                     1,663.3                  0.0%                  57.172                     4.1%
April-00                   1,663.8                 -2.8%                  54.940                    -2.0%
March-00                    1,712.0                  2.3%                  56.056                    16.0%
February-00                    1,674.2                 -0.2%                  48.306                   -11.0%
January-00                    1,677.2                 -2.2%                  54.251                   -20.8%
December-99                     1,715.0                 4.9%                  68.495                     20.0%
November-99                      1,635.2                 7.5%                  57.057                      2.3%
October-99                     1,521.5                -1.9%                  55.758                     18.4%
September-99                     1,550.6                 0.2%                  47.094                      7.4%
August-99                     1,547.4                -4.2%                  43.829                      4.9%
July-99                    1,614.8                 4.9%                  41.789                    -12.4%
June-99                     1,538.7                 0.3%                  47.724                     13.3%
May-99                      1,533.4                 0.4%                  42.111                     -7.3%
April-99                    1,527.6                 4.9%                  45.445                     -0.2%
March-99                     1,456.2                -1.0%                  45.538                      7.2%
February-99                     1,470.6                 1.3%                  42.499                      0.1%
January-99                     1,451.7                 6.2%                  42.437                      5.6%
December-98                     1,367.6                 4.4%                  40.185                      8.2%
November-98                      1,309.7                17.5%                  37.125                      9.0%
October-98                     1,114.9                -0.2%                  34.044                     26.4%
September-98                     1,117.3                -1.5%                  26.927                     -7.3%
August-98                     1,134.7               -12.8%                  29.048                     -6.5%
July-98                    1,300.8                 3.8%                  31.078                      3.9%
June-98                     1,252.7                -0.2%                  29.909                     10.4%
May-98                      1,254.6                -0.1%                  27.103                      9.0%
April-98                    1,255.3                 4.5%                  24.860                     -0.5%
March-98                     1,200.9                 4.8%                  24.983                      9.9%
February-98                     1,145.7                 8.2%                  22.736                     16.3%
January-98                     1,058.7                -0.7%                  19.545                      1.0%
December-97                     1,066.7                 1.4%                  19.361                     -1.4%
November-97                      1,052.4                 5.9%                  19.635                     14.5%
October-97                       994.0                -6.0%                  17.153                     -4.4%
September-97                     1,057.3                 0.8%                  17.950                      3.4%
August-97                     1,049.4                   NA                  17.368                        NA

Average (Annual)                                                  6.9%                                             31.4%
Standard deviation
(Annual)                                                         18.7%                                             34.5%
Correlation between Wal-Mart and the market.                     27.4%
Beta (using the SLOPE function)                                    0.51

Step 3. Examine the Data

Using the AVERAGE function and the STDEV function, we found the average historical return and standard
deviation for Wal-Mart and the market. (We converted these from monthly figures to annual figures. Notice that you
must multiply the monthly standard deviation by the square root of 12, and not 12, to convert it to an annual basis.)
These are show in the rows above. Notice that Wal-Mart has a standard deviation about twice that of the market. We
also used the CORREL function to find the correlation between Wal-Mart and the market.

Step 4. Plot the Data and Calculate Beta
Using the Chart Wizard, we plotted the Wal-Mart returns on the y-axis and the market returns on the x-axis. We
also used the menu Chart > Options to add a trend line, and to display the regression equation and R 2 on the chart.
The chart is shown below.

Step 4. Interpret the Results
The beta coefficient is about .51, as shown by the slope of the coefficient in the regression equation on the chart. The
R2 of about 0.08 indicates that 8% of the variance in the stock return can be explained by the market. The rest of the
stock's variance is due to factors other than the market. If we had done this regression for a portfolio of 50 well
diversified stocks, we would have gotten an R2 of over 0.9.
y = 0.5056x + 0.0232      Historic Realized
Returns
R² = 0.0752          on Wal-Mart, rS
30%

20%

10%

0%
-30%      -20%      -10%          0%        10%        20%          30%
Historic Realized Returns
-10%                           on the Market, rM

-20%

-30%

THE SECURITY MARKET LINE
The Security Market Line shows the relationship between a stock's beta and its expected return.

Risk-free rate (Varies over time)                            6%
Market return (Also varies over time)                        11%            Required Return for a stock with beta = 0.50       8.5%
Beta (Varies by company)                                      0.5

With the above data, we can generate a Security Market Line that will be flexible enough to allow for changes in
any of the input factors. We generate a table of values for beta and expected returns, and then plot the graph as a scatter diagram.

Required Return
Beta                    8.5%
0.00                    6.0%
0.50                    8.5%
1.00                   11.0%
1.50                   13.5%
2.00                   16.0%
Security Market Line

18%
Required Return

12%

6%

0%
0.00   0.50          1.00          1.50       2.00          2.50
Beta

The Security Market Line shows the projected changes in expected return, due to changes in the beta coefficient. However, we can also
look at the potential changes in the required return due to variation of other factors, namely the market return and risk-free rate. In
other words, we can see how required returns can be influenced by changing inflation and risk aversion. The level of investor risk
aversion is measured by the market risk premium (rm-rrf), which is also the slope of the SML. Hence, an increase in the market
return results in an increase in the maturity risk premium, other things held constant.

We will look at two potential conditions as shown in the following columns:

OR
Scenario 1. Inflation Increases:                                                 Scenario 2. Investors become more risk averse:
Risk-free Rate                                        6%                         Risk-free Rate                            6%
Change in inflation                                   2%                         Old Market Return                        11%
Old Market Return                                    11%                         Increase in MRP                         2.5%
New Market Return                                    13%                         New Market Return                      13.5%
Beta                                                 0.50                        Beta                                     0.50

Required Return                                  10.5%                           Required Return                        9.75%
Now, we can see how these two factors can affect a Security Market Line, by creating a data table for the required return with
different beta coefficients.
Required Return
Beta               Original Situation     New Scenario 1       New Scenario 2
8.5%                 10.5%                 9.75%
0.00                     6.00%                8.00%                 6.00%
0.50                     8.50%               10.50%                 9.75%
1.00                    11.00%               13.00%                13.50%
1.50                    13.50%               15.50%                17.25%
2.00                    16.00%               18.00%                21.00%

The SML Under Different Conditions

25%
Required Returns

20%
Original
15%
Scenario #1
10%                                                     Scenario #2
5%
0%
0.00   0.50      1.00      1.50         2.00
Beta

The graph shows that as risk as measured by beta increases, so does the required rate of return on securities. However, the required
return for any given beta varies depending on the position and slope of the SML.

```
To top