VIEWS: 162 PAGES: 10 CATEGORY: Business POSTED ON: 11/22/2010
How to Convert Historical Stock Prices to Returns document sample
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 Adjusted 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 Adjusted 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 Adjusted 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.