Comments (74)

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