Tip #2002-03: Using Excel's Slope Function to Calculate Beta
One of the most widely used risk management tools used by market professionals is beta. Beta measures the
volatility of a security relative to something else, usually a benchmark index. For example, portfolio
managers, who trade large capitalization equities in the Unties States would most likely compare the
volatility of holdings in their portfolio to a broad-based benchmark market index such as the S&P 500.
International holdings would need to be compared to other benchmarks. Other market sectors would use other
indices. For fixed income instruments, one would most likely use the Lehman Brothers Aggregate Bond index.
Beta is easy to interpret. A beta that is greater than one is more volatile than the benchmark index. A beta
that is less than one is less volatile than the benchmark index. For example, a beta of 1 means that if the
benchmark index move up or down ten percent, the equity with a beta of 1 should also move 10 percent. If the
equity has a beta of 1.2 should move up or down twenty percent in relation to a 10 percent move by the
benchmark index.
Nearly all the real-time data vendors calculate beta for a particular instrument. However, some of the beta
values vary a good deal. For example, we retrieved the beta fro IBM from Bloomberg and got a beta of .939. We
then retrieved the beta from Bridge and got a beta of 1.22. The reason betas vary is because they are calculated
differently. If you calculate a beta using a 3 year period you are likely to get a different number from one
which is calculated for a one year or a 6 month period. Also if you calculate the volatility using a weekly
and monthly data for the same period of time you are likely to get beta values that vary quite a bit.
Since market professionals rely on a beta as to how they structure and fine tune their portfolios, they need to
know how beta is calculated and how accurate it is in determining the risk of their portfolio holdings. Also
with the renewed emphasis on accountability it makes sense to calculate your own beta rather than rely on an
outside source. If you can retrieve historical data into Excel you can calculate a beta value quite easily.
If you create your own beta calculation model, and update the historical data, you will have a more sensitive
and accurate calculation of a stock's volatility than one which you obtained from an outside source.
Compare Relationship of Equity to Index
In order to calculate beta you need historical data for the benchmark index and historical data for the same
period for the instrument for which you are trying to calculate the beta. As a market professional you are
aware of you investment objectives and how much risk you can assume and can best decide whether to use weekly,
monthly or daily pricing information in your beta calculation. Once you have retrieved the historical data you
need to normalize the data by calculating the percent change from one period to another. If you are using
weekly data you will need to calculate the percent change from one week to another for the benchmark index
and for the instrument for which you are calculating the beta.
Once the data is normalized you need to calculate the relationship of the two instruments. Excel's slope function
lets you calculate this relationship. Excel's slope function takes two arguments:
- the array of dependent variables (weekly percent change of instrument)
- the array of independent variables and returns the rate of change along the regression
line (weekly percent change of the benchmark index)
| =SLOPE(array of wkly pct change of equity, array of wkly pct chg of index) |
We have created a model which shows how the beta is calculated for IBM in relationship to the S&P 500 Index using
the slope function. In our model we calculated the beta using weekly and monthly historical data for a one year
period to show how the beta values can vary. Download a sample spreadsheet.
Our spreadsheet models contain macros and DDE Links. For them to work properly we advise you to download the
model to a local drive and open it with Microsoft Excel rather than opening it with you web browser.
Contact us if you would like to employ our services to help you enhance
this model or help you design financial models or customized applications using Bloomberg, Bridge, ILX, Factset
or Reuters data.
|
