Can anybody help me find (simpel) excel based options models for fx and equities? I have real-time feeds ( will use static int rate and dividend data) which will allow me to make estimates for the greeks (especially delta, gamma and vega).
For European options otions calculations are generally quite straigtforward.
I have encoded the Black-Scholes formulae into an Excel AddIn and use it extensively.
However, American options are very different. Trading wise they are somewhat similar, but the devil lies in the detail.
The problem essentially boils down to the question of how to value the additional optionality which American options contain - namely the early execrcise portential.
With an European option, prior to expiry, you can only sell the option. With an American you have the choice of either selling the option or exercising it.
This requires rather cumbersome (but not impossible or, even, beyond Excel) analytics to value them.
However, most index options are European so Black-Scholes applies.
In my experience, the price outputted by any options model is fine ...... in theory, but usually bears little relationship to the actual market prices. Reasons: volatility smiles, liquidity, "normal" distribution and many other assumptions which these models require to be made for them to work mathematically.
My take on them:
Use the options valuation models as a framework within which to gauge likely future values of options - but never as a basis on which to trade them. Always allow a relatively large margin of error.
Anyone else have a view on how well the current slew of options valuation models perform in reallity????
The problem with most option models is they do not integrate with the underlying instrument for volatility and dividend adjustments, these have to be worked out and typed in.
The other problem with option models is simply that they do not calculate the true value correctly as long term capital management found out.
That is because shares and indices do not follow a gaussian model and any model based on the data with have an inherent tendency to underestimate the incidence of extreme events, thereby underestimating the price. This will not be obvious until you get seriously hit which may be after many years of making consistent profits from naked writing.