Excel Spreadsheets for Binary Options

This article introduces binary options and provides several pricing spreadsheets.

Binary options give the owner a fixed payout (which does not vary with the price of the underlying instrument) or nothing at all. Most Binary options are European-style; these are priced with closed-form equations derived from a Black-Scholes analysis, with the payoff determined at expiry.

The equations used in the following spreadsheets are sourced from “The Complete Guide to Option Pricing Formulas” by Espen Gaarder Haug.

Cash or Nothing & Asset or Nothing Options

Binary options can either be Cash or Nothing, or Asset or Nothing

  • A cash or nothing call has a fixed payoff if the stock price is above the strike price at expiry. A cash or nothing put has a fixed payoff if the stock price is below the strike price.
  • If the asset trades above the strike at expiry, the payoff of an asset or or nothing call is equal to the asset price. Conversely, an asset or nothing has a payoff equal to the asset price if the asset trades below the strike price.
Binary Options
This Excel spreadsheet prices Cash or Nothing & Asset or Nothing options

Download Excel Spreadsheet to Price Binary Options

Two-Asset Cash-or-Nothing Options

These binary options are priced across two assets. They have four variants, based upon the relationship between spot and strike prices.

  • up and up: These only pay if the strike price of both assets is below the spot price of both assets
  • up and down: These only pay if the spot price of one asset is above its strike price, and the spot price of the other asset is below its strike price
  • cash or nothing call: These pay a predetermined amount of the spot price of  both assets is above their strike price
  • cash or nothing put: These pay a predetermined amount if the spot price of both assets is below the strike prie
The following Excel spreadsheet prices all four variants using the solution proposed by Heynen and Kat (1996).

Download Excel Spreadsheet to Price Two Asset Cash or Nothing Options

C-Brick options  are constructed from four two-asset cash-or-nothing options. The holder receives a predetermined cash amount if the price of Asset A is between an upper and lower strike, and if the price of Asset B is between and upper and lower strike.

Download Excel Spreadsheet to Price C-Brick Options

Supershares

Supershare options are based on a portfolio of assets with shares issued against their value.  Supershares pay a predetermined amount if the underlying asset is priced between an upper and lower value at expiry.  The amount is usually a fixed proportion of the portfolio.

Supershare option pricing in Excel

Supershares were introduced by Hakansson (1976), and are priced with the following equations.

Supershare equations

Download Excel Spreadsheet to Price Supershares

 Gap Options

A Gap option has a trigger price that determines if the option will payout.  The strike price, however, determines the size of the payout.

The payout of a Gap option is determined by difference between the asset price and a gap, as long as the asset price is above or below the strike price.  The price and payout of a European style Gap option are given by these equations

Gap Option Price and Payout Equations

where X2 is the strike price and X1 is the trigger price.

Consider an call option with a strike price of 30, and a gap strike of 40.  The option can be exercised when the asset price is above 30, but pays nothing until the asset price is above 40.
Download Excel Spreadsheet to Price Gap Options


Leave a Reply

Your email address will not be published. Required fields are marked *

What is 8 + 3 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)