Binomial Option Pricing

Help for binomial option pricing in the real options valuation template for Excel

User Guide Navigation: Business Spreadsheets User Guides: Project Planning and Management Multiple Regression Analysis and Forecasting Investment and Business Valuation Real Options Valuation Portfolio Optimization Portfolio Performance Tracking Customer Invoicing Real Options Valuation Help Topics: Option Pricing Fundamentals The Option to Delay a Project The Option to Expand a Project The Option to Abandon a Project Binomial Option Pricing Game Theory Analysis

The Binomial Option pricing tool offers a more advanced application of Real Option Valuation where there exists 'options on options'.  The binomial model is able to evaluate the present value of an unlimited number of branches where at each node the value of the underlying asset or investment can go either up or down. This is useful for evaluating more complex real world situations where a wide range of possible outcomes may exist, and is especially powerful when re-evaluating the value as certain outcomes eventuate.

 

As the estimation of positive and negative outcome values and probabilities can be difficult to estimate, the model facilitates this by providing an approximation to the normal distribution, whereby underlying cash flow volatility is automatically translated into upward and downward outcome results. Moreover, this assumption that underlying asset volatility is distributed normally (as assumed by the Black-Scholes model above), can be relaxed to model scenarios where the possible outcomes (volatility) are skewed either positively or negatively. This flexibility literately enables a limitless number of possible real option valuation scenarios.

 

On clicking the 'Start' button in the 'Menu' sheet; you are taken to the 'Binomial' sheet. Binomial branches can be created or deleted by selecting a dark blue color cell with white text labeled 'Valuation', and clicking the corresponding button at the top left.

 

Creating a Branch

By clicking the Create Branch button, a form is displayed for the inputs to create the binomial option branch. These are:

• Annual risk-free rate. This is used in the valuation to create a replicating portfolio consisting of the underlying asset and the risk-free asset. The equivalent 1-year government bond rate can be used here.
• Annual dividend yield. The dividend yield represents the expected cash flow from the underlying asset. This is presented in the final output as the cash flow forgone if the decision is made not to invest. A negative dividend yield can be entered to reflect a required cash injection to maintain the asset. The annual dividend yield is adjusted for the length of the option's life. If there is no dividend yield, zero should be entered here.
• Period Length and Number of Periods. The length of the option can be expressed in years, semi annually, quarterly, or monthly. The number of periods below this specifies the total length of the options life. Bear in mind that the total option life is limited to 5 years. If a longer time period is required, multiple branches can be created to accommodate.
• Allow early exercise? Checking this box essentially turns the option into an American style option, whereby exercise can be made at any point up until the end of the option's life. This added flexibility increases the value of options created with multiple branches.
• Estimated Asset Value Now. This represents the present value of expected cash flows from the asset or project, but doesn't include costs of undertaking the investment or regular cash inflows or outflows to and from the asset (dividends). This can be obtained directly from traditional discounted cash flow analysis, such as that supplied by the Investment Valuation model.
• Estimated cost of Investment. This represents the present value of investing in the asset or project, not including regular ongoing funding requirements which can be treated as negative dividends in the earlier input parameter. This can also be obtained directly from traditional discounted cash flow analysis.
• Probability parameters. This section defines the upward and downward multipliers which determine the positive and negative outcome values for the option. Desired or estimated values for either one can be entered directly here.  Based the risk neutral probabilities of outcome under the Normal distribution (assuming that the risk-free rate has been entered), the form automatically calculates the outcome probabilities and underlying cash flow volatility in the box below. Note that these multipliers can be altered later in the worksheet to relax the assumption of normally distributed returns and model alternative scenarios.
• Use Volatility Assumptions. Alternatively, if the expected outcomes of the option are unknown, asset or project value volatility can be used and the upward and downward multipliers will be calculated automatically based on the approximation to the normal distribution. The standard deviation (volatility) of present value can be estimated by one of the following methods, in order of preference:

1. If similar projects or investments have been undertaken or made in the past the standard deviation of cash flows resulting from these projects can be used as a proxy for the standard deviation in cash flows for the proposed investment.
2. Probability analysis can be run on simulations of key inputs, such as revenue and cost drivers, market size and market share, to estimate the standard deviation of the resulting present value. While this type of analysis can be accomplished by using sampling analysis in the Analysis ToolPak add-in shipped with Excel, third-party add-ins can facilitate more sophisticated applications.
3. The standard deviation of publicly traded firms in the same business or industry can be used a proxy for the proposed investment.  This is the least preferred method due to the likely diversity of activities undertaken in other firms and resulting differences in variance characteristics. Such industry specific volatility data can be obtained from third party market data providers (such as those recommended at the Business-Spreadsheets.com web site) and entered into the Pre-Defined sheet for future use across models and proposals. It should be noted that the Pre-Defined sheet can also be utilized to store standard deviation data from similar projects undertaken in the past as described in the first method.

 

On clicking the 'Create' button, the binomial branch is created on the worksheet with both upward and downward value outcomes, key inputs and resulting option value in the original valuation box.  All cells highlighted with a light blue color can be manipulated directly here to model alternative distributions and outcomes.  Only the upward multiplier probability can be changed to modify outcome probabilities.  This is because both probabilities must sum to 100%, and the downward probability changes automatically to accommodate. Further binomial branches can now be created in the same way by selecting the 'Valuation' cell on either of the two outcomes. The valuation at the beginning node changes to account for all subsequent branches (outcomes).

 

Deleting a Branch

To delete a branch, you must select the 'Valuation' cell on the node for which the branch was created from, and click the 'Delete Branch' button in the top left corner. Only end branches can be deleted in this way; therefore to delete multiple branches, you must start at the end and work backwards.

Screenshot: Binomial Option Pricing

Back

Next