:: Subscribe to our newsletter
 
Articles / Directory
06/17/2013
Latest version of TraderCode(v5.6) includes new Technical Analysis indicators, Point-and-Figure Charting and Strategy Backtesting.



06/17/2013
Latest version of NeuralCode(v1.3) for
Neural Networks Trading.



06/17/2013
ConnectCode Barcode Font Pack - enables barcodes in office applications and includes an add-in for Excel that supports mass generation of barcodes.



06/17/2013
InvestmentCode, a comprehensive suite of
Financial calculators and models for Excel is now available.



09/01/2009
Launch of
Free Investment and Financial Calculator for Excel.



02/1/2008
Release of
SparkCode Professional - add-in for creating Dashboards in Excel with sparklines



12/15/2007
Announcing
ConnectCode Duplicate Remover - a powerful add-in for finding and removing duplicates entries in Excel



09/08/2007
Launch of
TinyGraphs - open source add-in for creating sparklines and tiny charts in Excel.


Options Valuation

Options

An option is a contract that gives a person or institution the right to buy or sell an asset at a specified price. A call option is a contract to buy an asset at a fixed price while a put option is a contract to sell an asset at a fixed price. The specified price is known as the option's strike price or exercise price. Options come with an expiration date, which is also known as the maturity date. This is the date when the option can no longer be exercised.

There are two important types of options, the American and the European. The American type option can be exercised any time up to the expiration date whereas the European type of option can only be exercised on the expiration date. One important usage of option is to adjust the risk exposure an investor has on the underlying assets.

Options Valuation spreadsheets

The Options Valuation package includes spreadsheets for Put Call Parity relation, Binomial Option Pricing, Binomial Trees and Black Scholes. The following is the entire list of the spreadsheets in the package. Each category of the spreadsheet is described in details in the subsequent sections.

Put Call Parity
  • PutCallParity.xls
Binomial Option Pricing
  • BinomialOptionPricing (European).xls
  • BinomialOptionPricing (per Annum Interest - European).xls
Binomial Trees

The following spreadsheets can be found in the “BinomialTrees” subfolder.
  • OneStepBinomialTree (European).xls
  • OneStepBinomialTree (American).xls
  • TwoStepBinomialTree (European).xls
  • TwoStepBinomialTree (American).xls
  • TwoStepBinomialTree (with Cox_Ross_Rubinstein u & d calibration - European).xls
  • TwoStepBinomialTree (with Cox_Ross_Rubinstein u & d calibration - American).xls
The following spreadsheets can be found in the “BinomialTrees/1000StepsFormula” subfolder. These two spreadsheets provide Excel Visual Basic for Applications (VBA) formulas for calculating option pricing up to 1000 steps in a Binomial Tree.
  • BinomilaOptionPricingVBAMacros – European.xls
  • BinomilaOptionPricingVBAMacros – American.xls
No Arbitrage, Risk Free Portfolio Replication Approach for valuing options

The following spreadsheets can be found in the “BinomialTrees\NoArbitrageRiskFreePortfolioReplication” subfolder.
  • OneStepBinomialTree (European).xls
  • OneStepBinomialTree (American).xls
Risk Neutral Valuation

The following spreadsheets can be found in the “BinomialTrees\RiskNeutralValuation” subfolder.
  • OneStepBinomialTree (European).xls
  • OneStepBinomialTree (American).xls
Black Scholes
  • BlackScholes.xls

Put Call Parity

The Put Call Parity assumes that options are not exercised before expiration day which is a requirement in European options. It defines a relationship between the price of a call option and a put option with the same strike price and expiry date, the stock price and the risk free rate.


The spreadsheet supports the calculation of the Stock Price, Put Price, Present value of Strike Price or Call Price depending on the input values provided. Simply leave the unknown variable as 0 and it will automatically be calculated by the program. Do note that only one unknown variable is supported at one time.

Binomial Option Pricing

For many years, financial analysts encounter difficulties developing a rigorous method for valuing options. This is until Fisher Black and Myron Scholes published the article “The Pricing of Options and Corporate Liabilities” in 1973 to describe a model for valuing options.

This model is famously known as the Black Scholes model. The Black Scholes model can be easily understood through a Binomial Option Pricing model. The model has a name “Binomial” because of its assumptions of having two possible states. Basically, the Binomial Option Pricing and Black Scholes models use the simple idea of setting up a replicating portfolio which replicates the payoff of the call or put option. The value of the portfolio is then observed to be the value or cost of the options.

The Options Valuation package includes the “BinomialOptionPricing (European).xls” spreadsheet, as described below, for calculating the value of both the European Call Option and European Put Option.

It is important to note that the "Risk free interest rate" in this spreadsheet is not specified on per annum basis. Instead, it is the risk free interest rate over the period of the current date to the exercise date.

An additional spreadsheet "BinomialOptionPricing (per Annum Interest - European).xls" is included to provide the same functionality as the binomial option pricing spreadsheet except for specifying the interest inper annum basis.

Binomial Option Pricing - Call Option

This first worksheet in the “BinomialOptionPricing (European).xls” spreadsheet sets up a replicating portfolio by borrowing money at the risk free rate and purchasing an amount of the actual stock to replicate the payoff of the Call Option. It then calculates the value (price) of the Call Option through observing the value of the portfolio.


Binomial Option Pricing - Put Option

The second worksheet sets up a replicating portfolio by lending money at the risk free rate and selling an amount of the actual stock to replicate the payoff of the Put Option. It then calculates the value (price) of the Put Option through observing the value of the portfolio.


Binomial Trees

The Options Valuation package includes several Binomial Trees spreadsheets as shown below. The spreadsheets can be found in the “BinomialTrees” subdirectory. These spreadsheets make use of the Cox, Ross and Rubinstein (CRR) technique introduced in 1979. The technique allows for complicated European and American options to be valued easily.

Both One Step and Two Steps Binomial Trees templates that use continuous compounding are provided. All the formulas and source code are provided to allow ease of customizations. The templates also serve as a useful tool for understanding the CRR techniques. Calibration of the Up Factor (u) and Down Factor (d) in the CRR are also implemented and illustrated in the Two Steps Binomial Trees spreadsheets.

List of spreadsheets
  • OneStepBinomialTree (European).xls
  • OneStepBinomialTree (American).xls
  • TwoStepBinomialTree (European).xls
  • TwoStepBinomialTree (American).xls
  • TwoStepBinomialTree (with Cox_Ross_Rubinstein u & d calibration - European).xls
  • TwoStepBinomialTree (with Cox_Ross_Rubinstein u & d calibration - American).xls

OneStepBinomialTree (European).xls

The first worksheet of this spreadsheet, as shown in the diagram below, implements a One Step Binomial Tree to value European Call options. The basic formulas used for the spreadsheet are as follows:
  • Call Option Price Now = (EXP((0-G7*G8/12))*(G21*E14+G22*E18))
  • Risk Neutral Probabilities p = ((EXP(G7*G8/12) -(1+ G10))/((1+G9)-(1+G10)))


The second worksheet, which is not shown here, implements a One Step Binomial Tree to value European Put options.

TwoStepBinomialTree (European).xls

This spreadsheet uses the same principles in the One Step Binomial Tree except that it is expanded to support a Two Step Binomial Tree. The basic formulas for calculating the risk probabilities p and valuing the option price are the same as the ones described in the previous spreadsheet.




TwoStepBinomialTree (with Cox_Ross_Rubinstein u & d calibration-European).xls

This spreadsheet uses the same principles in the Two Step Binomial Tree except that the Up Factor (u) and Down Factor (d) are calibrated using the technique described by Cox, Ross and Rubinstein.

The formulas for u and d are as follows:
  • u = EXP(G9*SQRT(G8/12))
  • d = EXP((0-G9)*SQRT(G8/12))


TwoStepBinomialTree (with CRR calibration - Continuous Dividend Yield - European).xls

