Comments (97)

Peter

April 30th, 2013 at 9:38pm

Hi Wong,

Yes, your numbers sound right. What worksheet are you looking at and what values are you using? Perhaps you could email me your version and I can take a look? Maybe you're looking at the P&L that includes time value - not the payoff at expiration?

wong

April 28th, 2013 at 9:05pm

hi, thanks for the worksheet. However, I am troubled by the calculated P/L on expiration. It should be made of two straight lines,joined at the strike price, right? but I did not get that. For example, for a put with strike $9, premium used is $0.91, the P/L for underlying price of 7, 8, 9, 10 were 1.19, 0.19, -0.81, -0.91, when they should be 1.09, 0.09, -0.91, -0,91, isn't that correct?

Peter

April 15th, 2013 at 7:06pm

Mmm...the average volatility is mentioned in cell B7 but not graphed. I didn't want to graph it as it would just be a flat line across the graph.

You're welcome to add it though - just email me and I'll send you the unprotected version.

Ryan

April 12th, 2013 at 9:11am

Sorry, I reread my question and it was confusing.. I'm just wondering if there is a way to also throw in Avg Volatility into the graph?

Peter

April 12th, 2013 at 12:35am

Hi Ryan,

Not sure if I understand correctly. The current volatility is what is graphed - the volatility calculated each day for the time period specified.

Ryan

April 10th, 2013 at 6:52pm

Hi,

Great volatility spreadsheet.. I'm wondering if its at all possible to track what the 'current' volatility is. Meaning just like your Max and Min are plotted on the chart, is it possible to add current, so we can see how its changed? If its not at all possible, do you know a program or willing to code this?

Thanks, Ryan

Peter

March 21st, 2013 at 6:35am

Hi Desmond,

The VBA is unlocked - just open the VBA editor and all of the formulas are there.

Desmond

March 21st, 2013 at 3:16am

can i know the formular in deriving the Theoretical Price in the basic tab

Peter

December 27th, 2012 at 5:19am

Hi Steve,

No, not yet, however, I found this site, which seems to have one;

Binary Options Excel

Let me know if it's what you're after.

Steve

December 16th, 2012 at 1:22pm

Terrific spreadsheets - thanks much!

Do you by any chance have a way to calculate theo prices for the new binary options (daily expriations) based on the Index futures (ES, NQ, etc.) that are traded on NADEX and other exchanges?

Thank you so much for your current spreadsheets - very easy to use and so so helpful.

-S

Peter

October 29th, 2012 at 11:05pm

Hi Vlad,

Thanks for writing.

The VBA I used for the calculations are open for you to look/modify as needed inside the spreadsheet.

The formula I used for Theta is;

CT = -(UnderlyingPrice * Volatility * NdOne(UnderlyingPrice, ExercisePrice, Time, Interest, Volatility, Dividend)) / (2 * Sqr(Time)) - Interest * ExercisePrice * Exp(-Interest * (Time)) * NdTwo(UnderlyingPrice, ExercisePrice, Time, Interest, Volatility, Dividend)

CallTheta = CT / 365

Vlad

October 29th, 2012 at 9:43pm

Greetings,

I would like to know how you calculated the theta on a basic call option. I virtually got the same answers to you but the theta in my calculation is way off. Here are my assumptions..

Strike Price $40.0
Stock Price $40.0
Volatility 5.0%
Interest Rate 3.0%
Expiration in 1.0 month(s) 0.1

D1 0.18
D2 0.16
N(d1) 0.57
N(d2) 0.56

My Call Option Your Answer

Delta 0.57 0.57
Gamma 0.69 0.69
Theta -2.06 -0.0056
Vega 0.04 0.04
Rho 0.02 0.02
Option $0.28 $0.28

Thuis is the formula I have for theta in excel which gives me -2.06.

