Chapter 10 - Section 1
Returns
Calculating returns in Excel is a relatively simple matter since we only need to input basic equations. Consider the information from the Video Concepts Company:
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:
Dividend income:
Total dollar return:
Capital gains return:
Dividend yield:
Total return:
Yahoo! Finance Returns
A popular website that provides daily stock prices is Yahoo! Finance. However, if you use the prices quoted on this website to calculate the return of a stock, you must be careful to use the correct information. Yahoo! Finance reports two closing stock prices, the actual closing price and the adjusted close which is adjusted for stock splits and dividends. In a stock split, the number of shares is increased and the stock price is decreased. For example, in a 2-for-1 stock split, the number of shares would be doubled since shareholders would receive 2 shares for every 1 share they currently own, and the stock price would be halved.
Suppose a stock is currently trading at $120 per share and undergoes a 2-for-1 stock split. Also assume that the stock price remains unchanged during the day. The closing price on Yahoo! Finance would report prices of $120 and $60, respectively, which looks like a 50 percent decrease in the stock price. In actuality, the shareholder return during the day was zero because although the stock price was cut in half, the number of shares they owned was doubled. In this case, the adjusted closing price would be 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 at the end of April, pays a dividend of $5, and has a price of $108 at the end of May. The stockholder return for this period was ($108 - 100 + 5) / $100 = 13 percent. In this case, the adjusted close at the end of May would be $108, while the adjusted close for the end of April would be $95.581, which is a return of ($108 - 95.581) / $95.581 = 13 percent. Below, we have calculated the return for IBM over a 12 month period using both the closing price and dividend, and the adjusted close.
Date	Close	Dividend	Adj Close	Return with closing price and dividend	Return with adjusted close
1/2/14	$ 176.68	$ 171.29
2/3/14	$ 185.17	$ 0.95	$ 180.51
3/3/14	$ 192.49	$ 187.64
4/1/14	$ 196.47	$ 191.52
5/1/14	$ 184.36	$ 1.10	$ 180.76
6/2/14	$ 181.27	$ 177.73
7/1/14	$ 191.67	$ 187.93
8/1/14	$ 192.30	$ 1.10	$ 189.66
9/2/14	$ 189.83	$ 187.23
10/1/14	$ 164.40	$ 162.15
11/3/14	$ 162.17	$ 1.10	$ 161.04
12/1/14	$ 160.44	$ 159.32
1/2/15	$ 153.31	$ 152.24
2/2/15	$ 163.89	$ 1.10	$ 163.89
Notice, the return calculations are very similar. The reason they are not exact is that Yahoo! Finance reports the adjusted close to the nearest cent. This rounding can cause a slight difference in the return calculation. Consider the example we used above. Using the adjusted price of $95.581, calculate for yourself that the return is actually 12.99 percent, not 13 percent. If you need total returns, the adjusted close will give you a fairly accurate return calculation that is much easier, especially since Yahoo! Finance won't download prices and dividends in the same spreadsheet. However, if you need capital gains returns and dividend yields separately, or very accurate returns, you will need to use the closing price and the dividends, not the adjusted close.

Section 10.2

Chapter 10 - Section 2

Holding Period Returns

In the text and on the next tab, we show the historical returns by year for various asset categories for the period 1926 to 2014. Of course, with a data series this long, charts will often allow you to better visualize the data over time. Below, we have produced a chart similar to Figure 10.5 in the textbook which graphically shows large company stock returns.

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, we have different markings for positive and negative numbers. To do this, we right clicked on one of the columns, selected Format Data Series, went to the Fill option, and put a check in the "Invert if negative" box.

