CAPM and BETA

In this and this we talked about beta and the Capital Asset Pricing Model so I thought a spreadsheet might be useful ... so here it is.

>Is it any good?
Well ... uh, I do offer a money-back guarantee.

Anyway, to download a .ZIPd spreadsheet, just click RIGHT-click here and Save Target file.

>So where's the picture of the spreadsheet?
Pictures take up so much disk space ... and I'm already past my disk limit

>Just a little picture?
Okay ... you type in a stock symbol (like MSFT) and some benchmark index (like ^SPX) and click a button or two
and you get a bunch of data downloaded from Yahoo! and a couple of charts ... and this  

P.S. The spreadsheet uses MONTHLY returns over the past three years to calculate beta.

Using this spreadsheet (today is December, 2003)
and keeping track of the results (!)
you could (if you had the patience) generate something like

or this

>Looks bad, eh?
Remember what CAPM means:

  • [1]       CAPM Return = Rf + beta (Rmkt - Rf)
    where Rf is a risk-free return (example Rf = 4%, in money market) and Rmkt is the market return (example the S&P500 return)
  • You take a risk just by being in the market (as opposed to investing just in some risk-free investment)
  • If beta = 0, then there is no such risk ... and you'd expect to get the risk-free return Rf (something like PG, above).
    (That's shown in [1], putting beta = 0)
  • If you just invest in the market (like the S&P500), then beta = 1 and you'd get the market risk
    ... so you should expect to get the market return Rmkt (again, as shown in [1], putting beta = 1).
  • However, if you invest in some stock (as opposed to investing in the market itself), then there's another risk peculiar to that stock.
  • That other risk is measured by beta for the stock:
  • If beta > 1 then it's riskier than the market and you'd expect to be rewarded for taking on that extra risk
    (and that's also shown in [1] where, if the market return is greater than the risk-free return, then CAPM Return > Rmkt if beta > 1).
  • The CAPM return is the return you should get for taking on that extra risk.

>It still looks bad!
Don't believe everything you read. We're talking future ... who knows what'll happen?
>Me!