Option Pricing Models and Volatility Using Excel-VBA

Fabrice D. Rouah, Gregory Vainberg

• Publisher: Wiley
• Year: 2012
• ISBN:
  9781118429204
• Edition: 1
• Topic: Business
• Subtopic: Finance
• Index: Business

This comprehensive guide offers traders, quants, and students the tools and techniques for using advanced models for pricing options. The accompanying website includes data files, such as options prices, stock prices, or index prices, as well as all of the codes needed to use the option and volatility models described in the book.

Praise for Option Pricing Models & Volatility Using Excel-VBA

"Excel is already a great pedagogical tool for teaching option valuation and risk management. But the VBA routines in this book elevate Excel to an industrial-strength financial engineering toolbox. I have no doubt that it will become hugely successful as a reference for option traders and risk managers."
— Peter Christoffersen, Associate Professor of Finance, Desautels Faculty of Management, McGill University

"This book is filled with methodology and techniques on how to implement option pricing and volatility models in VBA. The book takes an in-depth look into how to implement the Heston and Heston and Nandi models and includes an entire chapter on parameter estimation, but this is just the tip of the iceberg. Everyone interested in derivatives should have this book in their personal library."
— Espen Gaarder Haug, option trader, philosopher, and author of Derivatives Models on Models

"I am impressed. This is an important book because it is the first book to cover the modern generation of option models, including stochastic volatility and GARCH."
— Steven L. Heston, Assistant Professor of Finance, R.H. Smith School of Business, University of Maryland

Chapter 1

Mathematical Preliminaries

INTRODUCTION

In this chapter we introduce some of the mathematical concepts that will be needed to deal with the option pricing and stochastic volatility models introduced in this book, and to help readers implement these concepts as functions and routines in VBA. First, we introduce complex numbers, which are needed to evaluate characteristic functions of distributions driving option prices. These are required to evaluate the option pricing models of Heston (1993) and Heston and Nandi (2000) covered in Chapters 5 and 6, respectively. Next, we review and implement Newton’s method and the bisection method, two popular and simple algorithms for finding zeros of functions. These methods are needed to find volatility implied from option prices, which we introduce in Chapter 4 and deal with in Chapter 10. We show how to implement multiple linear regression with ordinary least squares (OLS) and weighted least squares (WLS) in VBA. These methods are needed to obtain the deterministic volatility functions of Chapter 4. Next, we show how to find maximum likelihood estimators, which are needed to estimate the parameters that are used in option pricing models. We also implement the Nelder-Mead algorithm, which is used to find the minimum values of multivariate functions and which will be used throughout this book. Finally, we implement cubic splines in VBA. Cubic splines will be used to obtain model-free implied volatility in Chapter 11, and model-free skewness and kurtosis in Chapter 12.

COMPLEX NUMBERS

Most of the numbers we are used to dealing with in our everyday lives are real numbers, which are defined as any number lying on the real line ℜ = (−∞, + ∞). As such, real numbers can be positive or negative; rational, meaning that they can be expressed as a fraction; or irrational, meaning that they cannot be expressed as a fraction. Some examples of real numbers are

and π. Complex numbers, however, are constructed around the imaginary unit i defined as

. While i is not a real number, i2 is a real number since i2 = −1. A complex number is defined as a = x + iy, where x and y are both real numbers, called the real and imaginary parts of a, respectively. The notation Re[] and Im[] is used to denote these quantities, so that Re[a] = x and Im[a] = y.

Operations on Complex Numbers

Many of the operations on complex numbers are done by isolating the real and imaginary parts. Other operations require simple tricks, such as rewriting the complex number in a different form or using its complex conjugate. Krantz (1999) is a good reference for this section.

Addition and subtraction of complex numbers is performed by separate operation on the real and imaginary parts. It requires adding and subtracting, respectively, the real and imaginary parts of the two complex numbers:

Multiplying two complex numbers is done by applying the distributive axiom to the product, and regrouping the real and imaginary parts:

The complex conjugate of a complex number is defined as

and is useful for dividing complex numbers. Since

, we can express division of any two complex numbers as the ratio

Exponentiation of a complex number is done by applying Euler’s formula, which produces

Hence, the real part of th...