- Growth in Structured Securities
- Growing Emphasis on Low Volatility and Dividends
- Criticisms of Structured Securities
- Demand for Quantitative Skills
- Direction of Quantitative Finance
- When I Realized It Might Be Easier
- Try Again
- The Spreadsheet
- Visualizing the Result
- What It Means and Why It Works: A Nontechnical Overview
- It Doesn't Get Too Complicated
- An Integrated View of Risk Management
This chapter is from the book
This chapter is from the book
This chapter is from the book
The Spreadsheet
Because the adjustment (1/2 variance) has important implications, I want to show you what the spreadsheet looked like before the adjustment. In the next chapter, I will correct the mistake and walk through the spreadsheet components step by step.
To get a reference point, I priced an option with the Black-Scholes formula. I assumed a one-year term, 30% volatility, and $100 for the current stock price and the option strike. I also assumed 0% interest and no dividends. The Black-Scholes price was $11.92.
Then I started building the spreadsheet.
At the top, I entered the pricing assumptions. Then I started filling in the body of the sheet, following the rule that “continuously compounded stock returns are normally distributed.”
When dealing with a normal distribution, the usual place to start is with the standard normal distribution. This is just a special case in which the mean or average value is 0 and the standard deviation is 1. Excel has a built-in function, so I filled in the first two columns with an approximate version that fit on two pages. (I divided it into 81 points, ranging from –4 standard deviation to +4 standard deviations in 0.1 increments. To handle the tails, I put everything outside 4 standard deviations in the two endpoints.) I knew it would not be exact, but it would work for a first try.
Next, I used a common rule of statistics to transform the standard normal distribution into a normal distribution with a standard deviation of 30% and a mean of 0. I remembered that “continuous compounding” meant using the EXP function. That gave me the stock prices. Knowing the stock price makes it easy to calculate the option payoff. The option payoff is just the difference between the stock price and the strike price, not less than zero.
The only thing left to do was weight each option payoff by its probability and add the numbers. The answer was $14.63, shown in Cell F95. I am intentionally showing you the wrong version so that I can focus on the correction in the next chapter. What is important here is the basic format.
When I was finished, the spreadsheet looked like Figures 1a and 1b.
This spreadsheet describes a simple world. In this world, stock returns, stock prices, and option payoffs are linked to each other, and each of them can be only one of 81 different values.
Columns A and B are constants representing the approximated standard normal distribution. Here are the other column formulas:
- Column C = Column A × 0.30
- Column D = EXP[Column C]
- Column E = MAX[0, Column D − Strike price]
- Column F = Column B × Column E
The option price is the total of Column F.
)
Figure 1.1a. Option pricing, first attempt
)
Figure 1.1b. Option pricing, first attempt (Continued)
As an example in reading the spreadsheet, look at Row 53. It is one of the 81 possible outcomes. In this outcome, the stock return is 0%, the stock price is $100, and the option payoff is $0. The probability that this particular outcome will occur is 3.98776%.
Similarly, in Row 63 at the one standard deviation point, the stock return is 30%, the corresponding stock price is $134.99 (the fact this differs from $130 is explained later), and the option payoff (the difference between the stock price and the option strike price of $100) is $34.99. The probability that this particular outcome will occur is 2.41971%.
Notice that the only positive values for the option payoff are in Rows 54–93. These 40 outcomes are the only numbers factored into the option price. The last column shows the weighted values of the option payoffs.
Looking at Column F, the value of the option is concentrated between 0 and 3 standard deviations. The highest contributions occur at around 1 standard deviation, with a weighted value of $0.85. In the tail of the distribution, the payoffs are very high but the probabilities are very low. For instance, even though the payoff goes as high as $232.01 in Row 93, the effect on the value of the option is only 1 cent. The probability at this point is so low that a high payoff has almost no effect.