How to compute the historical volatility of index and equities?

In this post, I’ll divert from the discussion of the “Actual Operation of Warrant Trading” and talk about how various issuers of warrant compute the historical volatility of index and equities. In fact, what I am going to illustrate later can also be applied to the US market to compute the historical volatility of index and equities.

Some of my readers may be wondering why we should ever bother about computing the historical volatility since these events had already happened. I have the same thoughts too until my last WAT gathering when Greekman shared with us the expected move formula and it struck me that why did I not think of that? Let me go through the computation before we move on to make some modification to the formula and see where we can go from there.
I am using the Straits Time Index (STI) as an example here to compute the historical volatility. I have done some screen captures from different issuers to show what the historical volatility of STI is after market closed today.

From the above screen captures, we can see that the historical volatility is around 15.01%. We are not concern about the different terms of warrant chosen as long as they all have the same underlying; their historical volatility should be approximately the same.

The screen capture below is a spreadsheet where I used to compute the historical volatility of STI index. Notice the value I got is quite close to the one shown on the previous screen captures.

Let walk through how each value is being computed. Under the column with the heading showing “Straits Time Index”, the value in each of the cell shows the closing STI index value on that day. Take note that we do not include weekend or any non trading day. For example, we do not include 19th May 2008 as it is Vesak day.

Under the column with the heading showing “Percentage Change”, the value in each cell is computed based on the natural logarithm of the prior day closing index and today closing index. Take for example, the percentage change on 18th June 2008 is computed as follow, Ln(3040.09/3028.24) = 0.39%.

Once we have all the various percentage change calculated for the last 30 days, not including the one on 19th June 2008, since this is a historical volatility. To compute the historical volatility for last 30 days, we first find the standard deviation of the percentage change from 8th May 2008 to 18th June 2008 and then multiply it with the square root of 250 or 252; the number of trading days in a year. That is Stdev(-1.78%, -0.31%, 0.57% ….-0.29%, 0.39%)*Sqrt(250) = 15.02%. The reason why we multiply the standard deviation with the square root of the number of trading days is to annualize the historical volatility.

Based on the theory of statistic, if the sample is 30 and above, we can assume the distribution to be normal. This is why I have chosen 30 days and nothing less. Assuming if the STI index does follow the normal distribution, then there is a 68% of the time the STI index will fall within 2992.66 * (1 ± 15.02% * Sqrt (30/250)), which gives us a range of 2836.95 to 3148.37. We multiply the historical volatility with the Sqrt(30/250) to de-annualize it. For those who attended the last WAT gathering, do you find this formula familiar? What happen if we substitute the 2992.66 with half the current equity share price, the 15.02% with the ATM option implied volatility and the 30 with the days to expiration of the option? Effectively, this gives us an idea of how much the share price will move towards the expiration date.

I certainly hope you enjoy this as much as I do. I shall be posting the “Actual Operation of Warrant Trading (Part 4)” soon.

Actual Operation of Warrant Trading (Part 3)

This is the third part of the Actual Operation of Warrant Trading. In this post, I’ll discuss about the face value of warrant. Some investors prefer warrants with a smaller face value, because they cost less to buy and the tick value is lower, and they are more sensitive to the movement of the underlying price. However, other investors prefer warrants with a bigger face value, on the grounds that, with their higher tick value, one tick will be enough to pay the brokerage commission.

So, is it a better strategy to buy warrants with smaller face value or those with a bigger face value? Lets us find it out by comparing transaction costs and how closely the warrant price will follow the movement of the underlying price.

Let us start with transaction costs. Assume that the bid/ask spread of the warrants is one tick across the board. For a warrant with a face value of no more than S$1.00, say S$0.50, the tick value is S$0.005. This is also the minimum transaction cost for buying and selling the warrant, with the underlying price remaining unchanged. For another warrant with a face value within the range between S$1.00 and S$9.99, say S$5.00, the tick value of the warrant is S$0.01. It seems that the warrant with a bigger face value is more costly. In fact, this is not true. In percentage terms, the transaction cost is actually higher for warrants with a smaller face value. In the case here, the warrant with a smaller face value, S$0.005 / S$0.50 * 100 = 1%, compared with the warrant with a bigger face value, S$0.01 / S$5.00 * 100 = 0.2%. Hence, the trading risk is relatively lower for the latter.

