Please answer the following questions with step by step work with topics about capital markets and finance.
Black-Scholes Value Calculator Cherkes Spring 2014
1
Option Value Calculator “OptionValue” is an Excel spreadsheet program that can be used to calculate the Black- Scholes value (or price) of an American or European call option (and a European put option) written on a non-dividend-paying stock. Because price and value are the same if there is no arbitrage, we can use these two terms synonymously when referring to the Black-Scholes model. The Excel program can also be used to calculate the implied volatility of a stock given the actual market price of a call option based on that stock. The actual market price may be quite different from the Black-Scholes value (or price). Calculation of the Black-Scholes Price: The Black-Scholes formula requires the specification of five parameters: the current stock price, the strike (or exercise) price of the call option, the risk-free (riskless) rate of interest (for the period covered by the option), the volatility of the stock, and the time to expiration of the call option. When the spreadsheet is called, the configuration seen in Figure 1 appears on the screen. Eight cells are highlighted: these are cells in which the user can enter values required by the program. The first seven are used for entering the parameters needed to calculate the Black-Scholes price (value). The eighth is used for calculating the implied volatility.
OPTION PRICE CALCULATOR
CURRENT PRICE BLACK SCHOLES PRICE (a) 6.335959 OF STOCK 100.000
STOCK POSITION (b) 39.600461 EX PRICE 115.000 BORROWING (c) 33.264502
LEVERAGE RATIO (b/a) (d) 6.250113 RISKLESS RATE 0.050
VOLATILITY 0.300 C_S (DELTA) 0.396005 C_SS (GAMMA) 0.014831
TIME TO EXP 0.750 DERIVATIVES C_Sigma (VEGA) 33.368792 -C_(T-t) (THETA) 8.336983
CURRENT DATE 01/01/04 C_r (RHO) 24.948376 C_X -0.289257
EXP DATE 09/30/04 IMPLIED VOLATILITY 0.38606
MARKET PRICE* 9.250 TIME TO EXPIRATION 0.75000
VALUE OF EUROPEAN PUT: *OPTIONAL SAME EX PRICE AND TIME TO EXP 17.103317
Figure 1
Black-Scholes Value Calculator Cherkes Spring 2014
2
We will consider a specific example. Suppose ABC shares are currently trading at 100 and that a call option is written on this stock. The call has an exercise price of 115 and expires in 9 months. To calculate the Black-Scholes price, enter the number 100 in the first highlighted cell, i.e. the cell next to the label calling for the current stock price. To enter a number in a cell, move the cursor using the arrow keys until the cursor is at the desired cell, then type the number and push the <ENTER> key. A warning will be given if one attempts to enter a number in one of the cells that is not highlighted. As soon as a number is entered, the program recalculates all the calculated values displayed on the worksheet. Ignore this until all numbers have been entered. Next move the cursor to the cell for the exercise price (the one labeled EX PRICE) and enter 115. In the cell labeled RISKLESS RATE, the riskless rate of interest must be entered. This too must be expressed in yearly terms and is taken to be the continuously compounded rate one would earn on a riskless investment. In this example we assume that the riskless rate over the nine month period is 5% continuously compounded on an annual basis. This should be entered in decimal form, i.e., 0.050. The next number to enter is the value that is most difficult to determine: the volatility of the stock. Here we assume that the annualized volatility is 30%, entering this as 0.300 in the cell marked VOLATILITY. The final input for calculating the Black-Scholes price is the time to expiration. This can be entered in either of two ways. First one can put the time to expiration in directly in the cell labeled TIME TO EXP. Since 9 months is 0.75 years, one enters 0.750 in the cell labeled TIME TO EXP. One can also enter the time to expiration by entering the current and expiration dates. To use this approach, delete any entry in the cell marked TIME TO EXP. This tells the program to use the dates to calculate the time to expiration. (If there is a numerical entry in TIME TO EXP, the program will use this as the time to expiration and ignore the current and expiration dates). Then enter the current date in the cell marked CURRENT DATE and the expiration date in the cell marked EXP DATE. The format for both of these is MONTH/DAY/YEAR. Figure 2 shows how this method is used. When the current and expiration dates are used to give the time to expiration, the calculated time to expiration (in years) appears as the second to last displayed number in the second column. In this case, a little over 0.75 years passes between Jan 1, 2004 and Sept 30, 2004.
After all five numbers have been entered, the Black-Scholes price appears at the top of the output column. The price for our example (in figure 2) is $6.32. The next number appearing in this column is the value of the stock position in the hedge. This is approximately $39.56. This says that to mimic the option we must hold $39.56 worth of the stock. The next number gives the amount we borrow in the hedge, in this case $33.24. The difference between these two numbers is the value of the call. The leverage ratio gives the ratio of the value of the stock in the hedge to the value of the call.
Black-Scholes Value Calculator Cherkes Spring 2014
3
OPTION PRICE CALCULATOR
CURRENT PRICE BLACK SCHOLES PRICE (a) 6.318822 OF STOCK 100.000
STOCK POSITION (b) 39.557530 EX PRICE 115.000 BORROWING (c) 33.238707
LEVERAGE RATIO (b/a) (d) 6.260269 RISKLESS RATE 0.050
VOLATILITY 0.300 C_S (DELTA) 0.395575 C_SS (GAMMA) 0.014847
TIME TO EXP DERIVATIVES C_Sigma (VEGA) 33.313238 -C_(T-t) (THETA) 8.342887
CURRENT DATE 01/01/04 C_r (RHO) 24.860732 C_X -0.289032
EXP DATE 09/30/04 IMPLIED VOLATILITY 0.38669
MARKET PRICE* 9.250 TIME TO EXPIRATION 0.74795
VALUE OF EUROPEAN PUT: *OPTIONAL SAME EX PRICE AND TIME TO EXP 17.097561
Figure 2
The next six numbers are derivatives of the Black-Scholes price. Each shows how sensitive the option price is to small changes in one of the five parameters. The first is the most important: it is called the Delta of the call and is equal to the number of shares purchased in the hedge. In this case, 0.40 shares are purchased. This derivative tells us that for a very small increase in the stock price equal to k, the call will increase by approximately 0.40k. The next number, usually called Gamma, gives the second derivative of the call price with respect to the stock price. It provides information about the curvature of the function relating the call price to the stock price. It also indicates how much the stock position in our hedge must change when the stock price changes. In our example, we find that if the stock price increases by $1.00, we will need to buy (approximately) 0.015 more shares of stock. The remaining derivatives give information about the sensitivity of the call price to small changes in the other parameters. For instance, the Theta of the call tells us how much the call would decrease for a small decrease in the time until expiration. The last two derivatives are for small changes in the riskless rate and exercise price. The second, for example, tells us that if the exercise price is increased by 1, the value of the call falls by approximately $0.29.
Now refer to the last entry in the output column. This is the value of a European put that has the same exercise price and the same expiration date as the call option. This value is easily determined from the put-call parity relationship after we have the value of the call.
Black-Scholes Value Calculator Cherkes Spring 2014
4
In our example, a European put that has the same exercise price and the same time to expiration is worth $17.09.
By changing the five parameters in the input column, one can see almost instantly how the value of the call changes. For example, one can put in progressively lower values for the current stock price and see that, holding everything else constant, the leverage ratio increases as the stock price decreases. In the same manner, one can increase the value of the riskless rate and see that the call value increases. Calculation of the Implied Volatility: The option price calculator can also be used to determine the level of volatility implied by a given market price for the call. For example, assume that the nine-month call on ABC shares having an exercise price of 115 is selling in the market for 9.25, rather than the 6.32 we calculated in figure 2. What is the level of volatility that justifies this price in the Black/Scholes model? To find this level, we enter 9.25 in the cell for MARKET PRICE (see figure 3). The program then calculates the implied volatility as 39%. Whenever any of the input parameters is changed, the implied volatility is updated. However, an implied volatility can only be calculated if the market price is positive, at least as high as the difference between the stock price and the present value of the exercise price, and not more than the stock price, which can be summarized as: Max(0,S – PV(K)) ≤ Market Price ≤ S. Values that are not within this range give rise to arbitrage opportunities. If a market price outside this range is entered, the program will not calculate an implied volatility but will instead indicate which bound is violated.
Black-Scholes Value Calculator Cherkes Spring 2014
5
OPTION PRICE CALCULATOR
CURRENT PRICE BLACK SCHOLES PRICE (a) 6.318822 OF STOCK 100.000
STOCK POSITION (b) 39.557530 EX PRICE 115.000 BORROWING (c) 33.238707
LEVERAGE RATIO (b/a) (d) 6.260269 RISKLESS RATE 0.050
VOLATILITY 0.300 C_S (DELTA) 0.395575 C_SS (GAMMA) 0.014847
TIME TO EXP DERIVATIVES C_Sigma (VEGA) 33.313238 -C_(T-t) (THETA) 8.342887
CURRENT DATE 01/01/04 C_r (RHO) 24.860732 C_X -0.289032
EXP DATE 09/30/04 IMPLIED VOLATILITY 0.38669
MARKET PRICE* 9.250 TIME TO EXPIRATION 0.74795
VALUE OF EUROPEAN PUT: *OPTIONAL SAME EX PRICE AND TIME TO EXP 17.097561
Figure 3
Note: For some values of the input parameters the calculated values will be too large to be displayed on the screen. You will see a series of #'s in the relevant cells indicating that this is the case. For some other (extreme) values of the input parameters, some of the calculations cannot be performed. For example, if one attempts to value a deep out of the money call whose price is effectively zero, the leverage ratio cannot be calculated. Effectively, one is dividing by zero. In this leverage ratio cell, you will see #DIV/O! indicating that this has occurred. The Excel file is password protected. If you want to unprotect the document, the document password is “B7302” (case-sensitive).