The two spreadsheets below are similar to the Two Steps Binomial Tree with Cox, Ross and Rubinstein technique except that they make an additional assumption of the stock paying a Continuous Dividend Yield. In this case, the expected return of the stock must equal the risk free interest rate minus the dividend yield.
  • TwoStepBinomialTree (with CRR calibration - Continuous Dividend Yield - European).xls
  • TwoStepBinomialTree (with CRR calibration - Continuous Dividend Yield - American).xls
The screenshot below shows the Two Step Binomial Tree with CRR calibration and Continuous Dividend Yield.



The “Dividend Yield” is the only additional input field and the formula for “a” needs to be adjusted as follows:
  • a = EXP((G7-G8)*(G9/12))
where G8 is the dividend yield.

NStepBinomialTree (with CRR calibration - Continuous Dividend Yield - European).xls

The two spreadsheets below are the generalization of all the previous binomial tree spreadsheets. They support a variable N Step (up to 10) Binomial Tree using Cox, Ross and Rubinstein technique and allow a Continuous Dividend Yield to be specified.
  • NStepBinomialTree (with CRR calibration - Continuous Dividend Yield - European).xls
  • NStepBinomialTree (with CRR calibration - Continuous Dividend Yield - American).xls
The screenshot below shows how the number of steps in the Binomial Tree can be varied using the input "Number of Steps (up to 10)*".



American Options

The American options spreadsheets, which are not described here, are similar to the European options spreadsheets except that they evaluate the Binomial Trees to determine whether early exercise is feasible.

The spreadsheets use formulas to automatically work from the nodes at the end of the tree to the beginning to determine the greater of the value provided by the European valuation and the payoff from early exercise.
  • OneStepBinomialTree (American).xls spreadsheet
  • TwoStepBinomialTree (American).xls spreadsheet
  • TwoStepBinomialTree (with Cox_Ross_Rubinstein u & d calibration - American).xls spreadsheet.

Excel VBA formulas for calculating option price of up to 1000 steps

The following spreadsheets can be found in the “BinomialTrees/1000StepsFormula” subfolder. These two spreadsheets provide Excel Visual Basic for Applications (VBA) formulas for calculating option pricing up to 1000 steps in a Binomial Tree.
  • BinomilaOptionPricingVBAMacros – European.xls
  • BinomilaOptionPricingVBAMacros – American.xls
The formulas can be imported into any Excel spreadsheets and can be used together with existing financial models.
Formulas
The following is the list of formulas that can be found in the “BinomilaOptionPricingVBAMacros – European.xls” spreadsheet. The formulas return the option price based on the parameters provided:
  • =EuropeanCall(StrikePrice, StockPrice, Risk Free Interest Rate, Dividend Yield, Number Of Steps, Length Of Each Time Step in Months, Volatility)
  • =EuropeanPut(StrikePrice, StockPrice, Risk Free Interest Rate, Dividend Yield, Number Of Steps, Length Of Each Time Step in Months, Volatility)
The highlighted section in the following diagram shows how the formula can be used in an Excel spreadsheet.



The following list of formulas can be found in the “BinomilaOptionPricingVBAMacros – American.xls” spreadsheet. The formulas return the option price based on the parameters provided.
  • =AmericanCall(StrikePrice, StockPrice, Risk Free Interest Rate, Dividend Yield, Number Of Steps, Length Of Each Time Step in Months, Volatility)
  • =AmericanPut(StrikePrice, StockPrice, Risk Free Interest Rate, Dividend Yield, Number Of Steps, Length Of Each Time Step in Months, Volatility)
Both the European and American formulas support the calculation of option price up to 1000 steps using a Binomial Tree. It is important to note that the American opting pricing formulas can take a much longer time (more than several minutes) when calculating beyond 300 steps.
Importing the formulas to other spreadsheets
The following shows the steps for importing the Excel VBA formulas (for valuating option price) in other spreadsheets.
  • Launch Microsoft Excel and click on the Developer tab.
  • Click on the Visual Basic button on the ribbon.
  • This will launch the Visual Basic for Applications editor.
  • Click on the menu File->Import File...
  • Select the ImportEuropeamMacros.bas (or ImportAmericanMacros.bas) file in the "BinomialTrees/1000StepsFormula" folder.



  • Click on Save followed by Close in the Visual Basic Editor. You are now ready to use the formulas in the way as described in the previous sections.

