Spreadsheet option pricing
It is your job to decide how high volatility you expect and what number to enter — neither the Black-Scholes model, nor this page will tell you how high volatility to expect with your particular option.
You can interpolate the yield curve to get the interest rate for your exact time to expiration. If you are pricing an option on securities other than stocks, you may enter the second country interest rate for FX options or convenience yield for commodities here. Alternatively, you may want to measure time in trading days rather than calendar days. Furthermore, you can also be more precise and measure time to expiration to hours or even minutes. I will illustrate the calculations on the example below.
You can of course start in row 1 or arrange your calculations in a column. When you have the cells with parameters ready, the next step is to calculate d1 and d2, because these terms then enter all the calculations of call and put option prices and Greeks.
The formulas for d1 and d2 are:. All the operations in these formulas are relatively simple mathematics. The hardest on the d1 formula is making sure you put the brackets in the right places.
This is why you may want to calculate individual parts of the formula in separate cells, as I do in the example below:. First I calculate the natural logarithm of the ratio of underlying price and strike price in cell H Then I calculate the denominator of the d1 formula in cell J It is useful to calculate it separately like this, because this term will also enter the formula for d The two formulas are very similar.
There are 4 terms in each formula. I will again calculate them in separate cells first and then combine them in the final call and put formulas. Potentially unfamiliar parts of the formulas are the N d1 , N d2 , N -d2 , and N -d1 terms. N x denotes the standard normal cumulative distribution function — for example, N d1 is the standard normal cumulative distribution function for the d1 that you have calculated in the previous step.
DIST function, which has 4 parameters:. There is also the NORM. DIST, which provides greater flexibility. The exponents e-qt and e-rt terms are calculated using the EXP Excel function with -qt or -rt as parameter. Here you can continue to the second part, which explains the formulas for delta, gamma, theta, vega, and rho in Excel:. Continue to Option Greeks Excel Formulas. Or you can see how all the Excel calculations work together in the Black-Scholes Calculator.
If you don't agree with any part of this Agreement, please leave the website now. All information is for educational purposes only and may be inaccurate, incomplete, outdated or plain wrong. Choosing Payoff Combined will only graph the payoff diagram on the combined strategy of positions A through D. Similar graph types are available on the "Profits" worksheet.
There are four possible positions A through D and the position color will correspond to the color of the curve on the graph. For example, the first instrument A is blue, so its payoff diagram on the graph will be blue. For "Profits" worksheet, the payoff diagram curves on the graph are thicker than the profit diagram curves, but are the same color. Select each position To graph the payoff or profit diagrams, you must specify a position in at least one instrument. Choose the instrument you want from one of the four pop-up menus under the "Instrument" column.
For options, the Short position is when you "write" an option. For bonds, the Short position is equivalent to borrowing money i. The diagram represented on the graph shows the payoff or profit at expiration or time of exercise for the options contracts. Each position is for one share of stock, an option on one share of stock, or one bond with the given face value.
So, if you want a position of two of the same call options, for example, then you must specify identical call options for two separate positions e. To cancel a position, set the pop-up menu for the "Instrument" to blank the first pop-up menu choice. For example, setting all four positions A-D to a blank instrument gives you a blank graph.
The option premium for a Call or Put position will appear when the position is selected. The present value or price of the Bond will appear when the position is selected. All prices are rounded to the nearest 10 cents.