Estimating a market model:

Step-by-step
Prepared by Pamela Peterson Drake
Florida Atlantic University

The purpose of this document is to guide you through the process of estimating a market model for the
purpose of estimating the beta of a stock. The beta of a stock is the slope coefficient in the following
equation:

r
it
= + r
mt
t = 1,2, , T

where r
it
is the return on stock i in period t and r
mt
is the return on the market in period t. Some of the
more challenging elements of this process is gathering the necessary data and then putting it in a form
that allows us to use Microsoft Excel

to estimate the regression.

In this example, I will demonstrate how to estimate the beta of Microsoft stock using sixty months of
returns.

Contents:
1. Obtaining stock prices and dividends ......................................................................................... 1
2. Calculating returns on the stock ................................................................................................ 5
3. Obtaining returns on a market index.......................................................................................... 6
4. Estimating the market model..................................................................................................... 7
5. Determining the growth in value.............................................................................................. 10


1. Obtaining stock prices and dividends
There are many sources of downloadable stock prices on the Internet. One such source is Yahoo!
Finance. There are several paths to the stock prices and I will demonstrate one of these paths for you.
Using the main page of Yahoo! Finance, type in the ticker symbol of the stock of interest into the Enter
Symbol(s) box and click on GO:



1
This will produce the current days information on the stocks trading, along with links to company and
industry specific information. In the left-most menu, click on Historical Prices.

Now we specify the information that we need, which is monthly prices. Ideally, we want to estimate the
market model using at least sixty monthly returns. This means that we need sixty-one months of prices.
Specifying the start and end date,



We then Get Prices.

We see the prices and dividends, starting with the most recent period. Because we dont want to retype
all this ourselves, we click on the link in the lower portion of this page to Download To Spreadsheet:



We then are given a choice to Open or Save:
2


Choosing Save, I then specified the name as msft. I now have a common-separated-value (.csv) file
named msft.csv. When I open this file in Microsoft Excel

, I now see the data as follows:

For purposes of calculations and graphing, it is easier to have the data sorted in chronological order
(instead of reverse chronological order), so I highlight the rows 2 through 61 and then use Excel
commands of Data -- Sort:
3


and sort by date:



I can use the same Yahoo! Finance page to get the dividends as well by selecting the Dividends Only
choice. This produces a list of cash dividends over the same period:



This produces the list of dividends as follows:
4


which I can then download to a spreadsheet or simply type into the stock price worksheet. Because
there are so few of these, I simply insert them into the work sheet by hand. You will notice that
Microsoft had a 2:1 stock split on February 18, 2003.

We need to be alert for stock dividends and stock splits because it affects the data we are using.
Fortunately, Yahoo! Finance adjusts the stock prices for stock dividends and stock splits, Please note,
however, that not all online sources do so. Further, Yahoo! Finance does not adjust cash dividends for
stock splits and dividends, so if there are any cash dividends prior to the split, we need to adjust these
appropriately; failure to do so will result in an error in the returns.
1

Be sure to save the file as a Microsoft Excel workbook because we will be adding elements to this
worksheet that may be lost if we keep it as a .csv file. J ust use the File
Save As command and then specify the file type as a workbook.
2. Calculating returns on the stock
Once weve entered the cash dividends into our worksheet, we are now ready to calculate returns.
Remember that a monthly return is calculated as:

Price at the end of the month - Price at the beginning of the month + cash dividend
thly return =
Price at the beginning of the month
Mon

In the worksheet, this translates into referring to the cells for the prices and the dividend. For the return
for September 2000, we calculate this as =(G3-G2+H3)/ G2:

1
For example, if Microsoft had paid a dividend of, say, $0.10 prior to the 2:1 split, I would have to adjust
it to $0.05 so that it is consistent with the split-adjusted share prices.
5


Copying this cells formula to the remaining cells in column I, we then have the returns on MSFT for each
month for sixty months.
3. Obtaining returns on a market index
This is actually a tough part of the calculation. It is easy to retrieve the level of an index, say the S&P
500, but it is difficult to find the corresponding dividends on the index. Because dividends can be a
significant part of the return, we dont want to leave them out. One of the few places on the Internet
where we can find the return on the S&P 500 index that includes dividends is at .
This is a downloadable spreadsheet simply Save the file when prompted:
Standard & Poors site
2



