*** Proof of Product ***
Exploring the Essential Features of “Fabrice Douglas Rouah & Gregory Vainberg – Option Pricing Models & Volatility”
Option Pricing Models and Volatility Using Excel-VBA
by Fabrice D. Rouah (Author), Gregory Vainberg (Author)
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
Editorial Reviews
From the Inside Flap
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
From the Back Cover
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.
“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
About the Author
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.
Chapter One
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 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, respectively.
Function Set_cNum(rPart, iPart) As cNum Set_cNum.rP = rPart Set_cNum.iP = iPart End Function
The function cNumProd() multiplies two complex numbers cNum1 and cNum2, and returns the complex number cNumProd with real and imaginary parts cNumProd.rp and cNumProd.ip, respectively.
Function cNumProd(cNum1 As cNum, cNum2 As cNum) As cNum cNumProd.rP = (cNum1.rP * cNum2.rP) – (cNum1.iP * cNum2.iP) cNumProd.iP = (cNum1.rP * cNum2.iP) + (cNum1.iP * cNum2.rP) End Function
Similarly, the functions cNumDiv(), cNumAdd(), and cNumSub() return the real and imaginary parts of a complex number obtained by, respectively, division, addition, and subtraction of two complex numbers, while the function cNum-Conj() returns the conjugate of a complex number.
The function cNumSqrt() returns the square root of a complex number:
Function cNumSqrt(cNum1 As cNum) As cNum r = Sqr(cNum1.rP ^ 2 + cNum1.iP ^ 2) y = Atn(cNum1.iP / cNum1.rP) cNumSqrt.rP = Sqr(r) * Cos(y / 2) cNumSqrt.iP = Sqr(r) * Sin(y / 2) End Function
The functions cNumExp() and cNumLn() produce, respectively, the exponential of a complex number and the natural logarithm of a complex number using the VBA function Atn() for the inverse tan function (arctan).
Function cNumExp(cNum1 As cNum) As cNum cNumExp.rP = Exp(cNum1.rP) * Cos(cNum1.iP) cNumExp.iP = Exp(cNum1.rP) * Sin(cNum1.iP) End Function
Function cNumLn(cNum1 As cNum) As cNum r = (cNum1.rP^2 + cNum1.iP^2)^0.5 theta = Atn(cNum1.iP / cNum1.rP) cNumLn.rP = Application.Ln(r) cNumLn.iP = theta End Function
Finally, the functions cNumReal() and cNumIm() return the real and imaginary parts of a complex number, respectively.
The Excel file Chapter1Complex illustrates how these functions work. The VBA function Complexop2() performs operations on two complex numbers:
Function Complexop2(rP1, iP1, rP2, iP2, operation) Dim cNum1 As cNum, cNum2 As cNum, cNum3 As cNum Dim output(2) As Double cNum1 = setcnum(rP1, iP1) cNum2 = setcnum(rP2, iP2) Select Case operation Case 1: cNum3 = cNumAdd(cNum1, cNum2) ‘ Addition Case 2: cNum3 = cNumSub(cNum1, cNum2) ‘ Subtraction Case 3: cNum3 = cNumProd(cNum1, cNum2) ‘ Multiplication Case 4: cNum3 = cNumDiv(cNum1, cNum2) ‘ Division End Select output(1) = cNum3.rP output(2) = cNum3.iP complexop2 = output End Function
The Complexop2() function requires five inputs, a real and imaginary part for each number, and the parameter corresponding to the operation being performed (1 through 4). Its output is an array of dimension two, containing the real and imaginary parts of the complex number. Figure 1.1 illustrates how this function works. To add the two numbers 11 + 3i and -3 + 4i, which appear in ranges C4:D4 and C5:D5 respectively, in cell C6 we type
= Complexop2(C4,D4,C5,D5,F6)
and copy to cell D6, which produces the complex number 8 + 7i. Note that the output of the Complexop2() function is an array. The appendix to this book explains in detail how to output arrays from functions. Note also that the last argument of the function Complexop2() is cell F6, which contains the operation number (1) corresponding to addition.
Similarly, the function Complexop1() performs operations on a single complex number, in this example 4 + 5i. To obtain the complex conjugate, in cell C15 we type
= Complexop2(C14,D14,F15)
and copy to cell D15 This is illustrated in the bottom part of Figure 1.1.
Relevance of Complex Numbers
Complex numbers are abstract entities, but they are extremely useful because they can be used in algebraic calculations to produce solutions that are tangible. In particular, the option pricing models covered in this book require a probability density function for the logarithm of the stock price, X = log(S). From a theoretical standpoint, however, it is often easier to obtain the characteristic function [[psi].sub.X](t) for log(S), given by
[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII]where
i = [square root of -1,
fX(x) = probability density function of X.
The probability density function for the logarithm of the stock price can then be obtained by inversion of [[psi].sub. X](t):
[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII]One corollary of Levy’s inversion formula-an alternate inversion formula-is that the cumulative density function [F.sub.X](x) = Pr(X ) for the logarithm of the stock price can be obtained. The following expression is often used for the risk-neutral probability that a call option lies in-the-money:
[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII]where k = log(K) is the logarithm of the strike price K. Again, this formula requires evaluating an integral that contains i = [square root of -1].
FINDING ROOTS OF FUNCTIONS
In this section we present two algorithms for finding roots of functions, the Newton-Raphson method, and the bisection method. These will become important in later chapters that deal with Black-Scholes implied volatility. Since the Black-Scholes formula cannot be inverted to yield the volatility, finding implied volatility must be done numerically. For a given market price on an option, implied volatility is that volatility which, when plugged into the Black-Scholes formula, produces the same price as the market. Equivalently, implied volatility is that which produces a zero difference between the market price and the Black-Scholes price. Hence, finding implied volatility is essentially a root-finding problem.
The chief advantage of programming root-finding algorithms in VBA, rather than using the Goal Seek and Solver features included in Excel, is that a particular algorithm can be programmed for the problem at hand. For example, we will see in later chapters that the bisection algorithm is particularly well suited for finding implied volatility. There are at least four considerations that must be kept in mind when implementing root-finding algorithms. First, adequate starting values must be carefully chosen. This is particularly important in regions of highly functional variability and when there are multiple roots and local minima. If the function is highly variable, a starting value that is not close enough to the root might stray the algorithm away from a root. If there are multiple roots, the algorithm may yield only one root and not identify the others. If there are local minima, the algorithm may get stuck in a local minimum. In that case, it would yield the minimum as the best approximation to the root, without realizing that the true root lies outside the region of the minimum. Second, the tolerance must be specified. The tolerance is the difference between successive approximations to the root. In regions where the function is flat, a high number for tolerance can be used. In regions where the function is very steep, however, a very small number must be used for tolerance. This is because even small deviations from the true root can produce values for the function that are substantially different from zero. Third, the maximum number of iterations needs to be defined. If the number of iterations is too low, the algorithm may stop before the tolerance level is satisfied. If the number of iterations is too high and the algorithm is not converging to a root because of an inaccurate starting value, the algorithm may continue needlessly and waste computing time.
To summarize, while the built-in modules such as the Excel Solver or Goal Seek allows the user to specify starting values, tolerance, maximum number of iterations, and constraints, writing VBA functions to perform root finding sometimes allows flexibility that built-in modules do not. Furthermore, programming multivariate optimization algorithms in VBA, such as the Nelder-Mead covered later in this chapter, is easier if one is already familiar with programming single-variable algorithms. The root-finding methods outlined in this section can be found in Burden and Faires (2001) or Press et al. (2002).
Newton-Raphson Method
This method is one of the oldest and most popular methods for finding roots of functions. It is based on a first-order Taylor series approximation about the root. To find a root x of a function f(x), defined as that x which produces f(x) = 0, select a starting value x0 as the initial guess to the root, and update the guess using the formula
[f.sub.([x.sub.i]+1)] = [x.sub.i] -f([x.sub.i])f'([x.sub.i]) (1.2)for i = 0, 1, 2, . . ., and where f'([x.sub.i]) denotes the first derivative of f(x) evaluated at [x.sub.i]. There are two methods to specify a stopping condition for this algorithm, when the difference between two successive approximations is less than the tolerance level [epsilon], or when the slope of the function is sufficiently close to zero. The VBA code in this chapter uses the second condition, but the code can easily be adapted for the first condition. (Continues…)
Product details
Publisher : Wiley; 1st edition (March 29, 2007)
Language : English
Please see the full list of alternative group-buy courses available here: https://lunacourse.com/shop/