Besides, a warrant with a smaller face value is more likely to follow closely the movement of its underlying. Say, we have two warrants, both with a delta of 0.05 and a conversion ratio of 10:1. For the one with a smaller face value, its tick value is S$0.005. When its underlying goes up by S$0.10, the warrant will in theory, climb by 1 tick (S$0.1 * 0.05 = S$0.005). As for the warrant with a bigger face value, its tick value is S$0.01. When the underlying goes up by S$0.10, the warrant will appear to be not moving at all, as the increase in its price is less than a tick (S$0.005 = ½ tick).

Face value may be more relevant to investors looking for fast money. For ordinary investors, it does not mean much whether it is S$0.005 or S$0.01 a tick. Of course, we always want to buy something at a lower cost if possible. Nevertheless, you are advised not to be too concerned with the tick value, but spend more time studying the terms such as effective gearing, to find out the most suitable warrant for your portfolio.

Actual Operation of Warrant Trading (Part 2)

This post is a continuation from where I stopped back in April. In this post, I will discuss about the bid/ask spread or in option trading world, it is commonly known as the slippage, of warrant trading.

Short term investors will find warrant trading attractive only if at least two conditions are satisfied. Firstly, there must be sufficient liquidity in the market so that warrants can easily change hands. Secondly, the bid/ask spreads must be narrow enough to keep transaction costs low. Since market making was introduced, liquidity is no longer a problem. Besides, as competition gets more and more intense, market makers are also maintaining their bid/ask spreads within a tight range. However, at times some warrants do trade with a rather wide spread. Well, then, how are bid/ask spreads determined?

The first factor to consider is delta. Given a conversion ratio of 1:1, the higher the delta (that is, close to 1 or 100%), the narrower the gap in the bid/ask spread between a warrant and its underlying. Let us say that the bid/ask spread of the underlying is S$0.02. If the warrant has a very high delta, it’s bid/ask spread will be close to S$0.02. However, if the conversion ratio is 10:1, the bid/ask spread of the warrant will be around S$0.002.

Let use an actual example to see if that is the case. The screen capture below shows the bid/ask price of some warrant with delta close to 100% on June 13, 2008. The next screen capture shows the buy and sell price of some counters from SGX on the same date. Noticed in the first screen capture, three out of the four warrants in the screen capture that are very close to expiration have delta close to 100%. This should be the case since the three warrants are deep ITM with around two weeks to expiration. The only warrant with a delta close to 100% and more than two months to expiration is DBS BNP ECW080905. We shall use this as an example.

Based on the second screen capture, the bid/ask spread of DBS is S$19.10 – S$19.06 = S$0.04 and the entitlement ratio is 8. This means we should expect the bid/ask spread of the warrant to be around S$0.04/8 = S$0.005. However, based on the first screen capture, the bid/ask spread for DBS BNP ECW080905 is S$0.535 - S$0.51 = S$0.025. This is 5 times more than what we expected. How about the theoretical bid/ask spread of a warrant? We can compute this by multiplying the delta of the warrant with the tick value of the underlying. Hence, the theoretical bid/ask spread for DBS BNP ECW080905 is 90.22% * S$0.02 / 8 S$0.002. Since the bid/ask spread of the warrant is also subject to the different tick values of different price ranges set by the stock exchange, we will expect the warrant bid/ask spread to be S$0.005 (the minimum bid size for price range up to S$0.99) which is consistent with the expected S$0.005 bid/ask spread we calculated earlier on. Hence we need to find out why is the actual bid/ask spread 5 times more than the theoretical spread? This will lead us to the second factor.

The second factor to consider is the liquidity of the underlying. Once a warrant is issued, the issuer has to make the necessary hedging arrangements. Buying some holdings of the underlying is one of the methods. If the liquidity of the underlying is inadequate, the cost of hedging will be high. Hence, issuers will work out an estimate of the size of the float of the underlying they can buy or sell at the optimal price before setting the bid/ask spread of the warrant.

In the case of DBS BNP ECW080905, the delta is 90.22% and the bid/ask spread of the underlying (DBS) is S$0.04; therefore the warrant bid/ask spread should be around S$0.005. Since the conversion ratio is 8:1, this means for every 8 units of warrant the issuer needs 1 unit of DBS share to hedge its position. The outstanding warrant as on June 13, 2008 was 50, 000, 000. This is shown in the screen capture below.

Assuming this value is accurate, this means that the accumulated overnight positions, held by investors rather than the issuer at the close of trading was 50 million units. This means if the issuer needs 50 000 000/8 = 6.25 million units of DBS shares to hedge its position since the listing of this warrant.

