As I mentioned on the Home page of this website, I trade options seriously. I have developed several option pricing models based on the Black-Scholes pricing methodology and use them to determine:
• Potential profit or loss
• Exit points
• The probability of a successful trade
The spreadsheet calculates the option value given the following inputs:
• Price of the underlying asset, ie, stock or ETF
• Strike price
• Implied volatility - calculated* on broker’s option chain
• Dividend yield expressed as a percentage
• Risk-free interest rate
• Days to expiration
Below is a video of how the model works.
* Implied volatility is commonly derived from the price/value of a particular option, which is determined based on supply and demand; therefore, implied volatility is a “plug” amount that evaluates to the current theoretical option price. Since the market is efficient but seemingly moves in an “emotional” way, Black-Scholes provides a theoretical value given ones market forecast for:
• Price of the underlying
• Volatility, and
• Term of the trade
I developed the above templates to help me become a more disciplined trader. They are packed with VBA (Visual Basic for Applications) code and it is difficult to split them up.
Feel free to use them as a model for your own spreadsheet development.
If you are interested in purchasing these, please email me.