calculating beta excel

8/9/2019 Calculating Beta Excel

1/17

1

Calculating and Interpreting Beta

Introduction:In 1990, William Sharpe won a Nobel Prize in Economics for his work in

developing the Capital Asset Pricing Model (CAPM). Traditionally the CAPMhas been the basis for calculating the required return to the shareholder. Thisfigure in turn has been used to calculate the economic value of the stock and theWeighted Average Cost of Capital (WACC) for capital budgeting. In recentyears, the CAPM has been attacked as an incomplete model for explainingmarket pricing behavior, but academics and practitioners cannot agree on a goodreplacement. And so the CAPM remains an important model in practicalinvestment and financial management decision making.

Calculating Beta:The most important component in calculating the required return to

shareholder (from the CAPM) is the company’s beta. The CAPM can besuccinctly stated as:

s RF s RF M RF s k k k k k PremiumRiskMarket [1]The original model was conceived of theoretically, and was expected to be

forward looking. Careful reading of Sharpe’s original work show that themarket assesses systematic risk looking at expected future covariance of thecompany’s returns with that of the overall market. It is assumed that thesecovariances are unbiased and efficient estimates of the observed relationships ex post facto.

Traditionally the CAPM relationship is estimated using simple regressionon historical outcomes, where ks is the y variable, and k M -kRF (or the market riskpremium) is the only x variable. Care must be taken that the returns pluggedinto the regression are all for the same period. Calculated stock returns shouldbe annualized if the risk-free rate is an annual rate.

The market risk premium is merely the difference between the return tothe market portfolio and the risk-free rate. Academics typically use a valueweighted portfolio to proxy for the market portfolio, and a one-month TreasuryBill rate to proxy for the risk-free rate. Practitioners can use equally weightedportfolios (but not all do) and tend to use longer term Treasury bonds for therisk-free rate. The biggest contention between the two is over the risk-free rate

proxy. Academics want a rate that is free from all risk, including interest raterisk. Practitioners want a default-risk-free instrument with a more comparablematurity to stock.

Bringing the Data into Excel:For our purposes, we will use publicly available information to estimate

our company’s beta. We do not have access to the market weighted or value


2/17

2

weighted portfolios used by academics or high-priced stock analysts. Instead wewill use the S&P Index. Since most students will not become academics, we willallow the beta to be estimated using the 20 year Treasury bond.

The first step in estimating your company’s beta is gathering the returndata. We will gather the company’s stock returns first. This data is readilyavailable online. The example below shows how to gather it from Yahoo:

1. So first, go to http://finance.yahoo.com , and type in your company’sticker symbol.

2. Along the left-hand side of the screen, click on the option entitledHistorical Prices .

3. Next we need to get price (return) data for the stock over the mostrecent few years. Traditionally either monthly or weekly data are


3/17

3

used for this purpose. We will use monthly data if enough yearsare available, so click on Monthly . The maximum time series is thedefault displayed in Yahoo, make sure you have about five years ofdata if you are going to use monthly data. So that the data are forthe same period (are synchronous) as the Treasury bond data, wemust change the Date Range to be on the first of each month. Thenclick on the Get Prices button.

4. Although the data will be updated, it will appear as though littlehas changed. Scroll now to the bottom of the Prices table. Just

below the table is button that says .Click on this to get all of the data downloaded to spreadsheet. It


4/17

4

will ask whether you want to save or open it. You can do eitherone, but you will need it in Excel to run a regression. That meansthat if you choose to open it, you will have to open Excel, then copyand paste the information over to Excel.

