Options Pricing in Excel

[TWI]

Does anybody have a recommendation for a good options addin for excel.
I need to price options on futures and forwards.
Ideally I would like to find something that includes pricing for options on mean price reverting underlying with spikes but something basic would be a start. If anybody knows anything about this other subject however I am interested in talking to you.

 

[Profitaker]

Does anybody have a recommendation for a good options addin for excel.
I need to price options on futures and forwards.

Try Hoadley
http://www.hoadley.net/options/options.htm

Ideally I would like to find something that includes pricing for options on mean price reverting underlying with spikes but something basic would be a start.

Do you mean an options pricing model that incorporates gap risk, rather than assuming continuous trading ?

 

[TWI]

Profitaker, that appears to be a good product. Thanks.

Not exactly gap risk. I am having a look at the following which may prove useful but will also better explain to you than I can here what I am attempting to price.
http://www.energyforum.net/downloads/reports/Mean-reverting_with_spikes.pdf

 

[Profitaker]

Not exactly gap risk.

Just wizzing through that paper, it looks like they're trying to model a very unusual asset, with both seasonal and very peculiar spike tendencies.

Can’t help I’m afraid. My next best suggestion would be to look at the distribution in question, and see if you can’t adjust a “normal distribution” with Kurtosis / skew parameters so that it fits (more or less) the distribution your looking at (electricity).

Good luck.

 

[dewitte]

Does anybody have a recommendation for a good options addin for excel.
I need to price options on futures and forwards.
Ideally I would like to find something that includes pricing for options on mean price reverting underlying with spikes but something basic would be a start. If anybody knows anything about this other subject however I am interested in talking to you.

A page with some less common VB codes, perhaps something you can use:

http://www.vbnumericalmethods.com/vbnumericalmethods/financial.asp

 

[Robertral]

Profitaker, that appears to be a good product. Thanks.

Not exactly gap risk. I am having a look at the following which may prove useful but will also better explain to you than I can here what I am attempting to price.
http://www.energyforum.net/downloads/reports/Mean-reverting_with_spikes.pdf

Please tell me more? you're trying to price options on an asset that follows a mean-reverting process, like voltatility?

BS on stock assumes dS/S = mu.dt + sig.dW

Mean reversion assumes dS = K (M-aS).dt + sig.dW

But you are after the mean reversion with jumps yeah?????
something like dS = K(M-aS).dt + sig.dW + b.dP

Where dP follows a Possion Process

Is this what u are after for ur unlerying process????

 

[A Dashing Blade]

You want Haug:Complete Guide to Options Pricing Formulas


http://www.amazon.co.uk/exec/obidos...8587/sr=1-1/ref=sr_1_10_1/202-2443210-5177446

comes with a companion disk containing vba code for pretty much everything you'll need.

 

[TWI]

Robertral,
I am not sure as I am no options expert but I think you are correct yes. I was looking for a way to better price options for these markets as I think a lot of people including myself are still mispricing them using standard Black Scholes model. Wanted to initially look at a number of alternatives and see how they compared.

Profitaker, dewitte and A Dashing Blade thanks for your replies. The website and book look very useful.

 

[Robertral]

Robertral,
I am not sure as I am no options expert but I think you are correct yes. I was looking for a way to better price options for these markets as I think a lot of people including myself are still mispricing them using standard Black Scholes model. Wanted to initially look at a number of alternatives and see how they compared.

Profitaker, dewitte and A Dashing Blade thanks for your replies. The website and book look very useful.

The BS will not capture the jumps and so ur calls are going to be too cheap

Well I know there are closed form solutions for jump-process's but not for where the underlying mean reverts. You would have to preform some stoch calc on the underlying process I suggested.... call(t,....) = exp(-rt)E[max(S-K,0)],

Or as I suggested by someone else, numerically generate the distribution (either from observed or via MC) and numerically calculate the option price.

 

[TWI]

Robertral, when I have learnt a little more about this I would certainly be interested to talk to you further about it. I will let you know if I find a suitable solution before that.
Thanks for your comments,

 

[Robertral]

Robertral, when I have learnt a little more about this I would certainly be interested to talk to you further about it. I will let you know if I find a suitable solution before that.
Thanks for your comments,

