Greeks Computation for Call Option

The beginning of March 2008 is a wonderful day for my family. On 1st March, my nephew had finally arrived to this world. This is really a happy occasion for my whole family. The first week of March was also my last two lessons for the Wealth Academy Trader tutorial. Unfortunately, I fell sick and I missed my fourth lesson which Conrad taught about his 5DPEG. I heard from my course mates that that the lesson was one of the most interesting of all. I am now waiting anxiously for my makeup lesson in April.

Nevertheless, I did attend the last lesson and Lawrence taught about option trading. I have some basic knowledge about option trading and its Greeks. The thing that I am not too sure is how do I make use of the Greeks? I have been waiting for this lesson to learn on the interpretation of the Greeks and most importantly, their applications. Lawrence illustrated the usage of the Greeks with examples and this has really intrigued me. My first thought in my head was then how can I compute the Greeks myself and how can I apply them to warrant trading? The next thing I was asking myself is the relationship between historical and implied volatility. I have figured out how I can compute the historical and implied volatility but I do not know if there is a relationship between them?

I actually went on to do my own research on the computation of the Greeks and the relationship between historical and implied volatility. Well, my hard work did pay off and I manage to find the ways to compute the Greeks but unfortunately, the relationship between historical and implied volatility is not so straight forward and it required the understanding of regression model to understand how the relationship is being model. Furthermore, I believe the parameters to the model have to be tuned and changed accordingly as time goes by and whenever the underlying of the model is changed. As such, I decided to post my finding of the computation of the Greeks which can be easily done using Microsoft Excel.

I have tried the formulas for the computation of Greeks and compared the results to those on OptionXpress. Well, I cannot really said that these formulas gave very good results as compared with my previous posting of option pricing using the Black Scholes formula but at times they gave very close estimates. I have been trying to figure out how can I use these same formulas to compute the Greeks for warrant trading? This is because information on warrant Greeks is not so easily available as compared with option. Sad to say, I did not succeed. My original intention was to blog on how we can compute the Greeks for warrant? This also explains why I have not been blogging for so long. Hence I decided that I should post my finding on the computation of the Greeks for option and perhaps some of my readers can go ahead and figure out how to do so for warrant and it would be great if he or she can share the finding with us.

I am going to put down the steps for computing the Greeks for option. You can simply follow through the steps and try out on your own if you are interested. I am going to do both the Greeks for call and put options on the same worksheet 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 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 option pricing here together with the computation of the Greeks.

I will start doing with the Call option first and later for the Put option in another posting. Open an Excel workbook and on one of the worksheets, type in the following data;