=(-1*((Stock Price)*((1/(SQRT(2*PI())))*EXP(-1*(((D1^2)/2))))*Volatility)/(2*SQRT(Months))) - Interest Rate*Strike Price*EXP(-Interest Rate*Months)*N(d2.

Thank you for taking the time to read this, look forward to hearing from.

Regards,

Vladmir

Peter

June 4th, 2012 at 12:34am

Hi Zoran,

Margin and premium are different. A margin is a deposit that is required to cover any losses that may occur due to adverse price movements. For options, margins are required for net short positions in a portfolio. The amount of margin required can vary between broker and product but many exchanges and clearing brokers use the SPAN method for calculating option margins.

If your option position is long, then the amount of capital required is simply the total premium paid for the position - i.e. margin will not be required for long option positions.

For futures, however, a margin (typically called "initial margin") is required by both long and short positions and is set by the exchange and subject to change depending on market volatility.

zoran

June 1st, 2012 at 11:26pm

Hello, as I am new in trading options on futures please explain to me how to calculate margin, or daily premium, on Dollar Index, as I saw on the ICE Futures US web page, that the margin for the straddle is only 100 Dollars. It is so cheap that if I bought call and put options with the same strike, and form the straddle, it is look profitable to exercise early one leg of the position? I have in my account 3000 dollars.

Sincerely, Zoran

Peter

May 21st, 2012 at 5:32am

Hi B,

iVolatility have FTSE data but charge $10 a month to access European data. They have a free trial though so you can see if it is what you need.

B

May 21st, 2012 at 5:02am

Hi ,

Any one knows how we can get FTSE 100 index Historical volatility

Regards,
B

Peter

April 3rd, 2012 at 7:08pm

Hi Darong,

I don't think VWAP is used by option traders at all...VWAP would more likely be used by institutional traders/fund managers who execute large orders over the course of the day and want to make sure that they are better than the average weighted price over the day.

You would need accurate access to all the trade information in order to calculate it yourself so I would say that traders would obtain it from their broker or other vendor.

Darong

April 3rd, 2012 at 3:41am

Hi Peter,
I have a quick question as I just started to study Options...
For VWAP, normally, do option traders calculate it by themselves or tend to refer to calculated value by information vendors, or etc.? I want to know about market convention from traders' perspectives as a whole for option trading.
Appreciate if you revert to me.

Regards,

pintoo yadav

March 29th, 2012 at 11:49am

this is program in well mannered but required macros to be enabled for its work

Peter

March 26th, 2012 at 7:42pm

Hi Amitabh,

I suppose for short term trading the payoffs and strategy profiles become irrelevant. You'll just be trading off short term fluctuations in price based off expected movements in the underlying.

Amitabh

March 15th, 2012 at 10:02am

Hi Peter

How can this good work of yours be used for intraday or short term trading of options as these options make short-term tops and bottoms. Any strategies for same?

Warm wishes

Amitabh Choudhury
[email removed]

madhavan

March 13th, 2012 at 7:07am

First time I am going through any useful write up on option trading. Liked very much. But have to make an indepth study to enter into trading.

Jean charles

February 10th, 2012 at 9:53am

Hi Peter,

I have to say your website is great ressource for option trading and carry on. I was looking for your worksheet but for forex underlying instrument. I saw it but You don't offer to download.

[email removed]

Peter

January 31st, 2012 at 4:28pm

Do you mean an example of the code? You can see the code in the spreadsheet. It is also written on the Black Scholes page.

dilip kumar

January 31st, 2012 at 3:05am

Hi

please give example.

Peter

January 31st, 2012 at 2:06am

You can open the VBA editor to see the code used to generate the values. Alternatively you can look at the examples on the black scholes model page.

iqbal

January 30th, 2012 at 6:22am

Hi,

How is it that I can see the actual formula behind the cells that you have used to obtain the data? Thank you in advance.

Peter

January 26th, 2012 at 5:25pm

Hi Amit, is there an error that you can provide? What OS are you using? Have you seen the Support Page?

amit

January 25th, 2012 at 5:56am

hi..
The workbook is not opening....

sanjeev

December 29th, 2011 at 10:22pm

thanks for the workbook.

could you please explain me risk reversal with one or two examples?

P

December 2nd, 2011 at 10:04pm

Good day. Indian man trading today Found spreadsheet but does work? Look at it and needs fix to fix problem?

akshay

November 29th, 2011 at 11:35am

hello sir,

i am new to options and want to know how options pricing can help us...??

Deepak

November 17th, 2011 at 10:13am

Dear Sir ,

thanks for the reply .. but i am not able to collect the Historical Volatility , Risk Free Rate,Dividened Yield data .. could u please send me one example file for the stock NIFTY ...

Regards

Peter

November 16th, 2011 at 5:12pm

Hi Deepak,

You can use the spreadsheet on this page for any market - you just need to change the underlying/strike prices to the asset you want to analyze.

Deepak

November 16th, 2011 at 9:34am

Hello Sir,

I am looking for some options hedge strategies with excels for working in Indian markets ... Please suggest ...

Regards

Peter

October 30th, 2011 at 6:11am

Good evening.

NEEL 0512

October 30th, 2011 at 12:36am

HI PETER GOOD MORNING.

Peter

October 5th, 2011 at 10:39pm

Ok, I see now. In Open Office you must first have JRE installed - Download Latest JRE.

Next, in Open Office, you have to select "Executable Code" in Tools -> Options -> Load/Save -> VBA Properties.

Let me know if this doesn't work.

Peter

October 5th, 2011 at 5:47pm

After you have enabled Macros, save the document and re-open it.

Kyle

October 5th, 2011 at 3:24am

Yes, was receiving a $MARCOS? and $NAME? error. I have enabled the marcos, but still getting the $NAME? error. Thanks for your time.

Peter

October 4th, 2011 at 5:04pm

Yes, it should work. Are you having troubles with Open Office?

Kyle

October 4th, 2011 at 1:39pm

I was wondering if this spreadsheet can be opened with open office? If so how would i go about this?

Peter

October 3rd, 2011 at 11:11pm

Hi NK,

Whatever money costs you (i.e. to borrow) is your interest rate.

If you want to calculate the historical volatility for a stock then you can use my historical volatility spreadsheet.

You will also need to consider dividend payments if this is a stock that pays dividends and enter the effective yearly yield in the "dividend yield" field.

The prices don't have to match. If the prices are out, this just means that the market is "implying" a different volatility for the options than what you have estimated in your historical volatility calculation. This could be in anticipation of a company announcement, economic factors etc.

NK

October 1st, 2011 at 11:59am

Hi, i'm new to options. I'm calculating the Call and Put premiums for TATASTEEL(I used American Style options calculator). Date - 30 Sept, 2011.
Price - 415.25.
Strike price - 400
Interest rate - 9.00%
Volatility - 37.28%(I got this from Khelostocks.com)
Expiration Date - 25 Oct

CALL - 25.863 PUT - 8.335

Are these values correct or do i need to change any input parameters.
Also plz tell me what to put for Interest rate and from where to get the volatility for particular stocks in calculation.

The current price for the same options are
CALL - 27 PUT - 17.40.
Why is there such a difference and what should be my trading strategy in these?

Peter

September 8th, 2011 at 1:49am

Yes, it is for European options so it will suit the Indian NIFTY index options but not the stock options.

For retail traders I would say that a B&S is close enough for American options anyway - used as a guide. If you're a market maker, however, you would want something more accurate.

If you're interested in pricing American options you can read the page on the binomial model, which you'll also find some spreadsheets there.

Mehul Nakar

September 8th, 2011 at 1:23am

is this File Made in European style or American style option

How to USE in INDIA market
as Indian OPTIONS are trading in American style
can u make it American style model for Indian market user???

thanks in advance

Mahajan

September 3rd, 2011 at 12:34pm

Hi Peter,

Sorry for the confusion, but i am looking for some volatility formula only for futures trading (and not options).Can we use historical volatility in futures trading ? Any source/link you have, will be a great help to me.

Regards,
Mahajan

Peter

September 3rd, 2011 at 6:05am

Hi Gina,

15 points is the profit of the spread, yes, but you have to subtract the price that you have paid for the spread, which I assume is 5 - making your total profit 10 instead of 15.

Peter

September 3rd, 2011 at 6:03am

Hi Mahajan,

Do you mean options on futures or just straight futures?

The spreadsheet can be used for options on futures but is not useful at all if you are just trading outright futures.

Gina

September 2nd, 2011 at 3:04pm

If you look at Dec 2011 PUTs for netflix - I have a put spread - short 245 and long 260 - why doesn't this reflect a profit of 15 instead of 10?

Any idea?
Gina

Mahajan

September 2nd, 2011 at 6:58am

Hi Peter,

First of all tons of thanks for providing the useful excel.I am very new to options (previously i was trading in commodities futures).Can you please help me in understanding, how i can use these calculations for future trading(silver,gold,etc) ?

If there is any link please provide me the same.

Thanks again for enlightening thousand of traders.

Cheers,
Mahajan

Peter

August 26th, 2011 at 1:41am

Hi Edwin,

There isn't currently a sheet specifically for calendar spreads, however, you're welcome to use the formulas provided to build your own with the parameters needed.

You can email me if you like and I can try and help you with an example.

Edwin CHU (HK)

August 26th, 2011 at 12:59am

I am an active options trader with my own trade boob, I find your worksheet "Options Strategies quite helpful, BUT, can it cater for calendar spreads, I caanot find a clue to insert my positions when faced with options and fut contracts of different months?
Look forward to hearing from you soon.

Peter

June 28th, 2011 at 6:28pm

Oh, use the Contact Form.

Sunil

June 28th, 2011 at 11:42am

on which mail id should i send ?

Peter

June 27th, 2011 at 7:07pm

Hi Sunil, send me an email and we can take it the conversation offline.

Sunil

June 27th, 2011 at 12:06pm

Hi Peter, many thanks. I had gone through the VB functions but they use many inbuild excel functions for calculations. I wanted to write the program in Foxpro (old time language) which does not have the inbuild functions in it and hence was looking for basic logic in it. Never the less, the excel is also very useful, which i don't think anyone else has also shared on any site.

I went through the complete material on Options and you have really done a very good knowledge sharing on Options. You have really discussed in depth near about 30 strategies....Hats off. Thanks

Peter

June 27th, 2011 at 6:06am

Hi Sunil, for Delta and Implied Volatility the formulas are included in the Visual Basic provided with the spreadsheet at the top of this page. For Historical Volatility you can refer to the page on this site on calculating volatility. However, I am not sure on the profit probability - do you mean the probability that the option will expire in the money?

Sunil

June 26th, 2011 at 2:24am

Hi Peter,
How do i calculate the following. I want to write a program to run it on various stocks at a time and do first level scanning.
1. Delta
2. Implied volatility
3. Historical Volatility
4. Profit Probability.

can you please guide me on the formulas.

Peter

June 18th, 2011 at 2:11am

Pop up? What do you mean?

shark

June 17th, 2011 at 2:25am

where is the pop up

Peter

June 4th, 2011 at 6:46am

Hi DevRaj,

You can try my volatility spreadsheet that will calculate the historical volatility that you can use in the option model.

DevRaj

June 4th, 2011 at 5:55am

Very useful nice article and the excel is very good
Still one question
How to calculate volatility using (option price, spot price, time )
?

Satya

May 10th, 2011 at 6:55am

Hi Peter,

I have just started using the spreadsheet provided by you for option trade. A wonderful easy to use stuff with adequate tips for easy usage.

Thanks for your best efforts to help educate the society.

Regards
Satya

Peter

March 28th, 2011 at 4:43pm

It works for any European option - irrespective of the country where the options are traded.

Emma

March 28th, 2011 at 7:45am

Do you have it for Irish stocks???

Peter

March 9th, 2011 at 9:29pm

Hi Karen, those are some great points!

Sticking to a system/methodology is very hard...it is easy to be distracted by all of the offers out that are out there.

I am looking closely at a few option picking services right now and plan to list them on the site if they prove to be successful.

Karen Oates

March 9th, 2011 at 8:51pm

Is your option trading not working because you haven't found that right system yet or because you won't stick to one system?

What can you do to find the right system and then stick to it?

Could a lot of what is not working for you be because of how you are thinking? Your beliefs and mindset?

Working on improving yourself will help all areas of your life.

Peter

January 20th, 2011 at 5:18pm

Sure, you can use implied volatility if you like. But the point of using a pricing model is for you have your own idea of volatility so you know when the market is "implying" a value different to your own. Then, you are in a better position to determine if the option is cheap or expensive based on historical levels.

The spreadsheet is really more of a learning tool. To use implied volatilities for the greeks in the spreadsheet would require the workbook to be able to query option prices online and download them to generate the implied volatilities. That's why I have unlocked the VBA code in the spreadsheet so that users can customize it to their exact needs.

Alternatively, you can check out AnalyzerXL - they provide an Excel took that downloads option chains that you can use together with the option formulas in my spreadsheet.

t castle

January 20th, 2011 at 12:50pm

The Greeks that are calculated on the OptionPage tab of OptionTradingWorkbook.xls appear to be dependent on Historical Volatility. Should not the Greeks be determined by Implied Volatility? Comparing the values of the Greeks calculated by this workbook produces values that agree with, e.g., the values at TDAmeritrade or ThinkOrSwim only if the formulas are edited to replace HV with IV.

Peter

January 20th, 2011 at 5:40am

Not yet - do you have any examples you can suggest? What pricing model do they use?

r

January 20th, 2011 at 5:14am

anything available for interest rate options?

Peter

January 19th, 2011 at 8:48pm

It is the expected volatility that the underlying will realize from now until the expiration date.

general question

January 19th, 2011 at 5:13pm

hi, is the historical volatility input annualized vol, or vol for the period from today to expiration date? thanks.

imlak

January 19th, 2011 at 4:48am

very good, it solved my proble

SojaTrader

January 18th, 2011 at 8:50am

very happy with the spreadsheet
very useful
thanks and regards from Argentina

Peter

December 19th, 2010 at 9:30pm

Hi Madhuri, do you have Macros enabled? Please see the support page for details.

madhuri

December 18th, 2010 at 3:27am

dear friend,
same opinion i have about the spread sheet that
"this model doesn't work, no matter what you put in on the basic page for values, it has an invalid name error (#name?) for all the results cells. Even when you first open the thing, the default values the creator put in don't even work"

-madhuri

MD

November 25th, 2010 at 9:29am

Is these formulas will work for indian market? Please answer

rick

November 6th, 2010 at 6:23am

Do you have it for US stocks???

egress63

November 2nd, 2010 at 7:19am

Excellent stuff. Finally a good site with a simple and easy to use spreadsheet!

Thanks guys! :)

