Excel® Spreadsheet Stock Option Calculator
Kai Reinke has developed an Excel spreadsheet that incorporates an option pricing formula, similar to the Black Scholes model, that allows you to visualize your risk in the market.
Understanding Option Pricing Models
When I first started studying and trading options, I had a difficult time comprehending how my option position would behave in the market. The first time I opened a bull call spread, I sat frustrated watching the stock market climb higher while the value of my option spread sat stagnant.
The source of my frustration, and unprofitability, was a lack of understanding and feel for how options react to market changes. I owned a copy of Sheldon Natenberg's book, Option Volatility and Pricing, but my mind simply could not conceptualize what was written on the page.
Stock Option Analysis Software
Desperate for understanding, I paid for a subscription to a popular stock option analysis program. I used only about 10% of what that option analysis software was capable of doing, but I was transformed as an option trader by that software for one simple reason...
I could see what would happen to my option positions as the market changed!
They say seeing is believing, and for me that was certainly true when it came to option pricing formulas. It allowed me to form a picture in my mind, to conceptualize what was happening to my position over time and with market changes. I began to understand delta, gamma, vega and theta because I could see their effect upon a risk graph.
Can I Afford Option Analysis Software?
I no longer subscribe to that first option analysis service. It was simply too expensive for producing graphs.
After looking at several option analysis platforms, what I can tell you is that most of these analysis platforms have a few key elements in common with each other. Every option analysis program I have seen:
- Relies upon the same stock and option chain data;
- Utilizes similar option pricing formulas, each derived in some form from the Black Scholes model; and
- Provide you with a graphical representation of a stock and option position, i.e., a risk graph.
The more expensive platforms will allow you to access massive volumes of historical stock and option data, perform complex scans for the purpose of identifying potential trade candidates from among the universe of optionable stocks, and also assist in mechanically constructing option positions based upon your market assumptions. These platforms can costs thousands of dollars for the software, data, and maintenance costs.
Many traders cannot afford the cost of these services or do not want to scan through thousands of optionable stocks. For them, these sophisticated pieces of software are overkill and their lofty price tags are not cost justified.
Nonetheless, many traders would benefit from an affordable means of analyzing their option positions.
Kai Reinke's Excel Option Calculator
I can pretty much manage credit spreads and condors without the aid of a risk graph, as the risk graph for these positions are quite similar from one position to the next. However, for diagonal time spreads, and other complicated trades, it helps if I can visualize my risk in the market before opening the trade or making adjustments.
One of our members, Kai Reinke, has developed an Excel® spreadsheet that will analyze your option positions. I downloaded a copy for myself and began using it side-by-side with a more expensive piece of software while managing time spreads. What I found was that both the spreadsheet and my more expensive software generated comparable information.
Seeing the risk graph allows me to tailor my option position to my view of the market place. I begin with a hypothesis and test it in this virtual options laboratory. My initial thought on how to construct my option position is put to the test, then refined as I tailor the risk curve to suit my needs.
After using Kai's spread sheet tool and comparing its output to more expensive software, I found it to be a useful and reliable tool. The spreadsheet is available for download from Kai Reinke's website, at a very reasonable price. In fact, it's cost is probably equal to the round trip commissions on one trade.
Options Analysis For The Price Of Lunch?
For what it does and the programming involved, the spreadsheet is extraordinarily affordable. Stock and option data is downloaded from free sources on the Internet, so you're not tied into a monthly or annual contract for a data feed. A manual is available both online and as a separate download.
Should you consider picking up a copy? If you are having difficulty understanding why your spreads are behaving as the do, or you would like a tool that helps manage complex spread positions, then take look because for the cost of lunch for two help is but a download away!
Stock Options Analysis Resources
Kai Reinke's Excel Options Calculator v.10
Visualize trades and adjustments before
you make them, for less than $40!
Option Volatility & Pricing
Sheldon Natenberg's defacto bible on
option pricing and option strategy.
Using Excel Stock Option Calculator
See how Kai Reiinke's option calculator was
used to plan and adjust a diagonal spread.