Try looking up pricing formula for options on volatility as volatility follows the mean reverting process but does not have the jump component in there. Saying that, so does the elect contracts so I don't know of any available formula that offers all three components....
If I get time I will have a go at pricing this for you.

 

[Pitscum]

guys whatever formula you use they all are only as good as the user. ....ffs dont trust any model in the last 3 weeks of expiry, no mater what they say they go nutz..then its down to experience.....

 

[TWI]

Pitscum,
I have been pricing in European energy using same standard model as everybody else. What I need to know is the possibility that this is a mispricing. The markets are still immature and options are all OTC bespoke products. Many of the players in the utilities are very basic in their understanding of derivatives. This gives me an advantage but would be better if I could find a better model.

 

[Pitscum]

what sort of european energy? you mean electricity? the b +s with a little bit of jump diffusion chucked in worked absolutely fine. what you have to watch is to remember to adjust the interest rate variable depending whther you are long or short premium. thats something the utlities dont bother to work out meaning, veyr often, yes you can do conversions and reversions with them cos they think you have screwed up. other than that, i disagree with the statement they are basic in their understanding of options.. there are some good players out there now

 

[Profitaker]

In my never ending quest to learn, i have a couple of questions if you don't mind...

guys whatever formula you use they all are only as good as the user.....

In what respect ?

....ffs dont trust any model in the last 3 weeks of expiry, no mater what they say they go nutz..then its down to experience.....

How does the passage of time affect the accuracy of the any option pricing model ? Whats is "ffs" ?

what you have to watch is to remember to adjust the interest rate variable depending whther you are long or short premium

What do you mean by "adjust" ? Personally I use the forward asset price in any model.

guysthats something the utlities dont bother to work out meaning, veyr often, yes you can do conversions and reversions with them....

You mean cost of carry isn't properly accounted for in the options price ? Where do I sign up ?

 

[Pitscum]

In my never ending quest to learn, i have a couple of questions if you don't mind...

In what respect ?

if you choose to trade whatever the model says then i can guarantee you will run into trouble. the model does not take into account paper flow at that current time; political events even something as simple as what will happen when the future breaks that head and shoulders on the daily chart. so, the numbers exist as a guide. its your job, as the trader, to interpret them and use them accordingly.

How does the passage of time affect the accuracy of the any option pricing model ? Whats is "ffs" ?

ffs is a polite way of urging you not to trust the models numbers towards expiry. they go very very innaccurate the closer you are to expiry. e.g. a strike 50 ticks away maybe be only worth 2 ticks on your sheets a week to expiry and they are offered at 4 but, if you're short 250 of them, look at the bigger picture, the volatility of the underlying you're trading etctec.. so given an unpredictable scenario, would i buy them in? yes definately.

What do you mean by "adjust" ? Personally I use the forward asset price in any model.

adjust means if my book was long premium, technically, ive borrowed money to finance the position, remember we are talking otc here not exchange cleared margined vanilla stuff

You mean cost of carry isn't properly accounted for in the options price ? Where do I sign up ?

C o C wasnt an issue for every player because many didnt have to bother. sounds great huh? however, otc energy mkts (gas and electricity) are incredibly thin and infrequently traded.

 

[TWI]

i disagree with the statement they are basic in their understanding of options.. there are some good players out there now

Pitscum, yep I disagree with myself there as well. I know a few of them and they are pretty smart. You in this market?

 

[Pitscum]

used to be in them. way too much red tape, docs, credit and risk management shyte for me to try and cope with and trade!! fraid its one of those markets that should be enormous but wont be because the utlities dont want the market open. they make so much cash anyway making leccie and flooging it to a retail base trading revenues are porbably less than 2% of their p/l. other facts stopping the mkt growing is the lack of 'paper' to get things going. sometimes the same call spd would be quoted for a week and then still not trade...paint dries quicker.

 

[TWI]

Getting better now. I left it for 3 years post Enron due to the complete death of liquidity and just traded my own account but now I am back as things are picking up. All the investment banks are getting in and new funds are appearing. There is a lot of interest from the investment community to diversify into this area. Actually the scarcest commodity appears to be people with experience in this area.
I do believe this time it will take off. I am betting a lot on it.