We have a question for you: In how many years over the 1926-2014 period were the annual large company stock returns greater than 12 percent? While you can count these 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 2014?

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 function located under More Functions, Statistical. The 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 this case, returns that are greater than or equal to 12 percent. If you click on cell G68 and look at the formula bar, you will notice that Excel puts quotes around >=.12. The reason is that Excel treats the mathematical operator as text. COUNTIF can also be used to count the number of occurrences of text in a data set. We should note that there appears to be a bug with COUNTIF. Notice that we entered the 12 percent minimum return in the equation box. Generally, we would like to make this a cell reference so that we could change the number in the cell and have Excel COUNTIF for another specified number. Unfortunately, because Excel treats this input as text, it will not allow you to reference a cell for this input. (Actually, Excel will 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 the number of annual returns for large company stocks that were greater than 9 percent but less than 23 percent. We could count all the returns greater than 9 percent and subtract all the returns greater than 23 percent. To do this, 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 historical return header rows. These are sorting and filtering 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 selected filter. You may notice that the arrow for the year has a 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 up a box that allows you to sort the entire dataset by any particular column from the largest to smallest value, or smallest to largest value. If you look below the sorting options, you are also given filtering options. Go down to the Number Filter option and you will see a lot of different options. For example, you can filter by greater than a 30 percent large company stock return. When you do so, Excel will hide all rows in which the large company stock return is less than 30 percent. You can also filter by multiple columns. For example, if you filter by large company stock returns greater than 30 percent and long-term government bond returns greater than 10 percent, Excel will only display the years 1985, 1989, 1991, 1995, and 1997. To 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 90th percentile returns for large company stocks since 1926? 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 the header. We also selected to have the output in a different worksheet. If you look at the Percentile worksheet, you will find the output. So, the 90th percentile return was about 33.36 percent. (Notice, the 89.7 percentile return was exactly 33.36 percent.)

Large Company Stock Returns: 1926 - 2014

1926 1927 1928 1929 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 2009 2010 2011 2012 2013 2014 0.13750000000000001 0.35700000000000004 0.45079999999999998 -8.8000000000000009E-2 -0.25129999999999997 -0.436 -8.7499999999999994E-2 0.52950000000000008 -2.3099999999999999E-2 0.46789999999999998 0.32490000000000002 -0.35450000000000004 0.31629999999999997 -1.43E-2 -0.1036 -0.1202 0.20749999999999999 0.25379999999999997 0.19489999999999999 0.36210000000000003 -8.4199999999999997E-2 5.0499999999999996E-2 4.99E-2 0.17809999999999998 0.30049999999999999 0.2379 0.18390000000000001 -1.0700000000000001E-2 0.52229999999999999 0.31620000000000004 6.9099999999999995E-2 -0.105 0.43569999999999998 0.1201 4.6999999999999993E-3 0.26839999999999997 -8.7499999999999994E-2 0.22699999999999998 0.1643 0.12380000000000001 -0.10060000000000001 0.23980000000000001 0.1103 -8.43E-2 3.9399999999999998E-2 0.14300000000000002 0.18989999999999999 -0.1469 -0.26469999999999999 0.37229999999999996 0.23929999999999998 -7.1599999999999997E-2 6.5700000000000008E-2 0.18609999999999999 0.32500000000000001 -4.9200000000000001E-2 0.2155 0.22559999999999999 6.2699999999999992E-2 0.31730000000000003 0.1867 5.2499999999999998E-2 0.1661 0.31690000000000002 -3.1E-2 0.30459999999999998 7.6200000000000004E-2 0.1008 1.32E-2 0.37579999999999997 0.2296 0.33360000000000001 0.2858 0.2104 -9.0999999999999998E-2 -0.11890000000000001 -0.221 0.28889999999999999 0.10880000000000001 4.9100000000000005E-2 0.15789999999999998 5.4899999999999997E-2 -0.37 0.2646 0.15060000000000001 2.1100000000000001E-2 0.16 0.32300000000000001 0.13700000000000001

Year-end

Total Annual Return

Large Company Stock Returns: 1926 - 2014

Year-end

Total Annual Return

Large Company Stock Returns: 1926 - 2014

Year-end

Total Annual Return

Large Company Stock Returns: 1926 - 2014

Year-end

Total Annual Return

Large Company Stock Returns: 1926 - 2014

Year-end

Total Annual Return

Large Company Stock Returns: 1926 - 2014

Year-end

Total Annual Return

Large Company Stock Returns: 1926 - 2014

Year-end

Total Annual Return

Large Company Stock Returns: 1926 - 2014

Year-end

Total Annual Return

Historical Returns

Historical Returns
Large Company Stocks	Long-Term Government Bonds	U.S. Treasury Bills	Consumer Price 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%
2009	26.46%	-25.61%	0.15%	2.72%
2010	15.06%	7.73%	0.14%	1.50%
2011	2.11%	35.75%	0.06%	2.96%
2012	16.00%	1.80%	0.80%	1.74%
2013	32.30%	-14.69%	0.50%	1.50%
2014	13.70%	12.90%	0.20%	0.80%

Percentile

Section 10.3

Chapter 10 - Section 3

Return Statistics

Calculating the average return for a large sample is a time consuming task. Fortunately, Excel has the function AVERAGE that calculates the arithmetic average of a series of numbers. In the Historical Returns worksheet, we have reproduced the historical returns from Table 10.1. To calculate the arithmetic average return for each of these return series, we can use the AVERAGE function, which gives us:

Average return

Large company stocks:

Long-term government bonds:

U.S. Treasury bills:

Inflation:

Notice the average returns are slightly different from those reported in Table 10.2 because they are from two different sources.

RWJ Excel Tip

The AVERAGE function is a Statistical function under More Functions on the Formula tab. The AVERAGE function is relatively simple to use. We only need to input the cells that we want to calculate the average for in the box. Below, you will see our inputs for calculating the large company stock average return. Notice, we entered the data as an array by selecting all the adjacent cells with the mouse. The array is reported with a colon (:) between the first cell and the last cell. Of course, we could have entered one 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 the values as an array.

Suppose you want to sort the returns by the highest large company stock return. Excel has a sort function that allows you to sort based on text (A to Z), whether the text is case sensitive, number, date or time, cell color, font, and/or icon. We want to sort the returns by largest to smallest return. First, we want to sort by large company stock 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 columns including the year. We also included the column headers in our selection. Next, on the Home tab, click on Sort & Filter, then Custom Sort. This brings up a box that will look something like the box below:

Notice at the top right of the box, the box with "My data has headers" has been checked. This tells Excel to ignore the first row when it sorts. We chose Large Company stocks in the first column, sorted on values in the second column, then chose largest to smallest in the third column. To add another level of sorting, we clicked on "Add Level" in the upper left of the box and repeated the procedure for the other data arrays. Below, you will find a snapshot of what we got.

Notice that 1933 had the largest large company stock return over this period. In this example, the sorts on the other data series are almost irrelevant. A multi-level sort such as we have done here uses the first sort as the first priority. In this case, Excel will sort the large company stock returns from largest to smallest. If any of the large company stock returns are the same, it will then sort by U.S. Treasury bill returns from largest to smallest. To get the data back to chronological order, sort the data by the year from smallest to largest.