The two approaches to valuing options

Options can be valued by using two different approaches: the "No Arbitrage, Risk Free Portfolio Replication" and the "Risk Neutral Valuation". Both approaches provide the same results of the current value of the option. These two approaches are described in the next two sections and are provided in the Options Valuation package for completeness.

No Arbitrage, Risk Free Portfolio Replication

The “No arbitrage, risk free portfolio replication” approach is implemented by the following spreadsheets. The spreadsheets can be found in the “BinomialTrees” subfolder.
  • NoArbitrageRiskFreePortfolioReplication\OneStepBinomialTree (European).xls
  • NoArbitrageRiskFreePortfolioReplication\OneStepBinomialTree (American).xls
The screenshot for valuing a Call Option using the “NoArbitrageRiskFreePortfolioReplication\OneStepBinomialTree (European).xls” spreadsheet is shown below.



In “No arbitrage” approach, a risk free portfolio that consists of a position in the stock and a position in the option is set up.

By equating the present value of the expected portfolio price to the current cost of setting up the portfolio, the current value of the option can be calculated.

Risk Neutral Valuation

The risk neutral valuation is implemented by the following spreadsheets and can be found in the “BinomialTrees” subfolder.
  • RiskNeutralValuation\OneStepBinomialTree (European).xls
  • RiskNeutralValuation\OneStepBinomialTree (American).xls
The screenshot for valuing a Call Option using the “RiskNeutralValuation\OneStepBinomialTree (European).xls” spreadsheet is shown below.



In the risk neutral valuation, we set the probabilities in the branches of the Binomial Tree such that expected return of the stock equals the risk free interest rate.

In the screenshot as shown above, we can see that the “Expected Stock Price based on Risk free rate” is calculated first. Equating this value with the Expected Stock Price calculated using the probabilities of the branches allows us to derive the “Risk Neutral Probabilities p”.

With the value “p” and “q”, the expected price of the option is calculated and discounted using the risk free rate. This leads us to the “Call Option Price Now”.

Black Scholes Model

The Black Scholes Model is similar to that of the Binomial Option Pricing. The Binomial Option Pricing assumes two possible values of the stock price at the end of the period (maturity). If we used one year as the end of period and subsequently shorten the period to half a year, the number of possible values at the end of year increases.

By further shortening the period, we get an increasing number of possible values at the end of the year. We will reach a stage where we have a continuous range of possible prices at the end of the year. The Black Scholes Model provides a formula for calculating the value of the option (or portfolio) in the situation above and thus allows us to value options easily.





System Requirements

Microsoft® Windows XP®, Microsoft® Windows Vista®, Windows 7 or Windows 8
Windows Server 2003, 2008, 2012
512 MB RAM
5 MB of Hard Disk space
Excel 2002, 2003, XP, 2007, 2010, 2013

Benefits
  • Unlocked
  • Allows removal of copyright message in the template
  • Allows commercial use within the company
  • Allows customization of the model
  • Binomial Trees Templates
  • Cox, Ross and Rubinstein (CRR) Technique
  • Up (u) and Down (d) Factor Adjustments
  • CRR with Continuous Dividend Yield
  • Binomial Option Pricing Excel Formulas (Up to 1000 steps)
  • No Arbitrage Valuation
  • Risk Neutral Valuation
  • Full source code
PDF Specifications Commercial license

Price

USD30.00 - Purchase

Back to Excel Templates and Add-In.


 

Copyright (c) 2007-2014, ConnectCode Pte Ltd. All Rights Reserved.


Trademark Acknowledgements

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. Avery is the trademark of Avery Dennison Corporation. All other product names are trademarks, registered trademarks, or service marks of their respective owners