Excel based options models?

bgold

Established member
Messages
532
Likes
5
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).
thanks
JP
 
many thanks Roger. This service/product appears to be exactly what I am looking for. tx JP
 
You're welcome Toad. I use the free strategy calculator all the time. The online tutorials are first class.
 
Beware the Americans

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????
 
Last edited:
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.
 
Top