Option Greeks Analysis in Excel

Click HERE to download the example file used in this tutorial.

The Bumping Technique

In the investment industry, when people need to evaluate and manage the financial risks of investment portfolio, they need to work out the "Greeks" (delta, gamma, vega, theta, etc). In order to calculate those Greeks, they need to shift the market variables (the inputs of variables into pricing models). Such "shift" technique is commonly called "bump" or "bumping". (e.g. bump the price up by 1 bps, bump the volatility up by 1%, etc.)

Here, I tell you what "bump" means. It has TWO components:

    1. Plus (bump up) or minus (bump down) a input by a small amount (the "bump size"); AND
    2. Hold all other inputs constant.

By bumping one input and holding the rest constant, and calculate the valuation of a portfolio (or an investment), we can find out the net impact of the variable on the valuation.

Example:

Assume Price of A = B + 0.5*C

B now is $10.
C now is $10.
Therefore A now is $15.

We want to calculate the sensitivity of A to the change of B and C.

Now, we bump B up by $1. Keep C unchanged.
A = 11 + 0.5*10
A (B up 1) = 16

Now, we bump C up by $1. Keep B unchanged.
A = 10 + 0.5*11
A (C up 1) = $15.5

So, because A changes more when B was bumped then when C was bumped, therefore, we can say that:
Price of A is more sensitive to change in B than change in C.

In the above simple example, we can see how "bumping" technique is applied, and the skill to "hold" all input variables constant while only bumping 1 variable at a time. This concept is very important when we perform Greeks analysis and more complex risk analaysis.



Delta

Delta is the rate of change of valuation of an investment per unit change in underlying price.

For example, if now, the price of an option on Apple stock is $10. When Apple stock price up $1, the option price becomes $10.3. So, we can say, the delta of the option is $0.3.

See Excel in Action

(In the example file, go to the "Delta" sheet.)

We have an option of strike price $1200. 1 year to mature.
Here is the details:

We have set up a function to calculate this option price "CallPrice" which receives the following inputs:
=CallPrice(UnderlyingPrice, ExercisePrice,TimetoMature, Interes,Volatility,Dividend)

  1. First, we work out the price of the option:
    =CallPrice(1000,1200,1,0.05,0.2,0)
    =32.47477417

  2. Next, we work out the price of the option if the underlying price is $1. (Here we use a "bump size" of $1. Some investments e.g. bonds, may use 1 bps. Others like base metals may be using $10.)
    =CallPrice(1000+1,1200,1,0.05,0.2,0)
    =32.76281816

  3. Now, we know that when underlying price up $1, option value is up (32.76281816-32.47477417) = 0.288044
    This is actually the "rate of change of value for each unit of change in underlying price". This is the Delta.
    A simple expression is:
    (Value[bump up] - Value[no bump])/bump size

  4. Some traders/brokers used to use delta represented in percentage. We can work of the delta in percentage by:
    (Value[bump up] - Value[no bump])/bump size/Value[no bump]

    (32.76281816-32.47477417)/1/32.47477417
    =0.887%


Gamma

What is Gamma?

For example, if now, the price of an option on Apple stock is $10. When Apple stock price up $1, the option price becomes $10.3. So, we can say, the delta of the option is $0.3. Then, when the stock price is $12, the option price becomes $10.4. So, the new delta is $0.4. We can say, the rate of change of delta is (0.4-0.3) = $0.1 . This is the gamma.

See Excel in Action

(In the example file, go to the "Gamma" sheet.)

We have an option of strike price $1200. 1 year to mature.
Here is the details:

To calculate the gamma, we need to calculate two delta numbers (for two consecutive price bumps). We can, like the Apple stock example, bump price up twice. Or, better, we can bump price up $1 and then down $1 to find out two delta. This is more preferable, as we can find out a gamma to better represent the rate of change of delta around the current underlying price ($1000). If we bump up twice, we might bump the underlying price too far away from the current price.

  1. First, we work out the price of the option (no bump):
    =CallPrice(1000,1200,1,0.05,0.2,0)
    =32.47477417

  2. Work out the price of option for $1 bump:
    =CallPrice(1000+1,1200,1,0.05,0.2,0)
    =32.76281816

  3. Delta [Price bump +ve]
    = (32.76281816-32.47477417)/1
    = 0.288044

  4. Work out the price of option for -1 dollar bump:
    =CallPrice(1000-1,1200,1,0.05,0.2,0)
    =32.18843386

  5. Delta [Price bump -ve]
    = (32.47477417-32.18843386)/1
    = 0.28634
    (Note: here, we assume price now is 999, and work out the delta for $1 bump up)

  6. Gamma = (Delta [Price bump +ve] - Delta [Price bump -ve])/bump size
    = (0.288044-0.28634)/1
    = 0.0017

It is common for Gamma to be small. If you got a very big gamma number, you may need to re-check your calculation.

