ChartsOptionsOptions strategies

Binomial Option Pricing Model Excel

Written by Priya Kumar
Wed Jul 29 2020
Binomial Option Model
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Binomial Option Model
 

The Binomial Option Pricing Model Excel is available as a template with MarketXLS. The Binomial Option Pricing Model is a popular model for stock options evaluation, and to calculate the options premium.

The Binomial Options Pricing Model provides investors with a tool to help evaluate stock options. The model uses multiple periods to value the option.  For each period, the model simulates the options premium at two possibilities of price movement (up or down). The periods create a binomial tree — In the tree, each tree shows the two possible outcomes or the movement of the price.

Binomial Option Model

The model creates a binomial distribution of possible stock prices for the option. It creates possible paths that the stock price could go until the expiration date and the resulting impact on the options premium. Unlike the Black Scholes model of valuation of the option premium, the Binomial model gives you a view of an option contract at different prices at different periods until the expiration date.

Black-Scholes Vs Binomial Model

Black-Scholes model assumes that the option contract you are pricing is a European style option contract. A European style option contract is the one that can only be exercised at the date of the Expiry. The Americal style options contracts are the ones that can be exercised on any day until the expiry. Unlike, the Black Scholes model the Binomial option pricing model excel calculates the price of the option at various periods until the expiry. Since most of the exchange-traded options are American style options, the Black Scholes model seems to have a limitation.

If you were to assume that each period (days/weeks/months) until the expiry is the expiry date itself, you could also use the Black Scholes model to calculate a similar pay off table showing the value of the option for each period until expiry.

See the example below, where I use the Black Scholes model to generate a payoff for an option contract until the expiry date by assuming each day until the expiry is the expiry date. You can refer to our Options Profit Calculator template here.

Binomial Option Model vs Black Scholes Option Model

How do you calculate the Option Premiums using the Binomial Model?

The Binomial Option Pricing Model Excel takes the following as the Inputs. For example, I have taken a Call Option of American Airlines expiring on August 7th, 2020 and today is 29th of July 2020.  So, there are 10 days left until the expiry. The variable T as shown below in the days to expiry and n is the number of steps that we need in our Binomial tree. The current price of this option is 0.54 per contract. And the stock price is at 11.77. The following table shows other values and assumptions.

S = 11.77 #underlying pricek = 12 #Strike pricer = .04 #Riskfree ratev = .81 #VolatilityT = 10./365 #Time to maturityn = 10 #StepsUn= 1 #1 Unit is 100 stocksPC = 0 #Call option

The first step in the calculation is to create a binomial tree. This tree will have a specified amount of time that ends at the expiration date. Each point on the tree is a node. And each node is the price the stock can go at. The following image shows the binomial tree for the stock price movement(in table 1). So, for each period the table below shows the possible price movement on the underlying stock.

This chart below is the table for the price of the stock and the one below it is the table for the price of the option contract at corresponding prices (in table 2). And finally we have a table that shows the expected payoffs (in dollars) at these prices (in table 3) until the expiry when we buy 1 contract of this call option.

TABLE 1:

TABLE 2:

Binomial Option Model - Option Premium

TABLE 3:

Binomial Model PayOff Table

The binomial tree diagram represents the option payoff and probability at different nodes. Nodes outline the paths the price of the underlying asset may take over time. The following binomial tree represents the general one-period call option.

Binomial Option Pricing Model Excel

The option value using the one-period binomial option pricing model can be worked out using the following formula:

Binomial Option Pricing Model Excel

The put option uses the same formula as the call option:

Where:

C+ is the payoff of an up move;

C- is the payoff of the down move;

π is the probability of an up move;

1-π is the probability of the down move;

r is the discount rate.

Where π is the probability of an up move which is determined using the following equation:

Binomial Option Pricing Model Excel

Where:

t is the period multiplier (time to maturity);

r is the discount rate;

d is the down factor;

u is the up factor.

The binomial option pricing model excel is useful for options traders to help estimate the theoretical values of options. Price movements of the underlying stocks provide insight into the values of options premium. The model offers a calculation of what the price of an option contract could be worth today.

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates

Top 100 Gainers Today

Top 100 losers Today

