- (PDF) Using Binomial Decision Trees to Solve Real-Option
- Creating Binomial Trees in Excel - Macroption
- Binomial Option Pricing Model Definition
Using this formula, we can calculate option prices in all nodes going right to left from expiration to the first node of the tree – which is the current option price, the ultimate output.
(PDF) Using Binomial Decision Trees to Solve Real-Option
Remember from the previous part : While underlying price tree is calculated from left to right, option price tree is calculated backwards – from right to left, or from payoffs at expiration to current option price.
Creating Binomial Trees in Excel - Macroption
Notice how the nodes around the (vertical) middle of the tree have many possible paths coming in, while the nodes on the edges only have a single path (all ups or all downs). This reflects reality – it is more likely for price to stay the same or move only a little than to move by an extremely large amount.
Binomial Option Pricing Model Definition
There are only two possible paths from this cell to the last step – either underlying price goes up and option price (payoff at expiration) will be (cell L68), or underlying price goes down and option price will be (cell L69).
The binomial_option function runs the three functions that we described which are q_prob , build_stock_tree and value_binomial_option , and returns a list with the values of q , the stock tree , the option tree , and the option price .
For instance, at each step the price can either increase by % or decrease by %. These exact move sizes are calculated from the inputs , such as interest rate and volatility.
Payoff at expiration is different for calls and puts. If our CallPut cell contains the value 6, the option is a call, otherwise a put. Call payoff is underlying price at expiration (cell L9) minus strike put payoff is strike minus underlying price. If these differences are negative (option expires out of the money), payoff is zero, which is done with the MAX function. More explanation here.
In the first part we have prepared and named our input cells. In the second part we have explained how binomial trees work. In this part we will create underlying price tree and option price tree in our spreadsheet.
If we have a stock price of USD 85, with a volatility of and 7 periods we can build a tree with the function build_stock_tree and simulate the stock price. The results are the following: