Spreadsheet Link™ EX

Pricing Stock Options Using the Binomial Model

The Financial Toolbox product provides functions that compute prices, sensitivities, and profits for portfolios of options or other equity derivatives. This example uses the binomial model to price an option. The binomial model assumes that the probability of each possible price over time follows a binomial distribution. That is, prices can move to only two values, one up or one down, over any short time period. Plotting these two values over time is known as building a binomial tree.

This example organizes and displays input and output data using a Microsoft Excel worksheet. Spreadsheet Link EX functions copy data to a MATLAB matrix, calculate the prices, and return data to the worksheet.

Note   This example requires the Financial Toolbox software.

1. Click the Sheet4 tab on ExliSamp.xls to open the worksheet for this example.

   

   The worksheet contains three named ranges:

   • B4:B10 named bindata. Two cells in bindata contain formulas:

     • B7 contains =5/12

     • B8 contains =1/12

   • B15 named asset_tree.

   • B23 named value_tree.

2. Make D5 the active cell. Press F2; then press Enter to execute the Spreadsheet Link EX function that copies the asset data to the MATLAB workspace.

3. Move to D8 and execute the function that computes the binomial prices.

4. Execute the functions in D11 and D12 to copy the price data to the Excel worksheet.

   The worksheet looks as follows.

   

   Read the asset price tree as follows:

   • Period 1 shows the up and down prices.

   • Period 2 shows the up-up, up-down, and down-down prices.

   • Period 3 shows the up-up-up, up-up, down-down, and down-down-down prices.

   • And so on.

   Ignore the zeros. 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.

5. Try changing the data in B4:B10 and reexecuting the Spreadsheet Link EX functions.

   Note   If you increase the time to maturity (B7) or change the time increment (B8), you may need to enlarge the output tree areas.

6. When you finish the example, close the figure window.

Interpolating Data

Calculating and Plotting the Efficient Frontier of Financial Portfolios