1. In cell B2 and C2, perform a merge cell and type in “Call Option Pricing”.
2. In cell B3, type in “Stock Symbol”. I am going to use Agilent Technologies as an example; hence I am going to put the symbol “A” in cell C3.
3. In cell B4, type in “Link”. Copy and paste this formula =IF(ISBLANK(C3),"",HYPERLINK("https://www.optionsxpress.com.sg/quote_detail.asp?symbol="&UPPER(C3)&"&SessionID=0",C3)) in cell C4. Hence cell C4 will update every time to provide you with the hyperlink to the stock based on the stock symbol you input in cell C3. You can click on the hyperlink to get the stock information for Agilent Technologies in this case.
4. In cell B5, type in “Stock Option Chains”. Copy and paste this formula =IF(ISBLANK(C3),"",HYPERLINK("https://www.optionsxpress.com.sg/quote_option_chain.asp?SessionID=&symbol="&UPPER(C3)&"&Page=V&lstMarket=0&Range=ALL&AdjNonStdOptions=OFF&lstMonths=13",UPPER(C3)&"'s Option Chain")) in cell C5. Hence cell C5 will update every time to provide you with the hyperlink to the stock option chain based on the stock symbol you input in cell C3. You can click on the hyperlink to get the stock option chain information for Agilent Technologies in this case.
5. In cell B6, type in “Option Symbol”. Type in “AEF” in cell C6. I am going to use the May 08 call option with strike price of USD$30.00 for my illustration purpose.
6. In cell B7, type in “Current Stock Value”. If you have click on the hyperlink in cell C4, 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.64. Type in 29.64 (without the dollar sign symbol, you can format it later) in cell C7.
7. In cell B8, type in “Implied Volatility”. If you have click on the hyperlink in cell C5, you should be able to get the option chain for Agilent Technologies. You should be able to find the implied volatility for AEF. At this point of writing, the implied volatility for AEF is 36.9%. Type in 36.9% (including the percentage symbol) in cell C8.
8. In cell B9, 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 C9.
9. In cell B10, type in “Dividend Payout per Share”. Using the same hyperlink from cell C4, 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 C10 in this case.
10. In cell B11, type in “Days to expiration”. Using the same hyperlink from cell C5 which provides you the link to the option chain for Agilent Technologies, the May 08 call option has another 62 days to expiration. Type in 62 in cell C11.
11. In cell B12, type in “Strike Price”. Again, using the same hyperlink from cell C5, the strike for AEF is USD$30.00. Type in 30 (without the dollar sign symbol, you can format it later) in cell C12.
12. In cell B13, type in “Call Option Price (Approximate)”. Copy and paste the formula =IF(C7<>0,C7*EXP(-C10/C7*C11/365)*NORMSDIST(SUM(LN(C7*EXP(-C10/C7*C11/365)/C12),SUM(C9,POWER(C8,2)/2)*C11/365)/(IF(C8=0,0.00000000001,C8)*POWER(C11/365,0.5)))-C12*EXP(-C9*C11/365)*NORMSDIST(SUM(LN(C7*EXP(-C10/C7*C11/365)/C12),SUM(C9,POWER(C8,2)/2)*C11/365)/(IF(C8=0,0.00000000001,C8)*POWER(C11/365,0.5))-C8*POWER(C11/365,0.5)),0) in cell C13. You should get a value of USD$1.725. This is the theoretical value for the call option for AEF. At point of writing the bid-ask prices for AEF are USD$1.64 and USD$1.77 respectively. The last traded price was USD$1.73.
13. I now move on to do the computation for this call 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 B15, type in “Delta”. Copy and paste the following formula =NORMSDIST((LOG(C7/C12)+C11/365*(C9+0.5*C8^2))/(C8*SQRT(C11/365))) in cell C15. You should get a value of 0.535. 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 AEF. Change the various values on the website and click on the "Calculate" button. For example, in the “Current Imp Vol”, you should change to 36.9%. In the “Days Until Exp”, you should change to 62. 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 call option updated. Leave this page as it is as we will be comparing the remaining values later. Note that the delta is 0.517 and the “Theo Value” (which is the theoretical value for the call option price) is 1.729.
14. Let’s move on to do the computation for the remaining Greeks. In cell B16, type in “Gamma”. Copy and paste the following formula =EXP(-((LOG(C7/C12)+C11/365*(C9+0.5*C8^2))/(C8*SQRT(C11/365)))^2/2)/SQRT(2*PI())/C7/C8/SQRT(C11/365) in cell C16. You should get a value of 0.089. The Gamma value on the website from step 13 is 0.091.
15. In cell B17, type in “Vega”. Copy and paste the following formula =C7*EXP(-((LOG(C7/C12)+C11/365*(C9+0.5*C8^2))/(C8*SQRT(C11/365)))^2/2)/SQRT(2*PI())*SQRT(C11/365)/100 in cell C17. You should get a value of 0.049. Again the Vega value on the website from step 13 is 0.049.
16. In cell B18, type in “Theta”. Copy and paste the following formula =(-C7*EXP(-((LOG(C7/C12)+C11/365*(C9+0.5*C8^2))/(C8*SQRT(C11/365)))^2/2)/SQRT(2*PI())*C8/2/SQRT(C11/365)-C9*C12*EXP(-C9*C11/365)*NORMSDIST(((LOG(C7/C12)+C11/365*(C9+0.5*C8^2))/(C8*SQRT(C11/365)))-C8*SQRT(C11/365)))/365 in cell C18. You should get a value of -0.016. The Theta value on the website from step 13 is -0.016.
17. Lastly, let compute the “Rho”. In cell B19, type in “Rho”. Copy and paste the following formula =C11/365*C12*EXP(-C9*C11/365)*NORMSDIST(((LOG(C7/C12)+C11/365*(C9+0.5*C8^2))/(C8*SQRT(C11/365)))-C8*SQRT(C11/365))/100 in cell C19. You should get a value of 0.024. The Rho value on the website from step 13 is 0.023.

I hope you all enjoy this as much as I do. Please do not delete away the spreadsheet as I will use the same one to illustrate the computation for the put option in my later post. For those who understand and trade options and warrants, you will appreciate more and how this modeling exercise can help you better in choosing your option.