You can also use Excel and the calculations above (with some modifications and improvements) to model behaviour of individual option Greeks and option prices in different market situations (changes in the Black-Scholes model parameters). Make sure to put the minus sign to the beginning: =-B44*G44*Q44*P44/100 More about Option Greeks in Excel I calculate put rho in cell AF44, again as product of 4 other cells, divided by 100. It is simply a product of two parameters (strike price and time to expiration) and cells that I have already calculated in previous steps: =B44*G44*Q44*O44/100 In the calculator example I calculate call rho in cell Z44. There are two more minus signs in the put rho formula. Rho is again different for calls and puts. In the calculator example I calculate vega in cell Y44: =EXP(-1*POWER(K44,2)/2)/SQRT(2*PI())*S44*A44*SQRT(G44)/100 Rho in Excel You can again see the familiar term at the end. The formula for vega is the same for calls and puts: You can again find the explanation of all the individual cells in the first part or see all these Excel calculations directly in the calculator. If you want to keep it simple, you can replace the whole last line of the formula with a fixed number, such as 365. Cells D3 and D4 in the sheet Time Units contain the number of calendar and trading days per year. Cell C20 in the calculator contains a combo where users select calendar days or trading days. The last line of the formula in the screenshot above is the T. The whole formula for call theta in our example is in cell X44. Based on your selection, the interpretation of theta will then be either option price change in one calendar day or option price change in one trading day. You can choose either calendar days (T=365 or 365.25) or trading days (T=252 or something similar, depending on where you trade).
One exception is the T at the beginning of the formulas. Theta is very small for many options, which makes it often hard to detect a possible error in your calculations.Īlthough it looks complicated, all the symbols and terms in the formulas should be already familiar from the calculations of option prices and delta and gamma above. It is different for calls and puts, but the differences are again just a few minus signs here and there and you must be very careful. Theta has the longest formulas of all the five most common option Greeks. The whole formula for gamma (same for calls and puts) is: =EXP(-1*POWER(K44,2)/2)/SQRT(2*PI())*S44/(A44*J44) Theta in Excel In the example from the Black-Scholes Calculator I use the first formula. These two formulas must return the same result. Don't forget the minus sign before K44: =NORM.DIST(-K44,0,1,FALSE) The only difference from the first part is that the last parameter (cumulative) is now FALSE. where K44 is the cell where you have calculated d1 (see first part).Īlternatively, you can use the NORM.DIST Excel function, which I have also explained in the first part. In Excel the formula looks like this: =EXP(-1*POWER(K44,2)/2)/SQRT(2*PI()) It is the standard normal probability density function for -d1. You will find this term in the calculation of theta and vega too. Notice especially the second part of the formula: It is slightly more complicated than the delta formulas above: The formula for gamma is the same for calls and puts. Just add minus one and don't forget the brackets: =S44*(M44-1) Gamma in Excel The calculation of put delta is almost the same, using the same cells.
I calculate call delta in cell V44, continuing in the example from the first part, where I have already calculated the two individual terms in cells M44 and S44: =M44*S44 The formulas for delta are relatively simple and so is the calculation in Excel. Delta in Excelĭelta is different for call and put options. Here you can see how everything works together in Excel in the Black-Scholes Calculator. Here you can find detailed explanations of all the Black-Scholes formulas. See the first part for details on parameters and Excel formulas for d1, d2, call price, and put price. I will continue in the example from the first part to demonstrate the exact Excel formulas. Calculating Black-Scholes Greeks in Excel
0 kommentar(er)
