Free download

Excel Option Pricer

Excel based Black and Scholes calculator for European stock/index options and American options using dividend yields.

Grab Your Free Copy

Enter your name and email and I'll send the workbook straight to your inbox.

We respect your email privacy. Unsubscribe any time.

Option Strategy Input

My option pricing spreadsheet will allow you to price European call and put options using the Black and Scholes model.

Understanding the behavior of option prices in relation to other variables such as underlying price, volatility, time to expiration etc is best done by simulation. When I was first learning about options I began building a spreadsheet to help me understand the payoff profiles of calls and puts and also what the profiles look like of different combinations. I've uploaded my workbook here and you're welcome to it.

Simplified

Simple Option Pricing Screen

On the "basic" worksheet tab you will find a simple option calculator that generates fair values and option Greeks for a single call and put according to the underlying inputs you select. The white areas are for your user input while the shaded green areas are the model outputs.

Implied Volatility

Implied Volatility Calculator

Underneath the main pricing outputs is a section for calculating the implied volatility for the same call and put option. Here, you enter the market prices for the options, either last paid or bid/ask into the white Market Price cell and the spreadsheet will calculate the volatility that the model would have used to generate a theoretical price that is in-line with the market price i.e. the "implied" volatility.

Payoff Graphs

Basic Option Payoff Graphs

The PayoffGraphs tab gives you the profit and loss profile of basic option legs; buy call, sell call, buy put and sell put. You can change the underlying inputs to see how your changes effect the profit profile of each option.

Strategies

Option Strategy Input

The Strategies tab allows you to create option/stock combinations of up to 10 components. Again, use the white areas for your user input while the shaded areas are for the model outputs.

Formulas

Theoretical and Greek Prices

Use this Excel formula for generating theoretical prices for either call or put as well as the option Greeks:

=OTW_BlackScholes(Type, Output, Underlying Price, Exercise Price, Time, Interest Rates, Volatility, Dividend Yield)
Type
c = Call, p = Put, s = Stock
Output
p = theoretical price, d = delta, g = gamma, t = theta, v = vega, r = rho
Underlying Price
The current market price of the stock
Exercise Price
The exercise/strike price of the option
Time
Time to expiration in years e.g. 0.50 = 6 months
Interest Rates
As a percentage e.g. 5% = 0.05
Volatility
As a percentage e.g. 25% = 0.25
Dividend Yield
As a percentage e.g. 4% = 0.04

A sample formula would look like =OTW_BlackScholes(c, p, 25, 26, 0.25, 0.05, 0.21, 0.015).

Implied Volatility

=OTW_IV(Type, Underlying Price, Exercise Price, Time, Interest Rates, Market Price, Dividend Yield)

Same inputs as above except:

Market Price
The current market last, bid/ask of the option

Example: =OTW_IV(p, 100, 100, 0.74, 0.05, 8.2, 0.01)

Support

If you're having troubles getting the formulas to work, please check out the support page or send me an email.

If you're after an online version of an option calculator then you should visit Option-Price.com

Much of what I learnt that made this spreadsheet possible was taken from the highly acclaimed book on financial modeling by Simon Benninga — Financial Modeling - 3rd Edition. If you're an Excel junkie, you'll love it.

118 Comments

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

← Newer 1 2 3 4 5 6 … 12 Older → Page 4 of 12

Add a Comment

Subscribe for updates