Financial Analysis and Modeling Using Excel and VBA

Chapter 31. Binomial Option Pricing

In this chapter, we will develop binomial option pricing models in VBA that will parallel the binomial models we developed in Excel in Part Two. It is generally easier to understand and learn to develop binomial trees with Excei. Therefore, if you have not covered the corresponding Excel chapter yet, I recommend that you work through at least the first model there before trying to develop binomial models in VBA. Also, this chapter does not use any additional theory or concepts, but you have to understand the theory and concepts that I discussed in the earlier chapter.

The models in this chapter will demonstrate that one of the key advantages of developing them in VBA is that it is easier to modify them and use them with any number of steps to get the kind of accuracy necessary for "real world" applications.

Modeling Examples

MODEL 1: EUROPEAN OPTIONS ON STOCKS WITH KNOWN DIVIDEND YIELD

The Problem

Develop a model to estimate the price of European options (both puts and calls) on stocks with known dividend yields using a 9-step Cox, Ross, and Rubinstein (CRR) binomial tree. (The worksheet for this model is shown in Figure 31.1.)

Modeling Strategy

You may find it easier to study the code of this first model of binomial trees before attempting to write it on your own. However, if you can proceed on your own using the following strategy, do so.

Figure 31.1. Model ...