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

sachin April 19th, 2020 at 5:27am

Hi There,

Could anybody please paste the VCG scripts here?
OR
GS script compatible modules please?
Thanks,
Sachin

Julio June 22nd, 2019 at 2:43pm

Man! Outstanding tool! Thanks man!

Peter June 18th, 2019 at 4:58am

Hi Suresh,

Yes, you can download the non-VBA version here:

Black Scholes Excel Only

it is from the Black and Scholes Page.

Suresh June 16th, 2019 at 11:51am

Hi , Thank you very much for the workbook. Do you have a excel book without VBA code? to understand the formulas? if yes can you please share?

Peter April 23rd, 2019 at 12:14am

Hi Deepak,

Apologies for the delay; I've been away on vacation.

The current version doesn't calculate intra-day values, however, it can do it and is easy to modify it.

In the Excel formula you will notice that I take today's date from the expiration date and divide that number by 365 to give the time in years. You can simply create a new cell, or modify the formula directly to use intra-day values. For example, if there was half a day left to expiration you would use 0.00136986 (0.50 / 365) in the formula.

Deepak April 18th, 2019 at 3:45pm

Hi Peter,
Thanks for making your spreadsheet available.
Does it work for looking at options PL at different times intraday? How would you input the time remaining?
And how accurate is the model for options pricing in the last day before expiration?

Thanks.

Peter February 19th, 2017 at 4:47pm

Hi Luciano,

1) The spreadsheet here calculates the option greeks. In the Excel formula OTW_BlackSholes() just modify the second input from "p" to either "d", "g", "t", "v" or "r" (without quotes).

2) Yes, the greeks for a strategy is the sum of the individual legs.

Luciano February 19th, 2017 at 11:27am

Hello Peter,

I got two questions.

1) Do you know where I can get an add in for excel to calculate option greeks?
2) How do I calculate the greeks of a multiple legs strategy? E.g.Is the "total" delta the sum of the single legs deltas?

Thank you and regards.

Luciano.

Peter January 12th, 2017 at 5:23pm

Hi Mike,

Thanks for the feedback, appreciate it!

I see what you mean, however, as stocks don't carry a contract size I left this out of the payoff calculations. Instead, the correct way to account for this when comparing stocks with options is to use the appropriate amount of shares that the option represents i.e. for a Covered Call, you would enter 100 for the volume of shares for every 1 option contract sold. If you were to use 1 for 1 it would imply that you only bought 1 share.

Up to you though...if you prefer to embed the multiplier into the calculations and use single units for the stock, that's fine too. I just like to see how many shares/contracts I am buying/selling.

Is this what you mean...I hope I've not misunderstood you?

Mike C January 12th, 2017 at 6:26am

FYI,

You have an error in your spread sheet depending on how you look at it. It involves the theoretical graph vs the payoff graph with a stock position involved.. For your payoff you id the leg as stock and do not use the option multiplier. For the theo and greek graph you always multiplying by the "multiplier" even for stock legs so your calculations are off by a factor of the "multiplier".

PS Do you still maintain this? I have expanded it and can contribute if you are.

Mike

1 2 3 … 12 Older → Page 1 of 12

Add a Comment

Subscribe for updates