Über die Autorin bzw. den Autor

Fabrice Douglas Rouah is a Senior Quantitative Analyst at a large financial firm in Boston. He is coauthor and coeditor of four books on hedge funds and CTAs. This is his third book with John Wiley & Sons.

Gregory Vainberg is a Corporate Risk Specialist at a large consulting firm in Montreal. He is also the creator of the top finance and math VBA Web site, www.vbnumericalmethods.com.

Von der hinteren Coverseite

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

Aus dem Klappentext

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

Auszug. © Genehmigter Nachdruck. Alle Rechte vorbehalten.

Option Pricing Models and Volatility Using Excel-VBA

John Wiley & Sons

Chapter One

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 R = (-[infinity], +[infinity]). 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 1/3, -3, [square root of 2], and [pi]. Complex numbers, however, are constructed around the imaginary unit i defined as i = [square root of -1]. While i is not a real number, [i.sup.2] is a real number since [i.sup.2] = -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:

(x + iy) + (u + iv) = (x + u) + i(y + v), (x + iy) - (u + iv) = (x - u) + i(y - v).

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

(x + iy)(u + iv) = (xu - yv) + i(xv + yu).

The complex conjugate of a complex number is defined as [bar.a] = x - iy and is useful for dividing complex numbers. Since [bar.aa] = [x.sup.2] + [y.sup.2], we can express division of any two complex numbers as the ratio

x + iy/u + iv = (x + iy)(u - iv)/(u + iv)(u - iv) = (xu + yv) + i(yu - xv) [u.sup.2] + [v.sup.2]

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

exp(x + iy) = exp(ITLxITL) exp(iy) = exp(ITLxITL)[cos(y) + i sin(y)].

Hence, the real part of the resulting complex number is exp(ITLxITL) cos(y), and the imaginary part is exp(ITLxITL) sin(y). Obtaining the logarithm of a complex number requires algebra. Suppose that w = a + ib and that its logarithm is the complex number z = x + iy, so that z = log(w). Since w = exp(z), we know that a = [e.sup.x] cos(y) and b = [e.sup.x] sin(y). Squaring these numbers, applying the identity cos[(y).sup.2] + sin[(y).sup.2] = 1, and solving for ITLxITL produces ITLxITL = Re[z] = log([square root of [a.sup.2] + [b.sup.2]). Taking their ratio produces

b/a = sin(y)/cos(y) = tan(y),

and solving for y produces y = Im[z] = arctan(b/a).

It is now easy to obtain the square root of the complex number w = a + ib, using DeMoivre's Theorem:

[[cos(x) + i sin(x)].sup.n] = cos(nx) + i sin(nx). (1.1)

By arguments in the previous paragraph, we can write w = r cos(y) + ir sin(y) = [re.sup.iy], where y = arctan (b/a) and r = [square root of [a.sup.2] + [b.sup.2]. The square root of w is therefore

[[square root of r[cos(y) + i sin(y)].sup.1/2].

Applying DeMoivre's Theorem with n = 1/2, this becomes

[square root of r[cos(y/2) + i sin(y/2)],

so that the real and imaginary parts of [square root of w] are [square root of r cos(y/2) and [square root of r sin(y/2), respectively.

Finally, other functions of complex numbers are available, but we have not included VBA code for these functions. For example, the cosine of a complex number z = x + iy produces another complex number, with real and imaginary parts given by .sub. cosh(y) and - sin(x) sinh(y) respectively, while the sine of a complex number has real and imaginary parts sin(x) cosh(y) and - cos(x) sinh(y), respectively. The hyperbolic functions cosh(y) and sinh(y) are defined in Exercise 1.1.

Operations Using VBA

In this section we describe how to define complex numbers in VBA and how to construct functions for operations on complex numbers. Note that it is possible to use the built-in complex number functions in Excel directly, without having to construct them in VBA. However, we will see in later chapters that using the built-in functions increases substantially the computation time required for convergence of option prices. Constructing complex numbers in VBA, therefore, makes computation of option prices more efficient. Moreover, it is sometimes preferable to have control over how certain operations on complex numbers are defined. There are other definitions of the square root of a complex number, for example, than that given by applying DeMoivre's Theorem. Finally, learning how to construct complex numbers in VBA is a good learning exercise.

The Excel file Chapter1Complex contains VBA functions to define complex numbers and to perform operations on complex numbers. Each function returns the real part and the imaginary part of the resulting complex number. The first step is to construct a complex number in terms of its two parts. The function Set cNum() defines a complex number with real and imaginary parts given by set cNum.rp and set cNum.ip,...