Let’s assume due to the insufficient liquidity in the market, the issuer can only buy 6 million units of DBS shares to hedge its position at optimal price says at its average price of around S$18.80, computed based on the closing price of DBS since beginning of this year. This means the issuer has to pay an extra cost for the remaining 0.25 million units of DBS shares. If the issuer needs to pay the price of S$19.00 to buy those units for hedging, the bid/ask spread of the warrant will widen to (19.00 – 18.80)/0.02 * 0.02 * 90.22% / 8 = S$0.023 or about 5 ticks more.

The above are only assumptions made for the purpose of discussion and may not be necessary be true. Of course, sometimes an issuer may want to maintain a narrow spread for a particular warrant. So if the investors can spend a little time to observe how different issuers deal with the bid/ask spreads of their warrants, it would not be difficult to compare them and choose the most appropriate warrant to trade.

Investment Warrants

I came across this special type of warrant few months ago and I find it was quite an interesting derivative instrument that is very different from the usual plain vanilla warrant.

From what I know now is that the only issuer for Investment Warrant is Macquarie. I have gathered some information from their website and re-posted it here. If you are interested to know more, there is an upcoming free seminar on the 18th June 2008 talking about Investment Warrant. You can register here.

Macquarie’s Investment Warrants allow you to get exposure to shares at a fraction of the price. With Investment Warrants you can:

• gain long term exposure for a fraction of the share price
• limit your capital at risk
• increase your effective dividend yield

Investment Warrants have longer expiry dates, lower holding costs and a lower risk profile than Trading Warrants. They are suitable for both short term and long term investment horizons.

If you recall the first post on Analysis of Warrant Data I posted in early January this year, you will notice the warrant name has an additional “I” to indicate that it is an investment warrant, e.g. COSCOCORP MBL ICW90403. Also note that by its definition, there is only investment call warrant and no such thing as investment put warrant.

What is an Investment Warrant?

An Investment Warrant enables you to buy shares in two payments. You pay a fraction of the share price up front and get exposure to the capital movements in the underlying share and all of the ordinary dividends over the life of the warrant.

Generally, the price of an Investment Warrant will move in line with movements in the underlying share and, because warrants are only a fraction of the price of the underlying share, they tend to move in greater percentages than the share price.

Investment Warrants also give you a payment equivalent to 100% of the ordinary dividends of the underlying shares. This is something that normal derivative instruments do not offer. Hence, in a way Investment Warrants therefore allow you to potentially earn a greater return than you might achieve by owning the share itself (see example below).

Investment Warrants give you:

• the right to buy a share
• at a specific price (called the exercise price)
• on a specific date (called the expiry date)
• the equivalent of 100% of the ordinary dividends throughout the life of the warrant

Investment Warrants are listed on the SGX so you can buy and sell them at any time, just like shares.

At the expiry of the warrant you have the option to either pay the exercise price and take delivery of the shares or simply receive the cash settlement amount (if any).

Benefits of Investment Warrants

• Greater return potential – through the effect of gearing, price movements are magnified
• Longer term exposure – lower holding costs mean Investment Warrants are suitable for both short and long term investments
• No Margin calls - increase your exposure to shares without the risk of margin calls
• Physical settlement – option to exercise and take delivery of the fully paid shares at expiry
• Enhanced Dividend Yield – holders receive the equivalent of 100% of the ordinary dividends of the underlying share for less outlay

Warrants enable investors to spend less up front, diversify their investments, potentially accelerate their growth and meet their investment objectives sooner.

Who would use Investment Warrants?

You might use Investment Warrants if:

• you are a long-term investor looking for a lower risk way to increase your investment returns
• you are a trader with a positive view on an underlying share and you want a moderately geared alternative
• you are an existing shareholder and want to unlock some capital from your portfolio by switching from shares into Investment Warrants

How Investment Warrants work

If you believe DBS shares will rise, you may wish to leverage your view by buying Investment Warrants over DBS shares. A hypothetical example is shown below:

Instead of purchasing the DBS share at $21.00 you can buy the Investment Warrant for only $7.30 to gain exposure to the performance of the DBS shares. During the life of the DBS Investment Warrant, the warrant price will tend to move up and down in line with the DBS share price. Investors may increase or exit their investment at any time by buying or selling the Investment Warrants on the SGX. The investor will also receive the equivalent of 100% of the ordinary dividends paid by DBS throughout this term.