There are special situations that gamma can be very big, for example, when an option is close to expire and underlying price is close to the strike price.


Gamma - the d delta/d price analysis

This is an advanced analysis of gamma. BUT this is simple. So, don't be scared because this is advanced. If you learnt this section, you can gain the advanced practical skill of "real" gamma analysis.

Gamma is, by definition, d delta / d price. So, if we already learnt to calculate the gamma, why do we need to do a more complex
d delta/d price analysis?

Gamma is NOT constant for different of underlying prices. If Dow is now is now 15200. The gamma of a portfolio is 200. But when when Dow rises to 16500, the portfolio's gamma may become 100. When Dow drops to 14500, the gamma may even become negative, -50. Therefore, gamma can change dramatically, and even change sign, for different underlying prices.

If you calculate a gamma for your portfolio now based on the current market parameters, for example +100, and go on a vacation. During your vacation, the market moves dramatically. Your gamma number might no longer be valid at all. The true gamma might have become -200. You watch the news a saw the market kept going up and up, and you THINK you're longer and longer delta and making money. And when you're back from vacation, your PnL fell deep into the toilet!!! And it'll be too late for you to re-calculate your real gamma then...

Do not think traders in hedge funds or investment banks know about this analysis. I've met with quite a numbers of different people in major investments working in different teams, including the risk management teams, I was amazed that some of them has no such control. They will be like flying blind-folded during crisis. (It they don't know their gamma, they won't know their real delta, and they won't be able to flatten they positions during a vigorous market movement.)

A d delta/d price analysis shows a matrix of delta and gamma numbers, across a series of underlying prices (instead of 1 underlying price). It allows the investor to see (and foresee) the delta and gamma movements at different market levels. (This is especially crucial if a portfolio contains a mix of different call and put options, or with some knock-in kock-out features.)

See Excel in Action

(In the example file, go to the "d Delta d Price" sheet.)

In the sheet, column E to G, is our "d delta d price" report. The objective is to show a spectrum of underlying price movements, portfolio values, delta and gamma.

Column D, type a list of price movements. These will be our "price bumps" we feed into the pricing function.
Column E, we use the CallDelta() function (as shown in previous examples) to calculate the portfolio value at different price bumps.
Coumn F, we fill the column with a simple calculation to work out the delta. (See formula in picture below)
Column G, we fill the column with a simple calculation to work out the gamma. (See formula in the second picture below)

Now, we got a spectrum of delta, gamma movements for market movement of + and - 10 dollars.

How to use this report? When market price is now 1008 (+ $8), we can quickly read from the report: Delta = 3018, Gamma = 17.3
If without this report, we still think Delta is 2880 and Gamma is 17.07!!


Vega

Vega is very similar to delta. The difference is, delta, we bump the underlying price. But for vega, we bump the volatility. (You can think of vega as "the delta of volatility".)

Definition of Vega: Vega is the rate of change of valuation of an investment per unit change in volatility.

See Excel in Action

(In the example file, go to the "Vega" sheet.)

Again, in the example file, we use the function "CallPrice" to calculate this option price:
=CallPrice(UnderlyingPrice, ExercisePrice,TimetoMature, Interes,Volatility,Dividend)

  1. First, we work out the price of the option:
    =CallPrice(1000,1200,1,0.05,0.2,0)
    =32.47477417

  2. Next, we work out the valuation of the option if the volatility increased by 1%.
    =CallPrice(1000,1200,1,0.05,0.2+0.01,0)
    =35.9170373

  3. Now, we know that when volatility up 1%, option value is up (35.9170373-32.47477417)/0.01*100 = 3.44226
    This is actually the "rate of change of value for each unit of change in volatility". This is the Vega.
    A simple expression is:
    (Value[bump up] - Value[no bump])/bump size

Theta

Theta is the change in value of an investment when 1 day past, with all market inputs being constant.

For example, if an option is 100 days to mature, and has a value of $10 at today market close. Tomorrow morning when you wake up, the market didn't move at all, the value of that option is $9.8. Why? This is the time value of money. Also, as the option is closer to mature, the owner of the option enjoys fewer chances of market uncertainty. These two components reduce the value of the option.

We can still use the bumping technique to caculate Theta. But in this case, we are bumping Time.

See Excel in Action

(In the example file, go to the "Theta" sheet.)

  1. First, we work out the price of the option (now):
    =CallPrice(1000,1200,1,0.05,0.2,0)
    =32.47477417

  2. Then, we try to work out the price of the option yesterday, holding all market inputs constant.
    "Yesterday" actually means "1 more day to mature", which means the option is (1*365+1)/365=1.00274 year to mature.
    =CallPrice(1000,1200,(1*365+1)/365,0.05,0.2,0)
    =32.603033

  3. Theta = Price(T) - Price(T-1)
    = 32.47477417 - 32.603033
    = -0.12826

This means, everyday, the value of the option drops by 0.12826, even with completely no change in market price or volatility or whatever.