Price Stock Options Using Binomial Model

This example uses the binomial model to price a stock option. The binomial model assumes that the probability of each possible price over time follows a binomial distribution. Price values can become either one up or one down over any short time period. Plotting these two values over time is known as building a binomial tree. For details about the binomial model, seePricing and Analyzing Equity Derivatives(Financial Toolbox).

The example organizes and displays the input and output data in aMicrosoft^®Excel^®worksheet. Spreadsheet Link™ functions copy data to a MATLAB^®matrix, calculate prices, and return data to the worksheet.

Open theExliSamp.xlsfile and select theSheet4worksheet. For help finding theExliSamp.xlsfile, seeInstallation.

This worksheet contains these named ranges:

B4:B10namedbindata. Two cells inbindatacontain formulas:
- B7contains=5/12
- B8contains=1/12
B15namedasset_tree.
B23namedvalue_tree.

Spreadsheet Link functions are in column D in cells D5, D8, D11, and D12.

Note

This example requires Financial Toolbox™, Statistics and Machine Learning Toolbox™, and Optimization Toolbox™.

Execute the Spreadsheet Link function that copies the asset data to the MATLAB workspace by double-clicking the cellD5and pressingEnter.
Execute the function that calculates the binomial prices in cellD8.
Copy the price data to the worksheet by executing the functions in cellsD11andD12.
The data in the worksheet updates.

The asset price tree contains these prices:
- Period 1 — The up and down prices
- Period 2 — The up-up, up-down, and down-down prices
- 期3 - up-up-up,向上,当当,道n-down-down prices
- And so on.
The option value tree gives the associated option value for each node in the price tree. The option value is zero for prices significantly above the exercise price. Ignore the zeros that correspond to a zero in the price tree.

You can generate different binomial prices by changing the data in the cell rangeB4:B10and executing the Spreadsheet Link functions again. If you increase the time to maturity in cellB7or change the time increment in cellB8, enlarge the output tree areas as needed.

Spreadsheet Link Documentation

金宝app

Try MATLAB, Simulink, and Other Products

Get trial now