At the expiry, if the investor is still holding the warrant they may either pay the exercise price of $15 and take delivery of the DBS shares or they can choose to receive the cash settlement value (if any).

• The cash settlement at expiry is calculated using the following formula:
  (Share price - Exercise price) x conversion ratio
• For example, if DBS is at $24 at expiry the warrant value would be:
  ($24.00 - $15.00) x 1 = $9.00

How gearing can boost your return

One of the main advantages of warrants is ‘gearing’, meaning a warrant provides the holder with an increased exposure to the underlying share. Therefore, a small percentage change in the price of the share can lead to a large percentage change in the value of the equity warrant.

The added advantage of Macquarie’s Investment Warrants is the increased effective dividend yield. The holder of an Investment Warrant is entitled to a payment equivalent to 100% of the ordinary dividends in the underlying share; however as the warrant price is only a fraction of the share price the effective dividend yield to the holder is increased.

Here is a hypothetical example:

It’s important to remember leverage works in both directions, so a fall in the share price would also cause a greater percentage fall in the value of the warrant. It is also important to be aware that Investment Warrants will expire worthless if the share price is at or below the exercise price at expiry.

Using Investment Warrants to release capital from your portfolio

Investment warrants are a convenient and lower risk alternative to release capital from your portfolio. If you have an existing share holding you can switch into a Macquarie Investment Warrant by selling the shares and buying Warrants. By doing so you will maintain exposure to the share movements and dividends while releasing capital for other investments.

Here is a hypothetical example:

Advantages of Investment Warrants over other financing facilities

• Ability to leverage above 70%
• Limited downside
• No margin calls

There is always some risks in any kind of investment may it be stock, plain vanilla warrant or option etc. Hence the same goes for Investment Warrant. Please do some homework and understand the risks and rewards of this new derivative instrument better before risking your hard earned money in it.

The Time Is Now

It has been almost two months since I last posted my blog entry. I must apologize to my readers who may think I have gone missing and decided not to blog again. In fact, so many things have happened within my family in these two months so much so that I feel very depress and helpless at certain point in time. But I guess I need to be strong, for my family needs me more than ever now.

My mum was diagnosed with ovarian cancer and the doctor said it is most probably in stage 3c or 4. I read up a lot on ovarian cancer since the day my mum got admitted to the hospital and I knew stage 3c and 4 are the last two stages of ovarian cancer. The gynecologist doctor who saw my mum was actually a secondary school mate of mine and he did mention to me that the prognosis of ovarian cancer is not very optimistic. At that point of time, I really cannot hold back my emotions anymore and I cried. I knew for the very fact that crying would not help and I should not have cried especially in front of my mum. But I simply cannot control myself. I cleared all my FTOs during that period of time to accompany her in the hospital. I was in the hospital early in the morning and would not leave till late at night. I know my mum needs a lot of family support especially at this point in time.

Sometimes I really wonder what have my mum done wrong to deserve this. Her life as a child was not an easy one. Being the eldest daughter in the family, she got to help out at my grandfather stall at a very young age and she did not even have a chance to go school like her other siblings. As such, she does not even know how to write her own name. Yet, as a mum of us, she has given us unconditional love throughout our bringing up, taking care of us and ensuring we are always given the best and working long hours for some miserable paycheck just so as to lessen the burden of the family. Finally when my sister got married and had her first child this year, we advised her to retire so she can help to take care of my nephew. I was happy that she can finally relax and enjoy a little after all these years of hard work and can lead a better life at old age now. But then…why should she be stricken with this? I regretted very much not bringing her to do annual checkup. I hate myself for not noticing the first time she complained about her abdomen discomfort. I am really very angry with myself.

As the day passes by and I see her getting thinner and thinner and her hairs starting to drop because of the chemotherapy she is undergoing, my heart aches a lot. The very fact that I am so helpless seeing her suffering but not able to share with her the pain she is undergoing really make me very useless. The fact that she has to take a blood test every time she goes down to the hospital made my heart aches even more. The fact that she lost her sister few years back and her father the very next year made me realized one thing - this world has not in any way be kind or fair to her. Why must her life be so tough?

I once came across a poem when I first learnt to design my own webpage and I can appreciate that poem more than ever now. I would like to share that poem here with my readers. This poem titled “The time is now”. I hope my readers would forward it to anyone who they think might benefit them in one way or another. This poem was written by a mum to her son.

THE TIME IS NOW

