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.
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.
>What's that RISK evaluation using Monte Carlo?
>Yeah, yeah, but what's the pictures?
- The Mean and Volatility for your selected stock (monthly and "annualized").
- A probability distribution for stock prices umpteen months into the future (using
- 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 ...
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.