2
I have found that Internet Explorer will often freeze when I try to open an Excel file from such an
option. It seems to work well to save the file first and then open it in Excel.
6


Using the same method to sort the data as we used previously, I sort the data:


4. Estimating the market model
We now have data in two workbooks: msft.xls and MONTHLY.xls. But we need the data in one
worksheet. Do accomplish this, Ill go into my msft.xls worksheet, Insert Worksheet, and then copy
what I need from MONTHLY.xls (the 1-month total return for each month) to msft.xls, which will be the
returns from September 2000 through August 2005 (sixty months):
3


3
If you are copying a value from a worksheet that was computed in that worksheet (as opposed to a
value simply typed in), when you copy and then paste the value into the new worksheet, you need to
specify Edit -- Paste-special and check the Values option.
7


The market model is the regression of the returns on the stock against the return on the market.
Therefore, I use the Microsoft Excel Tools Data Analysis and select Regression:


I then need to specify the Y and X variables, which I do by clicking on the worksheet icon in the selection
boxes and then highlighting the cells in the worksheet:



When I select OK, I end up with a regression output as a new worksheet:
8



The slope coefficient, beta, is 1.348860788. Rounding to three decimal places, the equation is

r
it
= 0.008 + 1.349 r
mt


We can see this relationship by graphing the returns. To prepare for the charting, I created a new
worksheet by copying the market model data worksheet [Edit Move or Copy Sheet Create a
copy] and then reordered the return columns so the returns on the market (the X axis) are listed first
and then the returns on MSFT (Y-Axis). Then

Highlight the data (the cells B5:c64)
Insert Chart -- XY (Scatter)

I placed the chart on the same worksheet as the plotting data (sheet named plotting). With only
specifying the X and Y titles and removing the legend (which is meaningless in this type of chart), I have
the following:

9


which is not very easy to interpret. Fixing up the graph (by double-clicking what I want to fix the
formatting on and then specifying it to my preferences, I end up with the following graph, including the
trendline:
-30%
-20%
-10%
0%
10%
20%
30%
40%
50%
-20% -10% 0% 10% 20%
Return on the S&P 500
Return on
MSFT

5. Determining the growth in value
We have captured the relation between Microsofts stock returns and those of the S&P 500 using
regression, arriving at a security beta of 1.35. Another way of comparing the two investments is by
10
looking at wealth changes from each investment. To do this, we use the returns that we have already
calculated and then use compounding to look at the growth in value over this same period.

First, we need to create another worksheet, so we will again Edit Move or copy worksheet
Create a copy from our market model data worksheet to create the new worksheet well call
wealth. We will calculate the value of $1 invested at the beginning of September 2000 in each
investment, MSFT and the S&P 500. Starting with the basic worksheet and entering the compounded
value calculation. For the month of September, 2000, the ending wealth is $1 multiplied by the return for
the month; for the MSFT investment, the entry into cell E5 is: 1*(1+B5) and for the S&P 500 the entry
into cell F5 is 1*(1+C5). For the next month, value of $1 invested is the value in E5 multiplied by 1
plus the return in B6, and so on:



Once we have these values computed, we can graph these values using a simple line graph over time.
Using the default settings for this graphing, we end up with:



But after working with this graph a bit, we can produce:

11
$0.0
$0.2
$0.4
$0.6
$0.8
$1.0
$1.2
0
9
/
2
0
0
0
0
2
/
2
0
0
1
0
7
/
2
0
0
1
1
2
/
2
0
0
1
0
5
/
2
0
0
2
1
0
/
2
0
0
2
0
3
/
2
0
0
3
0
8
/
2
0
0
3
0
1
/
2
0
0
4
0
6
/
2
0
0
4
1
1
/
2
0
0
4
0
4
/
2
0
0
5
Month
Value of $1
invested
9/2000
Value of $1 invested in MSFT in September 2000
Value of $1 invested in the S&P 500 in September 2000

From looking at the worksheet, we conclude that if we invested $1 in Microsoft in September 2000, we
will now have $1.02 of value. If we had invested $1 in the S&P 500, we would have $0.87 at the end of
August 2005.


You can find the worksheet that resulted from this effort here.
12