Binomial Tree, Cox Ross and Rubinstein (CRR), No Arbitrage and Risk Free Valuation.

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

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.