5. Now we need to collect the Treasury Bond rate. For thatinformation, you will need to go to the Federal Reserve Bank of St.Louis ( http://research.stlouisfed.org/fred2/series/GS20/ ). Click on theDownload Data link, and follow the links at the bottom of the nextpage, to download the monthly 20 year bond rate.

6. Next we need the market return data. This is gathered the sameway the stock price data was gathered. First go tohttp://finance.yahoo.com; then click on S&P 500 . Then follow the


5/17

5

same directions listed for the stock prices (steps 2, 3 and 4 above) todownload the index levels for the S&P 500.

Adjusting the Data:The data will have to be lined up so that they cover the same month, and

so that they are return data. We will also need to adjust the data so that they areannual returns, since we want annual required return, and we will have to adjustthe Treasury yield data, since 3.80 means 3.80% (which should say 0.0380). Forthe explanation below, it is assumed that each component downloaded (stockprice data, Treasury bond rates, and S&P levels), are each on a separate sheet inExcel.

The first step is going to the sheet containing the Treasury yield data andputting it in the same order as the Yahoo provided data. This is rather simple:

1. Move the cellpointer to the first date in the list.

2. Click on the Data menu, and choose Sort .


6/17

6

3. Leave the Sort by as Date , but change the default to Descending asin the dialog box displayed below. Then click OK .

Note, alternatively, you can just move your cellpointer as indicated in step

1 above, then click on the Sort Descending button if it is displayed.Now we need to calculate the return figures for both the stock price and

the S&P levels. These are done exactly the same way. Therefore, only the stockprice will be shown. You will, however, need to do both. The monthly return issimply the percent change in Adj. Close from one month to the next. To get theannualized return we will simply multiply by 12:


7/17

7

In my example, I added an extra sheet entitled Summary. On this sheet, Iset the first column as the date, so that I was sure that the data are synchronous.Then the variables are simply the figures which we collected. The Treasury bonddata are divided by 100 so that they are directly comparable to the return datafrom the other two. The risk premium is simply the difference between themarket return and the risk-free rate.

Normally, the Treasury data is not as up-to-date as the stock price data.But one lost observation makes little difference.

Estimating Beta:As mentioned above, estimating beta is simply a matter of running a

simple regression model. To do that in Excel, we will use the regressioncapabilities described earlier when we projected our income statement:

1. Click on the Tools menu.2. Choose Data Analysi s (if it doesn’t show up on the list, follow the

directions listed under the notes to income statement projections toget it to show up).

3. Choose Regression from the dialog box.


8/17

8

4. If you are using monthly data, use about five years worth of data,entering the stock return data as the Y variable, and Risk Premiumas the X variable . Fill in the new worksheet name, then click OK .

Graphically Estimating Beta:Excel can also estimate simple regression using graphs. For some, this

method helps them understand what the beta estimate is doing. Remember, that

the observed difference between the market return and the risk-free rate is our x-variable, and the return to the stock is our y-variable. This method begins bygraphing the x and y variables.

1. Highlight the first sixty (60) stock return figures on the summary

sheet. Then click on the chart wizard button .2. Choose XY Scatter plot.


9/17

9

3. Click Next , then click on the Series tab at the top of the dialog box.Click in the X variable range and enter the first sixty (60) riskpremium figures on the summary sheet. Be sure these are the samerows as those entered for the stock returns.


10/17

10

4. After both series are entered, then click Next . I would enter thelabels into the X and Y axes labels, then click Next .

5. Place this graph on its own sheet, then click Finish .

6. To get the graph to draw a regression function through the data,right click any of the data points, and choose Add Trendline . Onthis dialog box, click on the Options tab at the top, then click the“Display equation on chart” and “Display R-square on the chart.”Then click on OK . (This dialog box is displayed on the top of thenext page.)


11/17


12/17

12

But all of this analysis is based on simple regression. Remember that infinance, we define risk as variation. Also remember that the CAPM says that theonly risk for which the investor is compensated is systematic risk. Systematicrisk is the risk that is correlated with the return to the market; when the return tothe market goes up, systematic return should also increase. But in addition tosystematic risk, stock prices will reflect risks that are unique to the firm. Thefluctuation in returns brought about by non-market information is called non-systematic, or company specific risk. The CAPM says that total risk is simply asum of the systematic and unsystematic risk. And all risk is measured by thevariance of the return.

In the income statement notes, we discussed the measure called R-squared. We pointed out that the proportion of sum of squares that wasexplained by the regression was called R-squared. Since the largest componentof the variance is the sum of squares, many say that the R-squared is theproportion of variance that is explained by the regression model. When we

translate this approximation to the CAPM model, then the R-squared is anapproximate measure of the amount of systematic risk contained in the totalvariation. According to the CAPM the non-systematic risk can be diversifiedaway. If my regression analysis results in a R-squared of 0.1588, then about15.88% of all risk in this stock is systematic, meaning non-diversifiable. That alsomeans that 84.12% of the risk displayed in the past five years of returns appearsto be diversifiable.


13/17

13

In addition to discussing the proportion of variation explained by themodel, you should also discuss the regression’s statistical significance. If the p-value of the F-statistic or the t-statistic is not less than 0.05, then the model isprobably not estimated with sufficient confidence to use. This means that overthe estimation period, the price for the firm has been driven by much companyspecific news. That news has overwhelmed the systematic component in theestimation.

As a final check of the accuracy of your estimate, you can compare yourbeta estimate to that of online investing information like Finance Yahoo or MSN Money.

Finding the Price:All economic value is based on the present value of expected future cash

flows. There are two approaches we can use to estimate the appropriateeconomic value of the stock. The first is a dividend discount model. The second

is a cash flow discount model. The problem with taking present values ofexpected cash flows is that the dividends or cash flow streams are expected tocontinue for a very long time. In all instances, simplifying assumptions are madeso that these calculations can be made.

Dividend Discount Models:The dividend discount model approach was pioneered by Myron Gordon

in the late 1950s and early 1960s. To solve the problem of estimating a largenumber of expected dividends, he made a simplifying assumption; he assumedthat all future dividends would grow at a pre-specified constant rate. The

present value would then be:

n

n

k

g D

k

g D

k

g D

k

g DP

1

1

1

1

1

1

1

10

3

3

0

2

2

000

[3]

In this model, the only dividend in the model is the last observeddividend ( D0), which grows at a constant rate ( g), and is discounted at therequired rate of return ( k). To solve for P0, we will first multiply both sides ofequation 3 by ( 1+g)/( 1+k):

1

10

4

40

3

30

2

20

01

1

1

1

1

1

1

1

1

1

n

n

k

g D

k

g D

k

g D

k

g D

k g

P [4]

Now if you subtract equation 4 from equation 3, you are left with thefollowing equality:

1

1

000

1

1

1

1

1

11

n

n

k

g D

k

g D

k g

P [5]

At this point we will make two observations. First, note that as long ask>g, then as n gets large, the final term gets very small. In fact, the limited of thelast term as n approaches infinity, is zero. Therefore, we will ignore it from hereon out. The second observation concerns the left side of the equality:


14/17

14

k gk

k g

k k

k g

11

1

1

1

1

11 [6]

Therefore, equation 5 can be restated in the following form:

k g D

k gk

P1

1

1

00 [7]

Now if we solve for P0, we get the following equality, which is usuallycalled the Gordon Constant Growth Model:

gk

Dgk

g DP 100

ˆ1[8]

In the final form, the numerator can either be the last dividend, allowed togrow at the constant rate, or it could be restated as the expected dividend in thenext period, since the two are identical under Gordon’s simplifying assumptions.

For many applications, this form assumes too much. For many firms, theimplied growth is unlikely. Remember, that the growth rate is expected tocontinue forever. Unless the growth rate is rather low, most firms could notexpect constant growth in dividends that is much higher than inflation 1.

Smaller, younger companies may have a high growth period in thecorporate life cycle. That high growth period may be followed by a slowergrowth period. To accommodate this growth model, Gordon’s original modelhas been adjusted for two growth phases as follows (without derivation):

2

2011

1

100

1

1

1

1

11

1

gk

g D

k

g

k

g

gk

g DP

T T

[9]

Cash Flow Discount Models:

The biggest practical problem with the dividend discount method is thatmany firms do not pay dividends. Merton Miller and Franco Modigliani showedmathematically that it shouldn’t matter whether the cash flow comes to theshareholder in the form of a dividend or reinvested in the company, as long therequired return is earned. That simply means that if we replace the dividends inthe above models with cash flow (or more precisely free cash flow), we can usethe above models to value firms not offering dividends.

Practical Application of Valuation Models:Most analysts will not simply apply the above formulas to the firms they

analyze. Instead, they would project the financial statements of the firm over theshort-run, using company and market data and assumptions about theeffectiveness of the firm’s strategic and marketing plans. After those short-term

1 It should be noted at this juncture that the growth estimated here is nominal growth individends, not real growth. That means that a growth rate equal to the average expectedinflation rate would allow for a constant dividend in real terms, and should be applied later tothe cash flows as well when the same models are used to value companies using cash flows.


15/17

15

projections, the analyst would have to make some kind of simplifyingassumptions like that proposed by Gordon.

We will demonstrate how this can be done. Earlier in the semester weprojected the income for the firm. We have since then discussed differentestimates of cash flow. For this exercise, we will use what we called the quick anddirty cash flow estimate (net income + depreciation expense). This cash flowestimate was for cash flow to the shareholder and is easy to calculate 2.

To calculate the stock price per share, we will need to restate the cash flowon a per share basis. That means that an assumption must be made about thenumber of shares for any given period. At least to begin with, we can hold thesefigures constant at the most recently disclosed number of base sharesoutstanding. Taking total cash flow (net income + depreciation expense) anddividing it by the expected number of shares outstanding should yield a goodestimate of per share cash flow.

At the end of this cash flow stream, we will have to project the value of

the remaining cash flows outstanding. We will do that by applying a growthmodel to the remaining cash flow streams. This will yield a present value figureat the time of the last projected cash flow stream.

The example we used in class is for Rocky Shoe and Boot. Since they havenever paid a dividend and don’t look like they will anytime soon, we cannotdiscount their dividends. Instead, we take their projected income, and add backdepreciation expense. Then we divide by the number of shares outstanding.From the last projected year of cash flows, we need to calculate the present valueof the remaining cash flows. Since the previous cash flows have includedquarterly data, we restate the above Gordon Growth Model [equation 8] using

quarterly rates:

44

413

12gk

g DP

rdYr Avg

thQtr [10]

This “price” represents the value in time 12, of all future cash flows. Sinceit is denominated in time 12 dollars, we will need to take its present value. Theeasiest way of accomplishing that, is to add it to the periodic cash flow and takethem both together. Therefore, another line is added to sum all period cashflows (even though the only period with a “sum” is the final one. The screencapture below demonstrates the general spreadsheet layout.

2 Note that the example company, Rocky Shoe and Boot, does not list depreciation on its incomestatement. We used the change in accumulated depreciation from the balance sheet to estimatethe period by period depreciation expense.


16/17

16

In order to calculate the estimated economic value of the stock, estimatesfor all inputs must be made. Earlier in these notes we presented the method forestimating the required return to the stockholder. In addition to those estimates,we must guesstimate the long-term annual growth rate in cash flows. If

dividends are used, then the long-term annual dividend growth rate should beguesstimated.After all of these rates are estimated (or guesstimated), then we will be

ready to estimate the price of the stock, which is simply the present value of ourexpected future cash flows. The easiest way to calculate the present value is touse Excel’s NPV function. Remember that the NPV function in Excel does nottake into consideration the cash flow at time zero (see the Excel output at the topof the next page).

After a price is estimated, our work truly begins. The price may or maynot be close to the current market price. In the example given it is significantlyhigher than the current stock price. That usually means that the underlyingassumptions are not the same (or even similar) to those used by the marginalinvestors in the market. If the present value calculated is significantly different,double check your assumptions: income projections, cash flow projections,required return calculations, Gordon Growth model calculations, etc.


17/17

17

calculating beta excel

Documents