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
w to use the following Excel functions:

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

-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

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
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