Greeks Computation for Put Option

The day before yesterday, I posted the computation of the Greeks for call option. In today’s posting, I will continue to discuss how to do the computation of the Greeks for put option (I have purposely waited for two days to do this posting so we can see how the formulas work for us). I have also tried the formulas for the computation of Greeks for put option and compared the results to those on OptionXpress. Well, once again I cannot really say that these formulas gave very good results but they are close enough like in the case of the call option.

I am going to put down the steps for computing the Greeks for put option. You can simply follow through the steps and try out on your own if you are interested. I am going to do the Greeks for put options on the same worksheet that I used yesterday and I am going to list down the step of what you should key in each cell. If you follow exactly the cell reference I am using (which once again, I seriously encourage you to do so if you wish to try out), you should be able to just copy the formulas I have and paste them correctly into the cell reference to get the results. I am also going to include the formulas for computing the put option pricing here as well together with the computation of the Greeks.

Open the same Excel worksheet that we used yesterday, type in the following data;

1. In cell E2 and F2, perform a merge cell and type in “Put Option Pricing”.
2. In cell E3, type in “Stock Symbol”. Again, I am going to use Agilent Technologies as an example; hence I am going to put the symbol “A” in cell F3.
3. In cell E4, type in “Link”. Copy and paste this formula =IF(ISBLANK(F3),"",HYPERLINK("https://www.optionsxpress.com.sg/quote_detail.asp?symbol="&UPPER(F3)&"&SessionID=0",F3)) in cell F4. Hence cell F4 will update every time to provide you with the hyperlink to the stock based on the stock symbol you input in cell F3. You can click on the hyperlink to get the stock information for Agilent Technologies in this case.
4. In cell E5, type in “Stock Option Chains”. Copy and paste this formula =IF(ISBLANK(F3),"",HYPERLINK("https://www.optionsxpress.com.sg/quote_option_chain.asp?SessionID=&symbol="&UPPER(F3)&"&Page=V&lstMarket=0&Range=ALL&AdjNonStdOptions=OFF&lstMonths=13",UPPER(F3)&"'s Option Chain")) in cell F5. Hence cell F5 will update every time to provide you with the hyperlink to the stock option chain based on the stock symbol you input in cell F3. You can click on the hyperlink to get the stock option chain information for Agilent Technologies in this case.
5. In cell E6, type in “Option Symbol”. Type in “AQF” in cell C6. I am going to use the May 08 put option with strike price of USD$30.00 for my illustration purpose.
6. In cell E7, type in “Current Stock Value”. If you have click on the hyperlink in cell F4, you should be able to get the last traded stock price for Agilent Technologies. At this point of writing, the last traded price was USD$29.67. Type in 29.67 (without the dollar sign symbol, you can format it later) in cell C7.
7. In cell E8, type in “Implied Volatility”. If you have click on the hyperlink in cell F5, you should be able to get the option chain for Agilent Technologies. You should be able to find the implied volatility for AQF. At this point of writing, the implied volatility for AQF is 38.1%. Type in 38.1% (including the percentage symbol) in cell F8.
8. In cell E9, copy and paste the following formula: =HYPERLINK("http://cdrates.bankaholic.com/","6-month CD rate (annualized)"). This should provide you with the hyperlink to get the 6-month CD rate (annualized). At this point of writing, due to the recent Fed rate cut, the 6-month CD rate (annualized) is 4.05%. Type in 4.05% (including the percentage symbol) in cell F9.
9. In cell E10, type in “Dividend Payout per Share”. Using the same hyperlink from cell F4, you will realize that Agilent Technologies does not pay out dividend. You should see under the Dividend heading on the website with n/a. Agilent Technologies does not pay out dividend but instead they do stock repurchase from open market. Hence, type in 0 in cell F10 in this case.
10. In cell E11, type in “Days to expiration”. Using the same hyperlink from cell F5 which provides you the link to the option chain for Agilent Technologies, the May 08 put option has another 60 days to expiration. Type in 60 in cell C11.
11. In cell E12, type in “Strike Price”. Again, using the same hyperlink from cell F5, the strike for AQF is USD$30.00. Type in 30 (without the dollar sign symbol, you can format it later) in cell F12.
12. In cell E13, type in “Put Option Price (Approximate)”. Copy and paste the formula =IF(F7<>0,-F7*EXP(-F10/F7*F11/365)*NORMSDIST(-SUM(LN(F7*EXP(-F10/F7*F11/365)/F12),SUM(F9,POWER(F8,2)/2)*F11/365)/(IF(F8=0,0.00000000001,F8)*POWER(F11/365,0.5)))+F12*EXP(-F9*F11/365)*NORMSDIST(-SUM(LN(F7*EXP(-F10/F7*F11/365)/F12),SUM(F9,POWER(F8,2)/2)*F11/365)/(IF(F8=0,0.00000000001,F8)*POWER(F11/365,0.5))+F8*POWER(F11/365,0.5)),0) in cell F13. You should get a value of USD$1.897. This is the theoretical value for the put option for AQF. At point of writing the bid-ask prices for AQF are USD$1.92 and USD$1.96 respectively. The last traded price was USD$1.85.
13. I now move on to do the computation for this put option Greeks. The formula I am going to show may appear very complicated. The good thing is, you can just copy and paste them to your cell reference. I have done the tough portion for you. In cell E15, type in “Delta”. Copy and paste the following formula =NORMSDIST((LOG(F7/F12)+F11/365*(F9+0.5*F8^2))/(F8*SQRT(F11/365)))-1 in cell F15. You should get a value of -0.464. Using the link from step four, navigate to the top of the website and change the "Type" to "Pricer" and "Expiration" to "May 08" and click the "View Chain" button to get the Greeks for AQF. You need to select the “Puts” radio button too. Change the various values on the website and click on calculate. For example, in the “Current Imp Vol”, you should change to 38.1%. In the “Days Until Exp”, you should change to 60. Lastly, in the “Int Rate”, you should change the value to 4.05%. Click on the calculate button and you should get the Greeks value for all the May 08 put option updated. Leave this page as it is as we will be comparing the remaining values later. Note that the delta is -0.487 and the “Theo Value” (which is the theoretical value for the call option price) is 1.919.
14. Let’s move on to do the computation for the remaining Greeks. In cell E16, type in “Gamma”. Copy and paste the following formula =EXP(-((LOG(F7/F12)+F11/365*(F9+0.5*F8^2))/(F8*SQRT(F11/365)))^2/2)/SQRT(2*PI())/F7/F8/SQRT(F11/365) in cell F16. You should get a value of 0.087. The Gamma value on the website from step 13 is 0.091.
15. In cell E17, type in “Vega”. Copy and paste the following formula =F7*EXP(-((LOG(F7/F12)+F11/365*(F9+0.5*F8^2))/(F8*SQRT(F11/365)))^2/2)/SQRT(2*PI())*SQRT(F11/365)/100 in cell F17. You should get a value of 0.048. Again the Vega value on the website from step 13 is 0.048.
16. In cell E18, type in “Theta”. Copy and paste the following formula =(-F7*EXP(-((LOG(F7/F12)+F11/365*(F9+0.5*F8^2))/(F8*SQRT(F11/365)))^2/2)/SQRT(2*PI())*F8/2/SQRT(F11/365)+F9*F12*EXP(-F9*F11/365)*NORMSDIST(F8*SQRT(F11/365)-((LOG(F7/F12)+F11/365*(F9+0.5*F8^2))/(F8*SQRT(F11/365)))))/365 in cell F18. You should get a value of -0.014. The Theta value on the website from step 13 is -0.014.
17. Lastly, let compute the “Rho”. In cell E19, type in “Rho”. Copy and paste the following formula =-F11/365*F12*EXP(-F9*F11/365)*NORMSDIST(F8*SQRT(F11/365)-((LOG(F7/F12)+F11/365*(F9+0.5*F8^2))/(F8*SQRT(F11/365))))/100 in cell F19. You should get a value of -0.026. The Rho value on the website from step 13 is -0.027.

Once again I really hope you all enjoy this as much as I do. I hope this modeling can help you in better choosing your option in future.