If you are ever going to love me
Love me now while I can know
The sweet and tender feelings
Which from true affection flow

Love me now while I am living
Do not wait until I am gone
And then have it chiseled in marble
Sweet words on ice cold stone

If you have tender thoughts of me
Please tell me now
If you wait until I am sleeping
Never will be death between us
And I won't hear you then

So if you love me, even a little bit
Let me know while I am living
So that I can treasure it

Please feel free to bless anyone with this poem. I would like to take this opportunity to thank my friends, my colleagues and my readers that have given me a lot of support and encouragement during this very tough time of mine. Due to my mum condition, I have missed out some of the WAT gatherings and I have decided to take my CFA level II exam next year instead. Lastly, I will be back blogging and sharing things I learnt. Thanks everyone.

Actual Operation of Warrant Trading (Part 1)

It has been a really long time since I last blog. I am very sorry for my readers who visited my site and got nothing new to read. I was held up with a lot of things recently. Nevertheless, despite being busy, I have also been reading a lot on the Heston Stochastic Volatility Model and Model-Free Implied Volatility. I have dived a little more in depth on how to adjust a financial report to better value a company. There are simply too many things which I want to share as I read but I need some times to digest and further verify those things I read with real life examples before I share with my readers. As such, I have decided to continue another series of warrant trading posting but this time round, I’ll be posting on the actual operation of warrant trading.

In this posting, I’m going to discuss about the effect of tick value on warrants. With effect from 24th December 2007, SGX had revised the minimum bid sizes for its various financial instruments products. I am interested in the revised minimum bid size for securities. Under the new revised schedule, any securities trading below $1.00 have a minimum bid size of $0.005. Securities trading between $1.00 and $9.99 have a minimum bid size of $0.01. Securities trading $10.00 and above have a minimum bid size of $0.02. With this new revised minimum bid sizes in mind, I’m going to use Singapore Exchange (SGX) as an example for my discussion.

When SGX is trading at close to $10.00 in January this year, we can see that some of the warrants derived from it are lagging behind, while some others follow closely but with a bigger bid/ask spread. You can easily verify this by randomly looking at how closely the warrant price follows the stock price under the Data & Chart > Historical Price at SG Warrants. For easy reference, I have randomly capture three images for some SGX call warrants.

The examples I used here are not really perfect and one will be right to argue that there are some other factors (e.g. such as trading volume on that particular day) that causes the charts to be difference. Nevertheless, they are good enough to illustrate the point I’m going to discuss.

From the screen captures I have done, you will notice that for some warrants, the price of the warrant will follow very closely with that of the underlying stock while on the other hand, some of them do not follow as closely. Of course, for put warrants, the prices move in opposite direction of the underlying stock.

Assuming SGX is trading close to $10.00 at this point of time. The next tick will either bring the stock price up to $10.02 or $9.98. Hence, in this example here, a $0.02 change will mean a 0.2% increase or decrease in the underlying price. If the warrant has an effective gearing of 10 times, this $0.02 change in the underlying will be enlarged to a 2% (10 X 0.2%) change in warrant price.

Looking at another perspective, suppose we have a SGX warrant with a delta of 40% and a conversion ratio of 1:1, then for every $1.00 change in the underlying price, the warrant should, in theory, rise or fall by $0.40. So, for every $0.02 change in stock price, the warrant should move by $0.008 ($0.02 X 0.4). If the face value of the warrant is above $1.00, its tick value will be $0.01. For each tick ($0.02) of movement in the underlying price, the warrant will move by $0.008, which is not enough to make a tick in the warrant price. It appears that the warrant is lagging behind. However, if the warrant’s face value is below $1.00, its tick value will be $0.005. In this case, for every tick ($0.02) of movement in the underlying price, the warrant price will move by 1.6 ticks. Hence, if one goes for a warrant with a high delta and smaller face value, one may expect to see some movements in the warrant price.

In the face of technical issues, different issuers opt for different treatments. Some leave their warrants to swing up and down with the underlying securities, which indirectly increases the trading risk. Others take action to even out the fluctuations and make their warrants move up and down in an orderly manner (so that the warrant price will follow the underlying price to take on the ask side or the bid side). Still others choose to widen the bid/ask spreads of their warrants.

Investors should understand that there exist various technical issues in the market. We should not hastily jump to the conclusion based on the varied performance of different warrants that this or that issuer is not doing a good job. The truth may boiled down to the different treatments adopted by the issuers.

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.