Options Strategy Evaluation Tool
For FAQs relating specifically the Finance Add-in for Excel and associated applications, see Add-in FAQs.
|Why does OSET show as "read only"? Why can't I save OSET?
|Using the Options Strategy Evaluation Tool
See Finance Add-in for Excel system requirements.
Installation: Download the file from this web site and double click to install.
It is strongly recommended that, following installation you copy the OSET file (OptionStrategy Mk3.xls) to your usual documents folder and use this rather than the original in the Program Files directory. That will prevent the file showing as "read only" in Windows Vista or later (see Why does OSET show as "read only?"), and will stop you losing saved strategies if you install a later version of OSET in the future.
If OSET shows as "read only" in the Excel title bar -- and you therefore can't save OSET -- then that's caused by your Windows security settings.
In Windows Vista or later, files installed into the Program Files directory (which is where OSET is installed by default) are treated as "read only" unless you have your User Account Control (UAC) in Windows on its lowest setting.
The solution to this "issue" is not to change your Windows security settings, but simply to copy the OSET file itself (OptionStrategy Mk3.xls) to your usual documents folder on your hard drive following installation. Make sure this folder is a trusted location in versions of Excel 2007 or later. You do that by adding the folder to Trusted Locations in Excel's Trust Center.
You will then be able to use and save your files as normal.
The premium features are a range of features which greatly increase the power of the Options Strategy Evaluation Tool (OSET). See premium features for details.
To enable the premium features in OSET, simply purchase and install the Hoadley Finance Add-in for Excel from this web site. No other action is required. OSET will automatically detect that you have installed the add-in and the premium features will immediately be available for you to use.
For a summary of OSET premium features, click here.
By far the most common reason is that you haven't installed the application correctly.
Other reasons can range from corrupt or incorrectly installed versions of Excel to Windows corruptions.
Please investigate these possibilities thoroughly yourself before asking for help. Whilst it is conceivable there is a problem with the software that hasn't shown up after many thousands of installations, it is, to be blunt and direct, far, far more likely that you have screwed up the installation (eg copied the strategy spreadsheet to another PC without installing it first), or your PC configuration is faulty.
If all else fails, please contact Peter Hoadley for help.
The Options Strategy Evaluation Tool when you first download it is set to use a graphics card of 1024 x 768 resolution. If your PC has another graphics resolution, then adjust the zoom factor by selecting the appropriate graphics resolution from the combo box under the "Underlying assets, settings" tab. This will automatically scale each page of the tool to fit your screen. Then save the tool.
Note that the tool is optimised for resolutions of 1024 x 768 and above. Whilst it will run correctly on a screen resolution of 800 x 600 some of the text labels in combo boxes and other places is pretty hard to read at this resolution.
For an overview of the main features of the tool it is recommended that you take the seven minute on-line guided tour.
To use the tool you first set up your stock parameters (stock code, volatility) and any dividends due under the "Underlying assets, settings" tab. ie click this tab or sheet on the bottom of the spreadhseet.
Dividends can be specified as being of up to four discrete payments during the life of the option (ie dollar amounts at specific ex-dividend dates) or continuous (ie an annualised rate). For most short-term stock options it's better to specify discrete dividend payments if possible; continuous dividends are more useful for longer term stock options and are an absolute requirement for options on currencies, stock indices and futures contracts.
An annual volatility must also be specified for each stock. You also need to specify the risk free interest rate under the "Underlying assets, settings" tab.
You will see I have already set up a few stock codes including "SAMPL" which is the stock code used for the sample strategies. Just change these or add new codes for your own stocks.
Next, go to the "strategy evaluation" tab and set up a deal for a particular stock code by filling in the blanks (ie the bits highlighted in yellow). The stock code is entered at the top left hand corner of the tool and must have been previously set up on the "Underlying assets, settings" tab.
A deal may consist of up to ten option trades and two stock trades. For each options trade you must specify four items: an action (buy/sell), the option type (call/put), the number of options and the strike price. The tool will then automatically calculate the option price for each options trade using the specified calculation mode (Black-Scholes, or Binomial).
In addition to the above four items which must specified for each options trade you can also enter any or all of the following optional items for any or all of the individual option trades:
Implied volatility: For strategy purposes one volatility, specified under the "Underlying assets, settings" tab, covering all options for a stock will often be sufficient. However, to fine-tune the tool you can specify an implied volatility separately for each option trade to handle the "volatility smile" associated with the underlying asset (stock). An implied volatility calculator is supplied as part of the tool to help you estimate the implied volatility based on market prices.
Trade expiration date: You only need to enter the expiration date for each trade if it is different from the deal expiration date (eg for a calendar spread). Leave it blank and the tool will use the deal expiration date.
Override option price: If you want to override the option price calculated by the tool then enter a price here. You might want to do this, for example, when you have been quoted a specific price for an option, or you are looking at the forward profit profile for an option previously bought or written. Another reason to do this is to get the tool to calculate the implied volatility. In this case after entering an override price, click on the "calculate implied volatility" button for the trade and the implied volatility will be calculated.
The easiest way to see how to fill in the yellow bits is to retrieve one of the sample saved strategies and have a look at what's going on. Then press "cycle through time to expiration" to see how the time line changes as you get closer to expiration.
To compare two strategies, press "copy strategy to comparison area". This creates a copy of the strategy in another area (the "strategy comparison area"). Then press "switch to comparison area" to view this and to change any part of the copied strategy. The copied strategy and the original strategy will both be visible on the same pay-off diagram.
For more detailed analysis press the "Show Greeks" button. This will let you view the "Greeks" (delta, gamma, theta, vega, rho) and profit figures for each trade in the strategy separately, or for the net position (ie the entire strategy) graphically. As for the pay-off diagrams, you can change the days to expiration, either a day at a time or automatically, to simulate time decay.
At any time you can change the calculation model (Black-Scholes European; binomial-American; binomial-European) by selecting the appropriate model from a combo box on the main evaluation page. A different calculation model can be specified for the "strategy comparison area" thereby enabling those of a theoretical leaning to see the significance of different pricing models for deal profitability on the one pay-off diagram.
As well as viewing the profit results on pay-off diagrams, you can look at them in tabular form by pressing the 'switch to table' view button. In this view you can also see all the position "Greeks" for a range of stock prices.
See the on-line tutorials for a set of auto-demos which cover all the key features of the software.
Finally, just play around with it -- you'll soon figure it out.
The only fields that can be changed on the 'strategy evaluation' sheet are those highlighted in yellow. Other information, such as volatility, risk free rate, and dividends are changed by going to the 'underlying assets, settings' tab. Once you set up or change the information here it will be automatically used by the 'strategy evaluation' sheet. This is described above.
The reason it is designed this way is so that information which usually applies to all strategies, such as the risk free interest rate, or information which usually applies to all options for a given underlying asset, such as volatility and dividends, need only be changed in one place. This means, for example, if you want to change the default volatility for a stock you only have to change it once (ie on the 'underlying assets, settings' sheet) rather than having to change it for every strategy you have saved for the stock (which could be dozens of strategies).
To stop people accidentally overwriting formulas which are used by the software the parts of the tool which you do not have to change are protected. If Excel tells you a password is required then it means you are trying to update information which should be updated elsewhere.
Definitly not! Never change the file format of any spreadsheet downloaded from this web site.
The Options Strategy Evaluation Tool (OSET) is an Excel 97-2003 Workbook (XLS) -- ie compatibility mode. With this format OSET will run under all supported versions of Excel.
If you use "save as" to change the file type to the newer Excel format (eg XLSM) nothing will work correctly under any version of Excel. This applies to all the Excel applications available from this site (Historic Volatility Calculator, Implied Volatility Calculator etc).
Sometimes when you hit "save" under Excel 2007 or later you will receive an Excel message asking if you want to save the document in the "Office Open XML Format". Always click the "No" button.
There is no reason whatsoever to change the file format and contrary to what many people think, simply saving a spreadsheet in the new Excel format will usually result in errors for all but the simplest of spreadsheets. Never change the file format of any spreadsheet downloaded from this web site.
|Options Pricing; American vs European
The tool lets you choose the way options are priced:
American exercise: Most exchange traded equity options are American options (ie they can be exercised at any time before the expiration date) so you should select binomial American for equity options.
European exercise: If the options can only be exercised on their expiration date (like some index options) then you can select either Black-Scholes or the Binomial European as they give almost identical results even for options on dividend paying stocks.
More on pricing: Unlike the binomial model which can price both American and European options, the Black-Scholes model can only be used to price European options accurately, although for non-dividend paying stocks the American call price is exactly the same as the European call price and all three formulas produce/converge on the same result. This is because, assuming no arbitrage opportunities (ie the call is fairly priced) and ignoring transaction costs there is never any (rational) reason to exercise a call early.
For calls on dividend paying stocks there may be times, just prior to ex-dividend dates, when it might be worthwhile to exercise the call. Also prior to ex-dividend dates, European calls may be worth less than their intrinsic value -- and this can't happen for American calls. So for dividend paying stocks the American call may be worth more than the European call -- ie it has an early exercise premium, and the size of this premium varies greatly depending on the time to ex-dividend date and other things. The binomial American model handles this situation; the Black-Scholes model doesn't handle this as it only takes into account the position at expiration.
The situation is quite different for puts and even on non-dividend paying stocks the fair value of a deeply in the money European put can be less than its intrinsic value (due to the carrying costs on the positions which arbitrageurs undertaking conversions would have to carry through to expiration). American puts, on the other hand, cannot trade at a discount to parity as they would be quickly exercised by arbitrageurs.
It may also be optimal to exercise a deeply in the money put early -- an obvious example would be if the stock price fell to zero because the company went bankrupt. In this case there would definitely be nothing to gain (and in fact something to lose) by waiting until expiration. Other less extreme cases could also justify early exercise.
For these reasons -- ie because it may be worthwhile to exercise an American put early -- American puts will always be worth more than European puts. Once again, only the binomial American model handles this correctly.
To see how American pricing differs from European pricing, and how prices are affected by dividends use the American & European pricing and dividend impact analysis calculator on-line. To identify optimal early exercise thresholds for American options you can use the Option Strategy Evaluation Tool's early exercise report.
For more information on the approach to pricing see the section on Option Pricing Models.
These options can be handled by the tool, in a theoretically correct way using continuous dividend yields, as follows:
Options on foreign currencies: Set the continuous dividend yield for the currency option equal to the foreign risk-free rate. This is because holding foreign currency, which will pay you interest in the foreign currency at the foreign risk free rate which is continuously reinvested in the foreign currency, is analogous to holding stock paying a continuous dividend which is reinvested in additional units of the stock. So options on both these underlying assets are handled in the same way. See the example and additional explanation for FOREX options below.
Options on stock indices: Set the continuous dividend yield for the index option equal to the average dividend yield on the stocks in the index. You can also specify a "points multiplier" for the index to enable index points to be converted to monetary values on the profit & loss charts and tables.
Options on futures: The pricing of options on futures can be correctly handled by setting the continuous dividend yield for the option on the futures contract equal to the risk-free rate and replacing the spot price with the current futures price. However, calculating strategy profitability (as opposed to simple option pricing) can be tricky. To simplify this process, the Options Strategy Evaluation Tool lets you specify the underlying asset type as a futures contract, in which case it will automatically handle the pricing (ie no need to specify a dividend yield) plus it will take into account the impact on profitability of holding positions in a futures contract vs the underlying asset itself. See Why is there a separate asset type for futures contracts for details.
You can then select European or American pricing (see FAQ on pricing models below) in the normal way.
Foreign currency option (FOREX) example: You are in the United States of America and want to buy a put option in US Dollars (USD) over Japanese Yen (JPY), perhaps to "insure" investments you are holding in JPY against a strengthening USD. The strike would be expressed in terms of the cost of JPY in USD (eg .008), the risk free rate would be the US risk free rate (eg 4%) and the continuous dividend yield would be the dividend yield of the underlying asset -- ie the Japanese risk free interest rate (eg 1%).
Note that by definition you are, by buying the put on JPY, also buying a call on USD because your right to sell one currency is also the right to buy the other. ie each currency option is both a put and a call.
To identify optimal early exercise points you go to the 'Early Exercise' worksheet and run the report. This will identify the theoretical optimal early exercise thresholds -- the underlying asset price/date combinations where it could be optimal to exercise the option -- for the range of underlying assets being evaluated on the strategy evaluation worksheet. All options (which must be American-style exercise) will be searched for optimal early exercise points.
This information is valuable both to option holders and option writers. If you are a holder and either exercise too early or continue to hold beyond the optimal exercise date then you are not maximising your profit potential; if you are an options writer then you need to be able to anticipate the risk of early exercise so you can take evasive action in advance of this happening.
Note that American options can be exercised at any time prior to maturity -- an option holder is not obliged to act rationally and thus financially unsound early exercise 'decisions' are sometimes made: holders exercise when they shouldn't and don't exercise when they should. The Options Strategy Evaluation Tool, of course, just highlights those price/time combinations where it would make financial sense to exercise early.
When is it optimal for an option to be exercised? The following is a brief summary of the rules followed by the Options Strategy Evaluation Tool:
|Optimal Early Exercise Point
On the day before the
ex-dividend date(s), providing the dividend is greater than the present
value of the cost of holding the underlying asset to expiration and the
time value premium remaining on the call is negligible (ie the call is well
into the money). In practice when there are multiple dividend payments the
optimal exercise time will usually (but not always) be the day before the
last ex-dividend date.
Note that it is never optimal to
exercise a call just to collect the dividend as the price of the underlying
asset can be expected to fall by the amount of the dividend on the
ex-dividend date (where 'dividend' in this context is the cash dividend
adjusted for any tax impacts, such as imputation credits). You would
only exercise a call and carry the underlying asset as an alternative to
holding the option, not simply as a way of collecting the
When the value of the dividends
payable on the underlying asset (continuously compounded and reinvested in
additional units of the underlying asset) is greater than the cost of
holding the underlying asset to expiration and the time value premium
remaining is negligible. (The Options Strategy Evaluation Tool lets you
adjust the sensitivity of 'negligible'.)
|None, discrete, continuous
When the put is
sufficiently in the money so that time value premium remaining is
negligible. (The Options Strategy Evaluation Tool lets you adjust the
sensitivity of 'negligible')
the risk free interest rate the deeper in the money and the closer
to expiration the put will have to be to reach the early exercise
threshold. If there are one or more discrete dividends payable on
the underlying stock during the period of the option then it would
not normally be optimal to exercise the option before the last
ex-dividend date. It is often optimal to exercise a put
exactly on the last ex-dividend date.
Finally, a couple of general points:
Will an early exercise decision always turn out to be correct? Unfortunately, no. If, after exercise and on or before the option maturity date the underlying asset price moves so that the option, had you held it, would be out of the money then you could be worse off having exercised early.
Sell or exercise? Theoretically it is always better financially for an option holder to sell the option in the secondary market (and then to buy or sell the underlying asset at market price if required) rather than exercising when there is time value premium remaining on the option.
Practical considerations. The early exercise threshold produced by the Options Strategy Evaluation Tool using the above rules is the theoretical optimal price/time combination for early exercise. In practice bid/ask spreads, transaction costs, taxes and other market conditions can change this threshold significantly.
|Some More Advanced Points
Actually, it should be horizontal if your call really is covered. The problem is that you probably haven't set up your covered call using the correct number of units (ie shares) in the underlying asset.
The continuous dividend yield model is based on the a dividend yield specified as a percentage of the current underlying asset price. Hedging and covering options paying a continuous dividend yield require that you adopt a dividend reinvestment model. ie continuously purchasing additional units of the underlying asset at whatever the spot price of the asset is at the time. Luckily, under the dividend reinvestment model, the number of additional units purchased is independent of the spot price as when the spot price is high the dividend is larger but buys less units, and vice versa. ie constant units are purchased irrespective of the underlying asset price at the time.
The Options Strategy Evaluation Tool includes dividends received on underlying assets in the profit calculation. With assets paying a continuous dividend yield, the higher the value of the underlying asset at expiration the higher the value of the dividends received (ie constant units reinvested multiplied by the stock price at expiration). So in the absence of any adjustment to the initial number of shares purchased this produces a covered call pay-off diagram with the maximum profit line sloping gently upwards -- ie you don't really have a covered call.
In other words, because the "dividends" are being continuously reinvested like compounding interest, the number of units in the underlying asset you end up holding at expiry will be greater than at the start. So as a result you need to buy less units at the start to end up exactly covering your call at expiry. This applies to any asset where "dividends" are being continuously reinvested in additional units of the underlying, such as a holding in a foreign currency. Note that stocks are not like this -- they pay dividends in discrete amounts -- so strictly speaking you should be modelling stocks using discrete dividends rather than continuous.
But assuming you really do need the continuous model for a covered strategy, then the exact number of units of the underlying required for a European covered call is n*exp(-qt), where n is the number of call options sold q is the dividend yield and t is the time to expiry in years. You can actually key this formula directly into the "no. shares" field for the underlying asset as an Excel formula in the Options Strategy Evaluation Tool.
If you do this you will find your covered European call suddenly looks completely normal again, with a perfectly horizontal maximum pay-off line.
On the main sheet there is a check box to include funding cost/revenue in P&L.
If this box is not checked then the profit & loss figures and pay-off diagram will not include any costs (or revenues) from funding the deal. eg the cost of carrying the underlying asset for a covered call will not be included as part of the P&L calculation. This is the way pay-off diagrams are normally shown. For example, if you wrote a naked put then the fact that you had the use of the proceeds for the duration of the option and could have invested these funds and earned additional revenue is not normally taken into account. The profit & loss of the deal is simply calculated as the option pay-off at expiration plus the option premium.
Sometimes, however, it can be useful to include funding costs/revenues in the P&L figures and selecting this check box lets you do this. Some brief examples:
|Put-call parity: The Option
Strategy Evaluation Tool comes with a number of sample strategies. One
of these is an illustration of put-call parity, which consists of selling a
call, buying a put, and buying the underlying asset. (ie a short synthetic
position in the underlying plus a long position in the underlying). If
the funding box is not checked then the profit from put-call parity will
not be zero, but will equal the funding costs of the deal. Checking
the box will produce a profit of zero. ie this option basically lets you
include the funding cost/revenue from the cash component (X*exp(-rt)) of the
put-call parity equation in the profit calculation.
comparisons: Writing a covered call is the same as writing a naked put...
almost. If you don't include funding costs the profit from the covered
call will be a little greater than that from a naked put. The difference is
exactly equal to the funding costs (the cost of carrying the stock offset by
the initial credit from writing the options plus interest from dividends
received). If you check this box, the profits from both strategies will
be exactly the same. You can use the 'comparison area' of the tool to compare
the two strategies on the same pay-off diagram to check this out. So checking
this box lets you compare strategies which have identical payoffs but very
different funding requirements, like conversions, in a more meaningful
The funding calculation works out funding requirements based on the initial debit/credit from the option purchase/sale, the cost of purchasing/shorting the underlying asset, and discrete dividends received or paid from stock bought or shorted, and then calculates the interest cost/revenue on this figure. In the case of dividends the calculations take into account the timing of the dividends. The funding costs are included in both the profit at expiration and in the calculation of the profit at time now line.
All funding calculations are made on a continuously compounded basis using the risk free interest rate.
The application of funding costs is covered in the following on-line tutorials: Guided tour - the sample strategies, "what if" scenario analysis, and Dividends.
There are some significant differences between options on a futures contract ("futures options") and options on the underlying asset itself ("spot options").
Pricing: There is no cost associated with entering into a futures contract so there are no carrying costs -- unlike a position in the underlying asset itself. The cost of hedging a futures option is therefore different from hedging a spot option, put-call parity is similarly different etc -- ie the pricing of futures options is different from spot options (see pricing futures options).
Strategy profit/loss: When an options strategy involves a position in the underlying futures contract (eg a futures call option covered by a futures contract expiring at the same time as the call) then the calculation of strategy profit/loss is different from a strategy with a position in the underlying itself. To calculate the profit or loss of closing a strategy which includes a position in a futures contract (expiring at the same time as the options) it is necessary to calculate the current value of the futures contract, which equals the present value of the difference between the current futures price and the delivery price.
These differences can be important. If you just treat strategies on futures contracts as if they were strategies on the underlying assets themselves the results can be quite misleading. You may think you have spotted a great arbitrage opportunity when, in reality, none exists.
The Options Strategy Evaluation Tool takes these differences into account when calculating option prices and strategy profit or loss. You simply tell the tool, on the underlying asset sheet, whether or not the underlying is a futures contract and the necessary adjustments will be made automatically.
You can easily check that this is working as it should by setting up a strategy consisting of an at-the-money (ATM) synthetic long position in an underlying futures contract (ie long an ATM European futures call, and short an ATM European futures put) and a short position in the underlying futures contract. For all futures prices, the net profit/loss at expiration, and at all dates prior to expiration, should be, and is, exactly equal to zero.