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.
This example is included in the Spreadsheet Link EX product. To run it:
Start Excel, Spreadsheet Link EX, and MATLAB sessions.
Navigate to the folder matlabroot\toolbox\exlink\.
Open the file ExliSamp.xls
Execute the example as needed.
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.
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.
Move to D8 and execute the function that computes the binomial prices.
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.
Try changing the data in B4:B10, and then executing the Spreadsheet Link EX functions again.
When you finish the example, close the figure window.