Stock Analysis
motivated by e-mail from Eric B

Over the years (as I larned new stuff) I've written a bunch of tutorials and generated a bunch of spreadsheets so (at Eric's suggestion) maybe it's time to combine several things into a single spreadsheet.

>For example?
Well, a spreadsheet where you select your stock (example: GE) and your "Market" (example: S&P 500) and you click a button or two and monthly data is downloaded and a bunch of stuff is calculated.

>For example?
For example:
  • The Mean and Volatility for your selected stock (monthly and "annualized").
  • A probability distribution for stock prices umpteen months into the future (using Ito stuff).
  • The alpha and beta for the selected stock (with a regression line chart, comparing stock to "Market").
  • The expected CAPM stock price (using your chosen "Market").
  • Stock info such as trailing and forward P/E ratios, PEG ratios and, for the company,
    EPS, Earnings before Interest/Taxes/Depreciation/Amortization (EBITDA), Debt, Cash etc.
  • A few other (stranger!) ratios like gRANK and gNUM and M/E.
  • Some kind of RISK evaluation (using Monte Carlo simulation).
  • A bunch of charts ... with a moving average or two.
  • The Black-Scholes Option Premium (given some selected strike price).
  • The ...
>Yeah, yeah, but what's the pictures?
For example:

Figure 1
>What's that RISK evaluation using Monte Carlo?
Well, you can always assume that Standard Deviation (that is, Volatility) measures "Risk", but ...

>But, Monte Carlo?
Yes. I prefer some kind of measure of risk that involves losing money or having your portfolio drop dead or ...

>But, Monte Carlo?
Okay, I assume you start with some portfolio and withdraw monthly at the rate of 5% per year, for 40 years, your withdrawals increasing with inflation at 3% per year and the spreadsheet does a thousand Monte Carlo 40-year simulations each simulation involving selecting, at random, one of the monthly returns over the past ten years (calculated from the downloaded data) then calculating the percentage that failed to survive for 40 years.

>And your RISK is ??
It's the percentage of portfolios that failed to survive. If the number is small, you're laughin', eh?

>In Figure 1 it says that RISK = 3
Yes, 3% failed to survive ... but that depends upon the stock you selected.

Click here to download.

... updated (and corrected!) July 5, 2004

The spreadsheet looks like this.