Real gdpReal personal consumption expReal private investmentReal govt expenditureReal net exportsReal exportsReal importsFederal receiptsFederal outlaysFederal surplus or deficitFederal debtReal private investment nonresidentialReal private investment residentialReal potential gdpReal personal incomeReal personal consumption exp monthlyRpce durable goodsRpce nondurable goodsRpce servicesPersonal savings rateMonetary baseCurrency in circulationBank reservesMoney supply m1Money supply m2Sp500DjiaWilshire indexVixFinancial stress indexCorporate bond index aaCorporate bond index bbbFederal funds rateTreasury rate 3mTreasury rate 1yTreasury rate 5yTreasury rate 10yTips 5yTips 10yBond yield aaaBond yield baaMortgage rate 15yMortgage rate 30yUs dollar weighted averageUsdollar to euroUsdollar to poundYuan to usdollarCanadiandollar to usdollarYen to usdollarCpiCpi wo food energyCpi foodCpi energyChain price indexChain price index wo food energyGdp price deflatorPpi final demandPpi finished goodsPpi materialPpi crude goodsPpi final demand wo food energyPpi finished goods wo food energyHouse price indexHouse price index 20cityCrude oil priceGasoline priceNatural gas priceIndustrial productionCapacity utilisationInventoriesSales retail foodVehicle sales light weightManufacture orders durablesManufacture orders capital goodsLoansConsumer credit outstandingCorporate profitsHousing startsBuilding permitsResidential constructionEmployees nonfarmEmployees privateEmployees goods producingEmployees service providingEmployees governmentUnemployment rateInitial cliamsAverage weeks unemployedJob openingsHiresSeparationsQuitsLayoffs dischargesHours of productionHourly earningsReal outputPopulationLabor forceLabor force participation rate
Search for a stock

Top MarketXLS Rank stocks

CRA InternationalInc. logo

CRA InternationalInc.

89.32
 
USD
 
1.50
 
(1.71%)
81
Rank
Optionable: Yes
Market Cap: 646 M
Industry: Business Services
52 week range    
78.05   
   115.47
Booz Allen Hamilton Holding Corporation logo

Booz Allen Hamilton Holding Corporation

90.36
 
USD
 
1.83
 
(2.07%)
81
Rank
Optionable: Yes
Market Cap: 11,592 M
Industry: Business Services
52 week range    
69.32   
   90.99
Gaming and Leisure Properties Inc. logo

Gaming and Leisure Properties Inc.

45.86
 
USD
 
-0.26
 
(-0.56%)
79
Rank
Optionable: Yes
Market Cap: 11,555 M
Industry: REIT - Diversified
52 week range    
40.57   
   48.57
General Mills Inc. logo

General Mills Inc.

75.45
 
USD
 
0.73
 
(0.98%)
79
Rank
Optionable: Yes
Market Cap: 42,311 M
Industry: Packaged Foods
52 week range    
55.38   
   75.00
Sanderson Farms Inc. logo

Sanderson Farms Inc.

215.53
 
USD
 
-3.08
 
(-1.41%)
78
Rank
Optionable: Yes
Market Cap: 4,908 M
Industry: Packaged Foods
52 week range    
175.42   
   221.63
Ituran Location and Control Ltd. logo

Ituran Location and Control Ltd.

24.49
 
USD
 
0.01
 
(0.04%)
77
Rank
Optionable: Yes
Market Cap: 504 M
Industry: Communication Equipment
52 week range    
19.61   
   29.50
Republic Services Inc. logo

Republic Services Inc.

130.87
 
USD
 
1.12
 
(0.86%)
76
Rank
Optionable: Yes
Market Cap: 40,406 M
Industry: Waste Management
52 week range    
109.24   
   145.00
ConAgra Brands Inc. logo

ConAgra Brands Inc.

34.24
 
USD
 
-0.09
 
(-0.26%)
76
Rank
Optionable: Yes
Market Cap: 16,287 M
Industry: Packaged Foods
52 week range    
29.80   
   36.67
Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

104.69
 
USD
 
-5.36
 
(-4.87%)
75
Rank
Optionable: Yes
Market Cap: 8,406 M
Industry: Auto & Truck Dealerships
52 week range    
71.77   
   123.60
Franklin Covey Company logo

Franklin Covey Company

46.18
 
USD
 
8.15
 
(21.43%)
75
Rank
Optionable: Yes
Market Cap: 557 M
Industry: Education & Training Services
52 week range    
33.41   
   52.52

More Features

Stand with Ukraine

As the situation in Ukraine escalates, many of us in MarketXLS are left with emotions too overwhelming to name. If you’d like to show your support, but aren’t sure how to, we want to help make it easier for you to act.

For any amount donated, we’ll extend your MarketXLS subscription for double of the donated amount. Please send proof of your payment to support@marketxls.com to avail the extention

From all of us at MarketXLS, thank you!