-A gratified MBA Student.

Dinesh

October 4th, 2010 at 7:55am

Guys, this works and it is pretty easy. Just enable macros in excel. The way it has been put is very simple and with little understnading of Options any one can use it. Great work specially Option Strategies & Option Page.

Peter

January 3rd, 2010 at 5:44am

The shape of the graphs is the same but the values are different.

robert

January 2nd, 2010 at 7:05am

All graph in Theta sheet are identic. Are Call Oprion Price graph data correct? thx

daveM

January 1st, 2010 at 9:51am

The thing opened immediately for me, works like a charm.!! and the Benninga book..... I am so pleased that you referenced it...

Great Stuff..!!

Thanks so much.

Peter

December 23rd, 2009 at 4:35pm

Hi Song, do you have the actual formula for Asian options?

Song

December 18th, 2009 at 10:30pm

Hi Peter,
I need your help about the Asian option pricing using excel vba. I don't know how to write the code.
Please help me.

Peter

November 12th, 2009 at 6:01pm

Does the spreadsheet not work with OpenOffice?

Wondering

November 11th, 2009 at 8:09am

Any solutions that will work with OpenOffice?

rknox

April 24th, 2009 at 10:55am

Very Cool! Very nicely done. You sir, are an artist. One old hacker (76 years old - started on the PDP 8) to another.

