Document Sample

Ross, Westerfield, and Jordan's Excel Master Essentials of Corporate Finance, 7 th edition by Brad Jordan and Joe Smolira Version 7.0 Chapter 10 In these spreadsheets, you will learn how to use the following Excel f Column charts COUNTIF Sorting data Filtering data Rank and percentile AVERAGE Sorting data (2) Frequency distribution Frequency distribution charts Histograms VAR STDEV VARP STDEVP NORMDIST NORMINV Descriptive statistics GEOMEAN The following conventions are used in these spreadsheets: 1) Given data in blue 2) Calculations in red NOTE: Some functions used in these spreadsheets may require that the "Analysis ToolPak" or "Solver Add-In" be installed in Excel. To install these, click on the Office button then "Excel Options," "Add-Ins" and select "Go." Check "Analysis ToolPak" and "Solver Add-In," then click "OK." w to use the following Excel functions: hese spreadsheets: ets may require that stalled in Excel. Chapter 10 - Section 1 Returns Calculating returns in Excel is a relatively simple matter since we only need to input basic equations. Consider the i Shares: 100 Beginning price: $ 37.00 Ending price: $ 40.33 Dividend per share: $ 1.85 With this information, we can calculate the dollar returns and percentage returns as: Total dollar capital gains: $ 333.00 Dividend income: $ 185.00 Total dollar return: $ 518.00 Capital gains return: 9.00% Dividend yield: 5.00% Total return: 14.00% Yahoo! Finance Returns A popular website that provides daily stock prices is Yahoo! Finance. However, if you use the prices quoted on this careful to use the correct information. Yahoo! Finance reports two closing stock prices, the actual closing price and dividends. In a stock split, the number of shares is increased and the stock price is decreased. For example, in a 2-fo since shareholders would receive 2 shares for every 1 share they currently own, and the stock price would be halve Suppose a stock is currently trading at $120 per share and undergoes a 2-for-1 stock split. Also assume that the sto price on Yahoo! Finance would report prices of $120 and $60, respectively, which looks like a 50 percent decrease during the day was zero because although the stock price was cut in half, the number of shares they owned was do reported as $60 for both days. The adjusted close reported on Yahoo! Finance also adjusts for dividends. Consider a stock that is selling for $100 a $108 at the end of May. The stockholder return for this period was ($108 - 100 + 5) / $100 = 13 percent. In this cas while the adjusted close for the end of April would be $95.581, which is a return of ($108 - 95.581) / $95.581 = 13 p a 12 month period using both the closing price and dividend, and the adjusted close. Return with closing price Date Close Dividend Adj Close and dividend 5/30/2008 $ 129.43 $ 0.50 $ 126.85 6/30/2008 $ 118.53 $ 116.17 -8.42% 7/31/2008 $ 127.98 $ 125.43 7.97% 8/29/2008 $ 121.73 $ 0.50 $ 119.77 -4.49% 9/30/2008 $ 116.96 $ 115.08 -3.92% 10/31/2008 $ 92.97 $ 91.48 -20.51% 11/28/2008 $ 81.60 $ 0.50 $ 80.74 -11.69% 12/30/2008 $ 84.16 $ 83.27 3.14% 1/30/2009 $ 91.65 $ 90.68 8.90% 2/27/2009 $ 92.03 $ 0.50 $ 91.55 0.96% 3/31/2009 $ 96.89 $ 96.39 5.28% 4/30/2009 $ 103.21 $ 102.67 6.52% 5/29/2009 $ 106.28 $ 0.55 $ 106.28 3.51% Notice, the return calculations are very similar. The reason they are not exact is that Yahoo! Finance reports the ad slight difference in the return calculation. Consider the example we used above. Using the adjusted price of $95.58 percent, not 13 percent. If you need total returns, the adjusted close will give you a fairly accurate return calculatio won't download prices and dividends in the same spreadsheet. However, if you need capital gains returns and divid need to use the closing price and the dividends, not the adjusted close. nput basic equations. Consider the information from the Video Concepts Company: ns as: f you use the prices quoted on this website to calculate the return of a stock, you must be k prices, the actual closing price and the adjusted close which is adjusted for stock splits and e is decreased. For example, in a 2-for-1 stock split, the number of shares would be doubled , and the stock price would be halved. stock split. Also assume that the stock price remains unchanged during the day. The closing ch looks like a 50 percent decrease in the stock price. In actuality, the shareholder return umber of shares they owned was doubled. In this case, the adjusted closing price would be ider a stock that is selling for $100 at the end of April, pays a dividend of $5, and has a price of + 5) / $100 = 13 percent. In this case, the adjusted close at the end of May would be $108, n of ($108 - 95.581) / $95.581 = 13 percent. Below, we have calculated the return for IBM over close. Return with adjusted close -8.42% 7.97% -4.51% -3.92% -20.51% -11.74% 3.13% 8.90% 0.96% 5.29% 6.52% 3.52% that Yahoo! Finance reports the adjusted close to the nearest cent. This rounding can cause a . Using the adjusted price of $95.581, calculate for yourself that the return is actually 12.99 ou a fairly accurate return calculation that is much easier, especially since Yahoo! Finance need capital gains returns and dividend yields separately, or very accurate returns, you will Chapter 10 - Section 2 The Historical Record In the text and on the next tab, we show the historical returns by year for various asset categories for the period 19 will often allow you to better visualize the data over time. Below, we have produced a chart similar to Figure 10.5 i stock returns. Large Company Stock Returns: 1926 60% 40% 20% Total Annual Return 0% 1928 1954 1926 1930 1932 1934 1936 1938 1940 1942 1944 1946 1948 1950 1952 1956 1958 1960 1962 1964 1966 1968 1970 1972 -20% -40% -60% Year-end RWJ Excel Tip To insert a column chart, select the data you want to graph, then go to the Insert tab, and select Column. Notice, w numbers. To do this, we right clicked on one of the columns, selected Format Data Series, went to the Fill option, a We have a question for you: In how many years over the 1926-2008 period were the annual large-company stock r by hand, Excel has a function that quickly counts these values for you. How many times did large-company stocks have a return greater than or equal to 12 percent for the period 1926 to Number of years with a return greater than or equal to 12 percent: RWJ Excel Tip To count the number of times a value occurs that is greater than or less than a specified value, use the COUNTIF fu inputs for the function are relatively simple: Range is the range of the data you want to count the occurrences, and Criteria is the criteria you wish to count, in t percent. If you click on cell G68 and look at the formula bar, you will notice that Excel puts quotes around >=.12. Th text. COUNTIF can also be used to count the number of occurrences of text in a data set. We should note that ther entered the 12 percent minimum return in the equation box. Generally, we would like to make this a cell reference Excel COUNTIF for another specified number. Unfortunately, because Excel treats this input as text, it will not allow allow you to reference a cell for this input, but it will not correctly perform the operation.) Of course, as with any other function, the uses of COUNTIF can easily be extended. Suppose you wanted to count t were greater than 9 percent but less than 23 percent. We could count all the returns greater than 9 percent and su we could use two COUNTIF functions like this: Large company stock returns greater than 9 percent and less than 23 percent: Filtering Data Of course, you may want to do more sorting and filtering of data. You may have noticed small arrows on the histor functions we built into the worksheet. RWJ Excel Tip When a filter is applied to a dataset, you will see a small arrow in the header row. To insert these sort/filter icons, we selected all the headers for our data columns, went to the Home tab, and selec small arrow in it. This indicates that the data is sorted by the year. If you left click on one of the arrows, it will bring particular column from the largest to smallest value, or smallest to largest value. If you look below the sorting optio Number Filter option and you will see a lot of different options. For example, you can filter by greater than a 30 pe will hide all rows in which the large-company stock return is less than 30 percent. You can also filter by multiple col returns greater than 30 percent and long-term government bond returns greater than 10 percent, Excel will only d remove the filter on the column, left click on the filter arrow and then click on Clear Filter. If the data is sorted by a particular column, the arrow will look like this: If the data is filtered by a particular column, the arrow will look like this: Percentile If you want to sort the data and find a percentile ranking, Excel will also do this for you. For example, what is the 90 To answer this question, we can use Rank and Percentile. RWJ Excel Tip To sort data and find a percentile for each point, go to the Data tab, select Data Analysis, then Rank and Percentile Once you click OK, Excel will bring up another box with the input information: We used the large-company stock returns from the Historical Returns worksheet and selected the first row with th different worksheet. If you look at the Percentila worksheet, you will find the output. So, the 90th percentile return return was exactly 35.70 percent.) us asset categories for the period 1926 to 2008. Of course, with a data series this long, charts uced a chart similar to Figure 10.5 in the textbook which graphically shows large-company eturns: 1926 - 2008 1972 1974 1976 1978 1980 1982 1984 1986 1988 1990 1992 1994 1996 1998 2000 2002 2004 2006 rt tab, and select Column. Notice, we have different markings for positive and negative ata Series, went to the Fill option, and put a check in the "Invert if negative" box. e the annual large-company stock returns greater than 12 percent? While you can count these to 12 percent for the period 1926 to 2008? 44 specified value, use the COUNTIF function located under More Functions, Statistical. The is the criteria you wish to count, in this case, returns that are greater than or equal to 12 t Excel puts quotes around >=.12. The reason is that Excel treats the mathematical operator as data set. We should note that there appears to be a bug with COUNTIF. Notice that we uld like to make this a cell reference so that we could change the number in the cell and have ats this input as text, it will not allow you to reference a cell for this input. (Actually, Excel will operation.) ded. Suppose you wanted to count the number of annual returns for large-company stocks that turns greater than 9 percent and subtract all the returns greater than 23 percent. To do this, 22 e noticed small arrows on the historical return header rows. These are sorting and filtering ns, went to the Home tab, and selected filter. You may notice that the arrow for the year has a ck on one of the arrows, it will bring up a box that allows you to sort the entire dataset by any e. If you look below the sorting options, you are also given filtering options. Go down to the ou can filter by greater than a 30 percent large-company stock return. When you do so, Excel nt. You can also filter by multiple columns. For example, if you filter by large-company stock er than 10 percent, Excel will only display the years 1985, 1989, 1991, 1995, and 1997. To Clear Filter. for you. For example, what is the 90th percentile returns for large-company stocks since 1926? a Analysis, then Rank and Percentile: et and selected the first row with the header. We also selected to have the output in a utput. So, the 90th percentile return was about 35.70 percent. (Notice, the 90.2 percentile Historical Returns Long-Term Large Company Government U.S. Treasury Consumer Price Stocks Bonds Bills Index 1926 13.75% 5.69% 3.30% -1.12% 1927 35.70% 6.58% 3.15% -2.26% 1928 45.08% 1.15% 4.05% -1.16% 1929 -8.80% 4.39% 4.47% 0.58% 1930 -25.13% 4.47% 2.27% -6.40% 1931 -43.60% -2.15% 1.15% -9.32% 1932 -8.75% 8.51% 0.88% -10.27% 1933 52.95% 1.92% 0.52% 0.76% 1934 -2.31% 7.59% 0.27% 1.52% 1935 46.79% 4.20% 0.17% 2.99% 1936 32.49% 5.13% 0.17% 1.45% 1937 -35.45% 1.44% 0.27% 2.86% 1938 31.63% 4.21% 0.06% -2.78% 1939 -1.43% 3.84% 0.04% 0.00% 1940 -10.36% 5.70% 0.04% 0.71% 1941 -12.02% 0.47% 0.14% 9.93% 1942 20.75% 1.80% 0.34% 9.03% 1943 25.38% 2.01% 0.38% 2.96% 1944 19.49% 2.27% 0.38% 2.30% 1945 36.21% 5.29% 0.38% 2.25% 1946 -8.42% 0.54% 0.38% 18.13% 1947 5.05% -1.02% 0.62% 8.84% 1948 4.99% 2.66% 1.06% 2.99% 1949 17.81% 4.58% 1.12% -2.07% 1950 30.05% -0.98% 1.22% 5.93% 1951 23.79% -0.20% 1.56% 6.00% 1952 18.39% 2.43% 1.75% 0.75% 1953 -1.07% 2.28% 1.87% 0.75% 1954 52.23% 3.08% 0.93% -0.74% 1955 31.62% -0.73% 1.80% 0.37% 1956 6.91% -1.72% 2.66% 2.99% 1957 -10.50% 6.82% 3.28% 2.90% 1958 43.57% -1.72% 1.71% 1.76% 1959 12.01% -2.02% 3.48% 1.73% 1960 0.47% 11.21% 2.81% 1.36% 1961 26.84% 2.20% 2.40% 0.67% 1962 -8.75% 5.72% 2.82% 1.33% 1963 22.70% 1.79% 3.23% 1.64% 1964 16.43% 3.71% 3.62% 0.97% 1965 12.38% 0.93% 4.06% 1.92% 1966 -10.06% 5.12% 4.94% 3.46% 1967 23.98% -2.86% 4.39% 3.04% 1968 11.03% 2.25% 5.49% 4.72% 1969 -8.43% -5.63% 6.90% 6.20% 1970 3.94% 18.92% 6.50% 5.57% 1971 14.30% 11.24% 4.36% 3.27% 1972 18.99% 2.39% 4.23% 3.41% 1973 -14.69% 3.30% 7.29% 8.71% 1974 -26.47% 4.00% 7.99% 12.34% 1975 37.23% 5.52% 5.87% 6.94% 1976 23.93% 15.56% 5.07% 4.86% 1977 -7.16% 0.38% 5.45% 6.70% 1978 6.57% -1.26% 7.64% 9.02% 1979 18.61% 1.26% 10.56% 13.29% 1980 32.50% -2.48% 12.10% 12.52% 1981 -4.92% 4.04% 14.60% 8.92% 1982 21.55% 44.28% 10.94% 3.83% 1983 22.56% 1.29% 8.99% 3.79% 1984 6.27% 15.29% 9.90% 3.95% 1985 31.73% 32.27% 7.71% 3.80% 1986 18.67% 22.39% 6.09% 1.10% 1987 5.25% -3.03% 5.88% 4.43% 1988 16.61% 6.84% 6.94% 4.42% 1989 31.69% 18.54% 8.44% 4.65% 1990 -3.10% 7.74% 7.69% 6.11% 1991 30.46% 19.36% 5.43% 3.06% 1992 7.62% 7.34% 3.48% 2.90% 1993 10.08% 13.06% 3.03% 2.75% 1994 1.32% -7.32% 4.39% 2.67% 1995 37.58% 25.94% 5.61% 2.54% 1996 22.96% 0.13% 5.14% 3.32% 1997 33.36% 12.02% 5.19% 1.70% 1998 28.58% 14.45% 4.86% 1.61% 1999 21.04% -7.51% 4.80% 2.68% 2000 -9.10% 17.22% 5.98% 3.39% 2001 -11.89% 5.51% 3.33% 1.55% 2002 -22.10% 15.15% 1.61% 2.40% 2003 28.89% 2.01% 0.94% 1.90% 2004 10.88% 8.12% 1.14% 3.30% 2005 4.91% 6.89% 2.79% 3.40% 2006 15.79% 0.28% 4.97% 2.54% 2007 5.49% 10.85% 4.52% 4.08% 2008 -37.00% 14.24% 1.24% 0.90% Large Company Stocks Point Rank Percent 8 52.95% 1 100.00% 29 52.23% 2 98.70% 10 46.79% 3 97.50% 3 45.08% 4 96.30% 33 43.57% 5 95.10% 70 37.58% 6 93.90% 50 37.23% 7 92.60% 20 36.21% 8 91.40% 2 35.70% 9 90.20% 72 33.36% 10 89.00% 55 32.50% 11 87.80% 11 32.49% 12 86.50% 60 31.73% 13 85.30% 64 31.69% 14 84.10% 13 31.63% 15 82.90% 30 31.62% 16 81.70% 66 30.46% 17 80.40% 25 30.05% 18 79.20% 78 28.89% 19 78.00% 73 28.58% 20 76.80% 36 26.84% 21 75.60% 18 25.38% 22 74.30% 42 23.98% 23 73.10% 51 23.93% 24 71.90% 26 23.79% 25 70.70% 71 22.96% 26 69.50% 38 22.70% 27 68.20% 58 22.56% 28 67.00% 57 21.55% 29 65.80% 74 21.04% 30 64.60% 17 20.75% 31 63.40% 19 19.49% 32 62.10% 47 18.99% 33 60.90% 61 18.67% 34 59.70% 54 18.61% 35 58.50% 27 18.39% 36 57.30% 24 17.81% 37 56.00% 63 16.61% 38 54.80% 39 16.43% 39 53.60% 81 15.79% 40 52.40% 46 14.30% 41 51.20% 1 13.75% 42 50.00% 40 12.38% 43 48.70% 34 12.01% 44 47.50% 43 11.03% 45 46.30% 79 10.88% 46 45.10% 68 10.08% 47 43.90% 67 7.62% 48 42.60% 31 6.91% 49 41.40% 53 6.57% 50 40.20% 59 6.27% 51 39.00% 82 5.49% 52 37.80% 62 5.25% 53 36.50% 22 5.05% 54 35.30% 23 4.99% 55 34.10% 80 4.91% 56 32.90% 45 3.94% 57 31.70% 69 1.32% 58 30.40% 35 0.47% 59 29.20% 28 -1.07% 60 28.00% 14 -1.43% 61 26.80% 9 -2.31% 62 25.60% 65 -3.10% 63 24.30% 56 -4.92% 64 23.10% 52 -7.16% 65 21.90% 21 -8.42% 66 20.70% 44 -8.43% 67 19.50% 7 -8.75% 68 17.00% 37 -8.75% 68 17.00% 4 -8.80% 70 15.80% 75 -9.10% 71 14.60% 41 -10.06% 72 13.40% 15 -10.36% 73 12.10% 32 -10.50% 74 10.90% 76 -11.89% 75 9.70% 16 -12.02% 76 8.50% 48 -14.69% 77 7.30% 77 -22.10% 78 6.00% 5 -25.13% 79 4.80% 49 -26.47% 80 3.60% 12 -35.45% 81 2.40% 83 -37.00% 82 1.20% 6 -43.60% 83 0.00% Chapter 10 - Section 3 Average Returns: The First Lesson Calculating the average return for a large sample is a time consuming task. Fortunately, Excel has the function AVER numbers. In the Historical Returns worksheet, we have shown the historical returns for differnet asset classes. To c return series, we can use the AVERAGE function, which gives us: Average return Large company stocks: 11.55% Long-term government bonds: 5.77% U.S. Treasury bills: 3.85% Inflation: 3.11% Notice the average returns are slightly different from those reported in Table 10.2 because they are from two diffe RWJ Excel Tip The AVERAGE function is a Statistical function under More Functions on the Formula tab. The AVERAGE function is that we want to calculate the average for in the box. Below, you will see our inputs for calculating the large-compa array by selecting all the adjacent cells with the mouse. The array is reported with a colon (:) between the first cell at a time by entering the cell in Number1, hitting tab, and then entering the next cell in Number2, and so on. As you can see, Excel will only allow 255 numeric arguments, but will allow many more numbers when you enter th Suppose you want to sort the returns by the highest large-company stock return. Excel has a sort function that allo case sensitive, number, date or time, cell color, font, and/or icon. We want to sort the returns by largest to smalles returns, then Treasury bills returns, inflation, and finally, long-term government bond returns. RWJ Excel Tip To sort columns (or rows), first select the entire array of data you want to sort. In this case, we selected all five colu headers in our selection. Next, on the Home tab, click on Sort & Filter, then Custom Sort. This brings up a box that w Notice at the top right of the box, the box with "My data has headers" has been checked. This tells Excel to ignore t in the first column, sorted on values in the second column, then chose largest to smallest in the third column. To ad upper left of the box and repeated the procedure for the other data arrays. Below, you will find a snapshot of what Notice that 1933 had the largest large-company stock return over this period. In this example, the sorts on the oth as we have done here uses the first sort as the first priority. In this case, Excel will sort the large-company stock ret stock returns are the same, it will then sort by U.S. Treasury bill returns from largest to smallest. To get the data ba smallest to largest. unately, Excel has the function AVERAGE that calculates the arithmetic average of a series of urns for differnet asset classes. To calculate the arithmetic average return for each of these 0.2 because they are from two different sources. mula tab. The AVERAGE function is relatively simple to use. We only need to input the cells puts for calculating the large-company stock average return. Notice, we entered the data as an with a colon (:) between the first cell and the last cell. Of course, we could have entered one cell xt cell in Number2, and so on. ny more numbers when you enter the values as an array. n. Excel has a sort function that allows you to sort based on text (A to Z), whether the text is ort the returns by largest to smallest return. First, we want to sort by large-company stock t bond returns. In this case, we selected all five columns including the year. We also included the column tom Sort. This brings up a box that will look something like the box below: n checked. This tells Excel to ignore the first row when it sorts. We chose Large Company stocks o smallest in the third column. To add another level of sorting, we clicked on "Add Level" in the ow, you will find a snapshot of what we got. n this example, the sorts on the other data series are almost irrelevant. A multi-level sort such will sort the large-company stock returns from largest to smallest. If any of the large-company rgest to smallest. To get the data back to chronological order, sort the data by the year from Chapter 10 - Section 4 The Variability of Returns: The Second Lesson To examine the variability of the historical returns, again we may want to start with a graphical analysis. In the text large-company stocks, which we will replicate here. To do this, we must first create bins. A bin is just the limits of th which will count the number of annual returns less than -60%. The next bin is -55%. This will count the number of r -55%, but greater than -60%. To create this frequency distribution, we will use the FREQUENCY function. Bin Frequency -60% 0 -55% 0 -50% 0 -45% 0 -40% 1 -35% 2 -30% 0 -25% 2 -20% 1 -15% 0 -10% 6 -5% 7 0% 5 5% 5 10% 7 15% 7 20% 9 25% 9 30% 4 35% 9 40% 4 45% 1 50% 2 55% 2 60% 0 0 RWJ Excel Tip The FREQUENCY function is a Statistical function found under More Functions. Because the FREQUENCY function is step-by-step. 1) Set up the bins as we described above. The bins should be set up so that the smallest and largest bins have 2) Select the column (or row) next to the bins. The FREQUENCY function will return one more value than the n cell than the number of bins. In this case, we selected cell D33. This will return any results larger than your las 3) Go to the Formula tab and insert the Frequency function, found under More Functions, Statistical. 4) The Data_array is the data you want to analyze with the frequency distribution, while the bins array is the 5) DO NOT click OK when you have entered both the data array and bins array information! Before you click O OK. This will populate the entire array of frequency distributions that you have created. Below, you can see the function arguments we used to create this frequency distribution. Notice that beside the frequency distribution, we created another frequency distribution with ranges. We created While we could graph a frequency distribution using the bins, the legend will not be as descriptive. We will use the will see below. 10 Frequency Distribution of Large Company Stocks 9 8 Number of Observations 7 6 5 4 3 2 1 0 10% to -5% -55% -50% -45% -40% -35% -30% -25% -20% -15% -10% 5% to 0% 0% to 5% -10% to -5% -60% to -55% -55% to -50% -50% to -45% -45% to -40% -40% to -35% -35% to -30% -30% to -25% -25% to -20% -20% to -15% -15% to -10% 0% to 5% -5% to 0% Range of Annual Returns RWJ Excel Tip To create this frequency distribution, we selected the data we wanted to graph (H9:H32) and went to the Insert tab the data for the horizontal axis and input the legends as normal. Generally, when Excel draws a frequency distribut between the columns. You can change this width by right clicking on a column and selecting Format Data Series. In that will allow you to change the gap between the columns. One thing to notice is that the frequency distribution here looks less normal than Figure 10.9 in the textbook. Whe easy to make the graph look like you want. In other words, it is very easy to get mislead by a graph. There is another way to graph a histogram in Excel. To graph the histogram with this method, we need to set up th FREQUENCY function. RWJ Excel Tip For another way to graph a histogram, go to the Data tab, select Data Analysis, then Histogram: Once you click OK, Excel will bring up another box for the input information: We used the large-company stock returns from the Historical Returns worksheet and the bins we previously create and selected the Chart Output option. If you look at the Histogram worksheet, you will find the output, which inclu is "raw". We could always change the look of the graph if we wanted. Variance and Standard Deviation The variance and standard deviation of an asset are measures of the risk of the asset. Fortunately, Excel has built-in deviation. Variance of large-company stock returns: 0.042231 Standard deviation of large-company stock returns: 20.55% RWJ Excel Tip The variance function (VAR) and standard deviation function (STDEV) are both located in the Statistical category of and select the cells or array you want Excel to calculate the variance or standard deviation for. Below, you will see standard deviation for large-company stock returns. If you remember back to "sadistics", there are actually 2 different variances, and therefore standard deviations: the deviation. The difference in the calculation is that the sample standard deviation divides by N - 1, while the popula the population standard deviation is applicable when you have the entire population of observations, not just a sam sample of stock returns since there were stock returns before 1926 and there will be more in the future. Should yo population variance (VARP) and population standard deviation (STDEVP). Using these functions on large-company s Population variance of large-company stock returns: 0.041722 Population standard deviation of large-company stock returns: 20.43% RWJ Excel Tip The population variance function (VARP) and population standard deviation function (STDEVP) are both located in functions, insert the function and select the cells or array you want Excel to calculate the population variance or po entered the returns to calculate the population variance and population standard deviation for large-company stoc Notice that the sample variance and population variance are similar, as are the sample standard deviation and pop you have enough numbers to calculate a standard deviation or variance in practice, whether you divide by N or N-1 continue to use the sample standard deviation and sample variance throughout the text because they are technica Normal Distribution We are almost certain that one thing everyone remembers from statistics class was looking up standard normal pro calculate standard normal probabilities much more quickly and accurately. Looking back on the small-company stock returns in Table 10.1, what is the probability that you will lose more than Specified value: -16.00% Average return: 16.40% Standard deviation: 33.00% Probability less than value: 16.31% RWJ Excel Tip To find the standard normal probability, we use the NORMDIST function. Note, this is not the same as the NORMSD function, go to More Functions, Statistical. The NORMDIST function box looks like this: The inputs for the NORMDIST function are X (the value you want to test), the Mean (average), and Standard_dev (s cumulative probability function and False for the probability mass function. Notice that NORMDIST gives the proba look at the normal distribution, this is the probability to the left of the specified value. Since the total probability is than the specified value, we need to take 1 minus the value given by the NORMDIST function. You can look below f Suppose we are considering an asset with the following distribution. What is the probability that the return of the Specified value: 17.00% Average return: 13.00% Standard deviation: 35.20% Probability greater than value: 45.48% Another question that can arise when dealing with returns is this: What is the minimum loss an investor can expect company stock information from Figure 10.2 to answer this question. Specified percentage: 20.00% Average return: 11.70% Standard deviation: 20.60% Minimum expected loss: -5.64% RWJ Excel Tip To answer this question, we use the NORMINV function. The NORMINV function box looks like this: The inputs for the NORMINV function are Probability (the probablity you specify), the Mean (average), and Standar the return is less than -5.64 percent is 20 percent, or about once every 5 years. Summary Statistics Suppose you want all of the summary statistics for a data series in one step. Excel has an analysis tool that will do t large-company stock returns for 1926-2008. Large Company Stocks Mean 0.115460241 Standard Error 0.022556794 Median 0.1375 Mode -0.0875 Standard Deviation 0.205502175 Sample Variance 0.042231144 Kurtosis -0.089793742 Skewness -0.369882027 Range 0.9655 Minimum -0.436 Maximum 0.5295 Sum 9.5832 Count 83 RWJ Excel Tip To calculate all of the descriptive statistics for a data series, go to the Data tab, select Data Analysis, and Descriptiv When you click OK, another box comes up with the options that are available. Below are the options we made: We selected the large-company stock returns, including the header and checked the options for the label in the firs would report the statistics on this worksheet, and finally checked Summary statistics. As you can see, if you are inte some data, this option will allow you to get all of the statistics in one step. with a graphical analysis. In the textbook, Figure 10.9 illustrates a frequency distribution for ate bins. A bin is just the limits of the range. For example, in this case, the bin starts at -60%, 5%. This will count the number of returns less than he FREQUENCY function. Ranges Frequency -60% to -55% 0 -55% to -50% 0 -50% to -45% 0 -45% to -40% 1 -40% to -35% 2 -35% to -30% 0 -30% to -25% 2 -25% to -20% 1 -20% to -15% 0 -15% to -10% 6 -10% to -5% 7 -5% to 0% 5 0% to 5% 5 5% to 10% 7 10% to 15% 7 15% to 20% 9 20% to 25% 9 25% to 30% 4 30% to 35% 9 35% to 40% 4 40% to 45% 1 45% to 50% 2 50% to 55% 2 55% to 60% 0 ecause the FREQUENCY function is somewhat complicated, we will walk through the process the smallest and largest bins have no observations. ll return one more value than the number of bins you have created, so select one more urn any results larger than your last bin value. More Functions, Statistical. bution, while the bins array is the array that shows the bins you have already created. ray information! Before you click OK, hold down both the CTRL and SHIFT keys, then click on have created. tribution. tribution with ranges. We created the ranges by concatenating the bins we created earlier. t be as descriptive. We will use the ranges for graphing the frequency distribution, which you Large Company Stocks: 1926-2008 50% to 55% 10% to 15% 15% to 20% 20% to 25% 25% to 30% 30% to 35% 35% to 40% 40% to 45% 45% to 50% 55% to 60% 0% to 5% 5% to 10% 50% to 55% 0% to 5% 10% to 15% 15% to 20% 20% to 25% 25% to 30% 30% to 35% 35% to 40% 40% to 45% 45% to 50% 55% to 60% 5% to 10% ange of Annual Returns (H9:H32) and went to the Insert tab, Column chart, 2-D, Clustered Column. We then selected n Excel draws a frequency distribution as we have done here, there is a large amount of space nd selecting Format Data Series. In the box this brings up, there is a Series Option selection n Figure 10.9 in the textbook. When looking at any graph, always be aware that it is relatively mislead by a graph. this method, we need to set up the bins as we have done, but we do not need to use the hen Histogram: t and the bins we previously created. We selected to have the output in a different worksheet, ou will find the output, which includes the frequency distribution. Notice that the graph output asset. Fortunately, Excel has built-in functions that calculate both the variance and standard cated in the Statistical category of More Functions. To use both functions, insert the function deviation for. Below, you will see how we entered the returns to calculate the variance and therefore standard deviations: the sample standard deviation and the population standard n divides by N - 1, while the population standard deviation divides by N. As its name implies, ation of observations, not just a sample. In the case of stock returns, the returns are actually a ll be more in the future. Should you ever need them, Excel has built-in functions for the hese functions on large-company stock returns, we find the following: ction (STDEVP) are both located in the Statistical category of More Functions. To use both ulate the population variance or population standard deviation for. Below, you will see how we d deviation for large-company stock returns. ample standard deviation and population standard deviation. This should often be the case. If ce, whether you divide by N or N-1 should make little difference. Having said this, we will the text because they are technically the correct calculations. was looking up standard normal probabilities on tables. Excel has built-in functions that ability that you will lose more than a specified percentage of your money in a single year? his is not the same as the NORMSDIST (notice the "S" in the middle). To find the NORMDIST e this: ean (average), and Standard_dev (standard deviation). The Cumulative value uses True for the ce that NORMDIST gives the probability less than the specified value. In other words, if you value. Since the total probability is 1 (100%), if we want the probability that a return is greater DIST function. You can look below for an example. probability that the return of the asset is greater than a specified value? inimum loss an investor can expect a specified percentage of the time? We can use the large- box looks like this: ), the Mean (average), and Standard_dev (standard deviation). In this case, the probablity that el has an analysis tool that will do this for you. Below, you can see the descriptive statistics for elect Data Analysis, and Descriptive Statistics: elow are the options we made: the options for the label in the first row. We next selected the output range so that Excel stics. As you can see, if you are interested in all or most of the basic descriptive statistics about Bin Frequency -60% 0 -55% 0 -50% 0 -45% 0 -40% 1 -35% 2 -30% 0 -25% 2 -20% 1 -15% 0 -10% 6 Histogram -5% 7 10 0% 5 9 5% 5 8 10% 7 7 15% 7 Frequency 6 20% 9 5 25% 9 30% 4 4 35% 9 3 40% 4 2 45% 1 1 50% 2 0 55% 2 -60% -55% -50% -45% -40% -35% -30% -25% -20% -15% -10% -5% 60% 0 More 0 -5% 0% Bin 5% Histogram 10% 15% 20% 25% 30% 35% 40% 45% 50% 55% 60% More Chapter 10 - Section 5 More about Average Returns We used the AVERAGE function to calculate the arithmetic average of a series of returns. Excel also has a function function is slightly more difficult to use for returns since it will not work if any value in the series is less than or equ first, find the geometric return, and then subtract 1 from this answer. At the bottom of this worksheet, we have ad use Excel's geometric mean function: Large company stocks: 1.0950 Long-term government bonds: 1.0547 U.S. Treasury bills: 1.0380 Inflation: 1.0302 RWJ Excel Tip The GEOMEAN function is under More Functions, Statistical on the Formula tab. The GEOMEAN function requires t geometric mean for in the box. Below, you will see our inputs for calculating the gemoetric return for 1 plus the lar an array by selecting all the adjacent cells with the mouse. The array is reported with a colon (:) between the first cell at a time by entering the cell in Number1, hitting tab, and then entering the next cell in Number2, and so on. Now we can subtract one to find the geometric return for each asset class: Geometric Return Large company stocks: 9.50% Long-term government bonds: 5.47% U.S. Treasury bills: 3.80% Inflation: 3.02% 1 (One) plus the annual return Long-Term Large Company Government U.S. Treasury Consumer Price Stocks Bonds Bills Index 1926 1.1375 1.0569 1.0330 0.9888 1927 1.3570 1.0658 1.0315 0.9774 1928 1.4508 1.0115 1.0405 0.9884 1929 0.9120 1.0439 1.0447 1.0058 1930 0.7487 1.0447 1.0227 0.9360 1931 0.5640 0.9785 1.0115 0.9068 1932 0.9125 1.0851 1.0088 0.8973 1933 1.5295 1.0192 1.0052 1.0076 1934 0.9769 1.0759 1.0027 1.0152 1935 1.4679 1.0420 1.0017 1.0299 1936 1.3249 1.0513 1.0017 1.0145 1937 0.6455 1.0144 1.0027 1.0286 1938 1.3163 1.0421 1.0006 0.9722 1939 0.9857 1.0384 1.0004 1.0000 1940 0.8964 1.0570 1.0004 1.0071 1941 0.8798 1.0047 1.0014 1.0993 1942 1.2075 1.0180 1.0034 1.0903 1943 1.2538 1.0201 1.0038 1.0296 1944 1.1949 1.0227 1.0038 1.0230 1945 1.3621 1.0529 1.0038 1.0225 1946 0.9158 1.0054 1.0038 1.1813 1947 1.0505 0.9898 1.0062 1.0884 1948 1.0499 1.0266 1.0106 1.0299 1949 1.1781 1.0458 1.0112 0.9793 1950 1.3005 0.9902 1.0122 1.0593 1951 1.2379 0.9980 1.0156 1.0600 1952 1.1839 1.0243 1.0175 1.0075 1953 0.9893 1.0228 1.0187 1.0075 1954 1.5223 1.0308 1.0093 0.9926 1955 1.3162 0.9927 1.0180 1.0037 1956 1.0691 0.9828 1.0266 1.0299 1957 0.8950 1.0682 1.0328 1.0290 1958 1.4357 0.9828 1.0171 1.0176 1959 1.1201 0.9798 1.0348 1.0173 1960 1.0047 1.1121 1.0281 1.0136 1961 1.2684 1.0220 1.0240 1.0067 1962 0.9125 1.0572 1.0282 1.0133 1963 1.2270 1.0179 1.0323 1.0164 1964 1.1643 1.0371 1.0362 1.0097 1965 1.1238 1.0093 1.0406 1.0192 1966 0.8994 1.0512 1.0494 1.0346 1967 1.2398 0.9714 1.0439 1.0304 1968 1.1103 1.0225 1.0549 1.0472 1969 0.9157 0.9437 1.0690 1.0620 1970 1.0394 1.1892 1.0650 1.0557 1971 1.1430 1.1124 1.0436 1.0327 1972 1.1899 1.0239 1.0423 1.0341 1973 0.8531 1.0330 1.0729 1.0871 1974 0.7353 1.0400 1.0799 1.1234 1975 1.3723 1.0552 1.0587 1.0694 1976 1.2393 1.1556 1.0507 1.0486 1977 0.9284 1.0038 1.0545 1.0670 1978 1.0657 0.9874 1.0764 1.0902 1979 1.1861 1.0126 1.1056 1.1329 1980 1.3250 0.9752 1.1210 1.1252 1981 0.9508 1.0404 1.1460 1.0892 1982 1.2155 1.4428 1.1094 1.0383 1983 1.2256 1.0129 1.0899 1.0379 1984 1.0627 1.1529 1.0990 1.0395 1985 1.3173 1.3227 1.0771 1.0380 1986 1.1867 1.2239 1.0609 1.0110 1987 1.0525 0.9697 1.0588 1.0443 1988 1.1661 1.0684 1.0694 1.0442 1989 1.3169 1.1854 1.0844 1.0465 1990 0.9690 1.0774 1.0769 1.0611 1991 1.3046 1.1936 1.0543 1.0306 1992 1.0762 1.0734 1.0348 1.0290 1993 1.1008 1.1306 1.0303 1.0275 1994 1.0132 0.9268 1.0439 1.0267 1995 1.3758 1.2594 1.0561 1.0254 1996 1.2296 1.0013 1.0514 1.0332 1997 1.3336 1.1202 1.0519 1.0170 1998 1.2858 1.1445 1.0486 1.0161 1999 1.2104 0.9249 1.0480 1.0268 2000 0.9090 1.1722 1.0598 1.0339 2001 0.8811 1.0551 1.0333 1.0155 2002 0.7790 1.1515 1.0161 1.0240 2003 1.2889 1.0201 1.0094 1.0190 2004 1.1088 1.0812 1.0114 1.0330 2005 1.0491 1.0689 1.0279 1.0340 2006 1.1579 1.0028 1.0497 1.0254 2007 1.0549 1.1085 1.0452 1.0408 2008 0.6300 1.1424 1.0124 1.0090 of returns. Excel also has a function that calculates the geometric average, however the alue in the series is less than or equal to zero. To adjust for this, we can add 1 to each return ttom of this worksheet, we have added 1 to the annual return for each asset class. Now we . The GEOMEAN function requires the input for the cells that we want to calculate the e gemoetric return for 1 plus the large-company stock returns. Notice, we entered the data as d with a colon (:) between the first cell and the last cell. Of course, we could have entered one e next cell in Number2, and so on. Chapter 10 - Master it! As we have seen, over the 1926-2008 period, small-company stocks had the highest return and the highest ris lowest risk. While we certainly hope you have an 83 year holding period, likely your investment will be for few shorter investment period is by using rolling returns and standard deviations. Suppose you have a series of an average return. You would calculate the first rolling average at Year 3 using the returns for the first 3 years. T returns from Years 2, 3, and 4. a. Using the annual returns for large-company stocks and Treasury bills, calculate both the 5- and 10-year rolling b. Over how many 5-year periods did Treasury bills outperform large-company stocks? How many 10-year perio c. Over how many 5-year periods did Treasury bills have a larger standard deviation than large-company stocks? d. Graph the rolling 5-year and 10-year average returns for large-company stocks and Treasury bills. e. What conclusions do you draw from the above results? he highest return and the highest risk, while U.S. Treasury bills had the lowest return and the ikely your investment will be for fewer years. One way risk and return is examined over a ons. Suppose you have a series of annual returns and you want to calculate a 3-year rolling g the returns for the first 3 years. The next rolling average would be calculated using the ulate both the 5- and 10-year rolling average return and standard deviation. ny stocks? How many 10-year periods? eviation than large-company stocks? Over how many 10-year periods? ocks and Treasury bills. Master it! Solution Large-company Stocks 5-Year 10-Year 5-Year Standard 10-Year Standard a. Average Deviation Average Deviation 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 5-Year Large Company Stock and Treasury Bi 30% 25% 20% 15% 10% 5% 0% 1930 1933 1936 1939 1942 1945 1948 1951 1954 1957 1960 1963 1966 1969 1972 1975 1978 1981 -5% -10% 10-Year Large Company Stock and Treas 120% 100% 80% 60% 60% 40% 20% 0% 1944 1968 1935 1938 1941 1947 1950 1953 1956 1959 1962 1965 1971 1974 1977 1980 1983 Even though there appears to be a relationship between risk and return, for shorter periods this relationship throughout the 83 year history we have examined, Treasury bills have outperformed large-company stock nu the holding period lengthens, the relationship between risk and return strengthens. Using the 5-year rolling a company stocks 15 times, while this occurred only 6 times when examining the 10-year rolling average. Of co Treasury bills have a larger standard deviation than large-company stocks. Treasury Bills 5- Year Period 5-Year 10-Year 5-Year Standard 10-Year Standard T-Bill had a Average Deviation Average Deviation higher return Total: tock and Treasury Bill Rolling Averages 5-Year Large Company Stock Average 5-Year Treasury Bill Average 2002 1981 1984 1987 1990 1993 1996 1999 2005 2008 pany Stock and Treasury Bill Rolling Averages 10-Year Large Company Stock Average 10-Year Large Company Stock Average 10-Year Treasury Bill Average 1995 1983 1986 1989 1992 1998 2001 2004 2007 orter periods this relationship does not have to occur. Consider that rmed large-company stock numerous times for one year periods. However, as hens. Using the 5-year rolling averages, Treasury bills outperformed large- 10-year rolling average. Of course, over no rolling period we examined did 5- Year Period 10-Year Period T-Bills had a T-Bills had a higher standard T-Bill had a higher standard deviation higher return deviation

DOCUMENT INFO

Shared By:

Categories:

Tags:

Stats:

views: | 1 |

posted: | 2/8/2013 |

language: | English |

pages: | 82 |

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.