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.

Trend: Rising Food Prices

(wikipedia: picture of a farm in Hebei, China)

Over here in Australia, the two blockbuster trading bets are on a rise and continued rise of mineral ores, oil & gas and grains commodity. Australia stands to benefit quite a fair bit as an old world economy rich in mineral resources and arable land with a relatively tiny population.

In a recent media release from the Reserve Bank of Australia dated 1 April 2008, Governor Glenn Stevens, expresses the RBAs view that they expect a further rise in Australia's terms of trade. (Terms of Trade = Prices of Exports / Prices of Imports, hence Australia being a large exporter of commodities to hungry, growing nations, they expect a rise in the terms of trade.)

What's scary about this is that supply for iron or oil in the form of new iron ore mineral deposits or easily extractable oil wells are not in the pipeline, so to speak. In fact they're not even anywhere near the pipeline. Apparently, back when prices were low, we did not go exploring for these things, now that there's a demand again, exploration companies big and small are scrambling to find a lucrative node. Much of what's extractable however is in remote, farflung places. Sometimes located below nice pristine rainforests for example.

The same thing is apparently going on with food prices. We're now witnessing a market in disequilibrium as suppliers scramble to shift supply in response to a larger than anticipated demand.

Reuters.com is doing a special feature on rising food prices: "Agflation: The Real costs of Rising Food Prices"

The UN fears that if food prices continue to escalate, we're going to see more civil unrest. So where's the demand coming from? China and India mostly. But let's not forget to mention that farming isn't instantaneous, it takes time to produce that grain of rice, wheat, sorghum. It takes time to grow that chicken, sheep, cow. There's always a lag in supply response.

Also, let's not forget the market inefficiencies that government rules and taxes impose on prices. Prices are not going to smoothly adjust, they'll be sticky.

I got to thinking about the kind of trends that we can invest and trade on: I'm long emerging markets, so with the rise in incomes and wealth of the average citizen, then we can expect them to consume more meat, vegetables, milk, consumer food products etc. With the need to feed more people, basic inputs have to be increased as well. Stuff like fertilizer, feedstock, feedgrains, acreages etc. 

In the mean time, I guess we as consumers have to put up with higher prices. Nothing comes for free, that's the price of progress.