Peter

April 6th, 2009 at 7:37am

Hi Ken,

Take a look at the following page:

http://www.optiontradingtips.com/pricing/workbook-support.html

Ken

April 6th, 2009 at 5:21am

Hi, What if i am using the Office on Mac? it has an invalid name error (#name?) for all the results cells... thx

giggs

April 5th, 2009 at 12:14pm

Ok, it's working now. I saved & closed the excel file, opened again, and the results were there, in the blue areas! FYI, I had enabled all the macros in "Security of the macros" . Can't wait to play with the file now...

giggs

April 5th, 2009 at 12:06pm

I don't see the popup. I use Excel 2007 under Vista. The presentation is quite different from the previous versions. I enabled all macros. But I still get the #name error. Any idea?

giggs

April 5th, 2009 at 12:00pm

I don't see the popup. I use Excel 2007 under Vista. The presentation is quite different from the previous versions. Any idea?

Admin

March 23rd, 2009 at 4:17am

Hi Dissapointed,

The spreadsheet requires Macros to be enabled for it to work. Do you see a popup on the toolbar asking you if you want to enable this content? Just click it and select "enable".

Please send me an email if you need further clarification.

disappointed

March 22nd, 2009 at 4:25pm

this model doesn't work, no matter what you put in on the basic page for values, it has an invalid name error (#name?) for all the results cells. Even when you first open the thing, the default values the creator put in don't even work

Add a Comment

Name

Enter Verification Image

Comments

     Subscribe for Comment Updates