In these spreadsheets

Document Sample
In these spreadsheets Powered By Docstoc
					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