Crystal Ball

Oracle Crystal Ball is an Excel application that enhances your models with statistical analysis and Monte Carlo simulations. It is software I have benefited from in my profession for financial analytics and process improvement. Examples include, cost forecasting and optimization, capacity planning, value stream mapping, and process performance optimization. It is also advantageous outside of work, for example, in personal finance. Presented here is an example of using Crystal Ball to carry out a covered call options trading strategy.

Option Strategy

Writing covered calls is an options trading strategy I have grown fond of particularly for low volatility / high dividend holdings. It works well for shares that are continuously accumulated such as through corporate stock purchase programs or holdings that have outgrown their proportion of the portfolio. The objective is to write (a.k.a. sell) call contracts on existing holdings with the intent of collecting the premium and having the option expire out of the money so that the shares are not called away. Doing so on an ongoing basis helps eke out more return from a low risk security. The trick is to find a strike price that pays a decent premium, but will likely not get called away. Following the news, stock price, and options premiums over time will build experience and improve intuition. Example considerations include,

  • What forthcoming information will be reported between now expiration date that might increase the stock price, such as an earnings announcement, relevant economic data, reserve bank meetings, etc…
  • Ex-dividend dates – you don’t want to risk having shares called away right before a dividend payout.

What Are the Odds?

Crystal Ball can help you gage the likelihood that the underlying’s price will exceed various strike prices (get called away) assuming recent price volatility persists. Here are two examples of how to do this using a random walk method and the Crystal Ball Predictor Wizard.

Long Equity Position
Long Equity Position

For this example, suppose we have 500 shares of Altria Corp (MO), that we intend to hold for a long time. We collected stock price data up to June 30 and we are considering contracts expiring on 25-July.

Crystal Ball Overlay Chart
Crystal Ball Overlay Chart

First, select historical prices on which to base the analysis. I prefer using a recent few months or a quarter that represent the current situation/environment. This snapshot shows the distribution of daily returns on Altria shares for the months of April, May, and June. You can see that returns for June (in green) were more volatile and favor a lower daily percent change. This shows the importance of picking a relevant nearby historical baseline.

Random Walk Method

This is a simple and intuitive method of generating a distribution of likely future stock prices. Create assumptions using the Crystal Ball distribution-fitting feature on the chosen historical data. Each day’s predicted stock price is the previous day price multiplied by a return pulled from the assumed distribution during the Monte Carlo simulation. Running 10,000 trials creates a ‘walk’ towards a predicted values on expiration day.

Crystal Ball Frequency Chart
Crystal Ball Frequency Chart

The result of this method is a frequency graph of predicted stock prices. Also included in the graph are indicators for the strike prices of $42 and $43. A quick glance shows there is a greater than 50% chance the stock will exceed $42 on 25-July thus having the shares called away. There is roughly a 40% chance, however, of losing those shares on a $43 strike price.

Predictor Wizard

Crystal Ball Predictor Wizard
Crystal Ball Predictor Wizard
Crystal Ball Predictor Results
Crystal Ball Predictor Results

Another approach to forecasting future prices is the Crystal Ball Predictor Wizard. It analyzes your data and can also identify and incorporate seasonality and events. It finds the best fit between your data and numerous forecasting methods. The Predictor Wizard screenshot shows these methods in the wizard. The best method in this case is double exponential smoothing. It produced the output show here as well as populating data in the spreadsheet. The median predicted price for 25-July is $42.52, which is nearly the same as the predicted median above – $42.51! We now know that based on recent experience and fundamental observation that the share price will probably exceed the 42 strike price and could approach $43, but not likely to go too much higher. There is less than a 10% chance it will exceed $45.

Decision

MO Call Option Chain
MO Call Option Chain

Deciding on a contract is the art of weighing premium amounts against your opinion of future prices. Perusing the premium quotes for out-of-the money calls provides general investor sentiment as well as the opportunity for additional return. This table shows premiums for various strike prices on July 25 calls as of the close on June 30.

Covered Call Decision Tree
Covered Call Decision Tree

Assuming you hold the underlying security with the intention of keeping it for a long time, then there are three possible outcomes to consider.

  1. The ideal scenario is to collect a premium while the stock price falls short of the strike price.
  2. An ‘ok’ scenario is where the stock price rises to near the strike price. While your shares might be called away, you can replace them without too much of an interruption to long-term goals.
  3. The scenario to avoid is when the share price rises sharply so that you lose your shares and hence the return you would have enjoyed if you held the shares.

The decision tree shows potential results from our hypothetical situation of holding 500 shares of Altria (MO) and writing a $43 call collecting $.35 premium.

Hindsight

Looking back on our scenario reveals the impact to this hypothetical investor. Altria stock closed July 25 at $41.74 which is below the $43 strike price. The investor retained the underlying stock and collected $175 in premium, which is a .875% return on the investment. ExampleHoldingIf the investor traded similarly four times this year, it would generate an annual return of 3.5%.  That is in addition to the annual dividend yield of 4.5%, bringing return before price appreciation to 8%. Not bad for a a stock exhibiting a beta of .44 and has averaged 16% year-over-year growth in value over the past four years.



  • Concepts:
    • Covered Call
    • Beta
    • Random Walk

  • Sotware:
    • Microsoft Excel
    • Oracle Crystal Ball

  • Data Sources
    • finance.yahoo.com
    • MerillEdge

  • Reading:
    • Financial Modeling with Crystal Ball by John Charnes

Leave a Reply