How to calculate beta using an excel spreadsheet

I've signed up with Box.net to offer a couple of spreadsheets to help finance students with their projects. Beta's a key component of the valuation project. What I am offering is plain vanilla beta coefficent. Nothing fancy. Just an estimated coefficient based on the assumption that the market in which the stock operates in has a relationship with the stock itself.

Here's a link (http://www.box.net/shared/hd9k2xqo8g) to a beta coefficient calculator in Excel.

When you download the file, you'll get an excel file that looks like the one below:



For the calculator, I used price data for a company that I am interested in Noble Group. Next, is a primer on beta.

You can estimate Beta using the Capital Asset Pricing Model.

A company’s beta coefficient simply states the relation between a stock’s excess return in relation to excess market returns.

To obtain the beta we need to estimate it from the Capital Asset Pricing Model equation:
Ri = Rf + β(Rm – Rf) (1)

This equation is actually an application of financial economics. Re-arranging equation (1):
Ri - Rf = β(Rm – Rf) (2)

Ri - Rf is the Excess Return from the Stock. It is equal to a Beta transformation of the Excess Returns earned from the Market.

The Excess Return is whatever you get from the stock or the market after accounting for the risk free rate of return which is the market’s or economy’s risk free rate of return on a Government bond, Rf.

So beta is essentially a ratio of Stock and Market Excess Returns.