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;

- In cell E2 and F2, perform a merge cell and type in “Put Option Pricing”.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.

## 4 comments:

Since calls and warrants have similar characteristics, have you looked for pricing similarities or arbitrage opportunities?

Hi, do you mean call option and call warrant? or do you mean call and put warrant? I have try to use the same formula that I used for option pricing to do so for warrant as well and it seems to work relatively well so far. I have not look into arbitrage opportunities for warrant or option but I have seen an arbitrage opportunity for stock though, just that I'm not too sure how I can perform it? Have you done any arbitraging before? Perhaps, you can share with us how you do that?

I've never done arbitrage. Many years ago I wrote covered calls against IBM but found commission rates too high. The commissions are lower now and I have been thinking about trying it again.

I thought there might be times when call warrants and call options might diverge in value as I would assume warrants aren't followed as closely.

I would like to read about arbitrage possibilities you have seen with stocks. Maybe we could see a way to profit.

Hi, I'll find out more about arbitraging and share with you. I have never done arbitrage before and I'm not sure if it can really be perform in real life. Hope you can share more information as well. :)

Post a Comment