# Building an illustrative Vega and Gamma hedging model in Excel.

We build a simple Excel spreadsheet that allows us to hedge Gamma and Vega exposure for a single short position in a call option contract. Gamma and Vega hedges are created by buying cheaper out of money options with shorter or similar maturities.

The five lessons that follow review the methodology used to build the hedging sheet in Excel.

Figure 1 Hedging Vega Gamma exposure in Excel. The Solver objective function

For our first example we will build a spreadsheet focusing more on learning to work within the hedging domain using Solver in Excel. While we hedge a single position with a portfolio of cheaper options but our optimization metrics and our solver constraints remain simplistic.

Our second example extends the same framework for a portfolio of short positions by adding additional optimization metrics and new constraints.

1. Introducing the framework used to build the Excel model for hedging higher order Greeks.

Figure 2 Hedging Vega Gamma using Excel Solver step by step

1. We begin with a simple single position Solver model that hedges higher order Greeks (Gamma and Vega) using a universe of available out of money options using a simple objective function and minimal constraints. Our short position is one of the 13 at the money option selected by our sales desk. Our hedging universe is comprised of the other 104 options available for purchase and sale on the date of the hedge.

Figure 3 The Vega Gamma Hedging model in Solver

1. We review results from our Solver model and interpret them, evaluating the impact of changing constraints and objective functions. There are many ways to optimize and some are more “equal” than others.

Figure 4 Reviewing the results from the solver hedging model

What needs to change within the structure of the Solver model to hedge a book of written options rather than a single position. We introduce Vega and Gamma maturity buckets, Delta margins and new constraints on the number of open positions.

Figure 5 The Vega Gamma Differences by Maturity grid

The problem space is now revised to include a book of 13 options. We walk through a revised Solver model that handles Vega and Gamma buckets and evaluates solutions that minimize Delta, minimize hedge cost and minimize allocations (open positions) for the proposed hedging portfolio. We experiment with multiple objective functions to search for the one optimal solution.

Figure 6 Deconstructing the Solver hedging model for Vega and Gamma.

A review of hedge solutions (option allocations within our hedging universe) proposed by Excel solver point out obvious lessons. Shorter dated options are more sensitive to changes in the price of the underlying (Gamma) and less sensitive to changes in implied volatility (Vega). Longer dated options are more sensitive to changes in implied volatility. To find the optimal hedge solution you need to find the right balance between these two category.

Share

## Evaluating portfolio performance. A single metric to rule them all?

What is the best way of evaluating portfolio performance allocation strategies? Should we just compare risk, return or risk adjusted…

February 5, 2019 12:51 pm

## Project Plain speak – Sense making for the financial world.

6 mins read Introducing Project Plain speak. Currently a work in progress Plain speak focuses on bringing intelligent financial reporting…

January 22, 2019 6:08 am

## Oil markets time machine. Past, present and future.

9 mins read What can we learn about oil markets from the last ten years? The next decade. The final…

January 19, 2019 3:47 pm

## Signals in the data. Oil prices, tea leaves and crude price direction.

5 mins read What does the data say about future direction of crude oil markets. We look at OPEC spare…

January 19, 2019 12:44 pm

## Building a supply side model for crude oil

5 mins read What factors would impact crude oil supply side equation in 2019? Russian non compliance, lower breakeven for…

January 19, 2019 5:14 am

## Modeling demand for liquid fuels

5 mins read How would you model demand for crude oil? What are the key components? What is the long…

January 18, 2019 4:46 pm