— 1 —
We hope that our text, Advanced Modelling in Finance, is conclusive proof that a wide range of models can now be successfully implemented using spreadsheets. The models range across the complete spectrum of finance including equities, equity options and bond options spanning developments from the early fifties to the late nineties. The models are implemented in Excel spreadsheets, complemented with functions written using the VBA language within Excel. The resulting user-defined functions provide a portable library of programs with more than sufficient speed and accuracy.
Advanced Modelling in Finance should be viewed as a complement (or dare we say, an antidote) to traditional textbooks in the area. It contains relatively few derivations, allowing us to cover a broader range of models and methods, with particular emphasis on more recent advances.
The major theoretical developments in finance such as portfolio theory in the 1950s, the capital asset pricing model in the 1960s and the Black-Scholes formula in the 1970s brought with them analytic solutions that are now straightforward to calculate. The subsequent decades have seen a growing body of developments in numerical methods. With an intelligent choice of parameters, binomial trees have assumed a central role in the more numerically-intensive calculations now required to value equity and bond options. The centre of gravity in finance now concerns the search for more efficient ways of performing such calculations rather than the theories from yesteryear.
The breadth of the coverage across finance and the sophistication needed for some of the more advanced models are testament to the ability of Excel, the built-in functions contained in Excel and the real programming environment that VBA provides. This allows us to highlight the commonality of assumptions (lognormality), mathematical problems (expectation) and numerical methods (binomial trees) throughout finance as a whole. Without exception, we have tried to ensure a consistent and simple notation throughout the book to reinforce this commonality and to improve clarity of exposition.
Our objective in writing a book that covers the broad range of subjects in finance has proved to be both a challenge and an opportunity. The opportunity has provided us with the chance to overview finance as a whole and, in so doing, to make important connections and bring out commonalities in asset price assumptions, mathematical problems, numerical methods and Excel solutions. In the following sections we summarise a few of these unifying insights that apply to equities, options and bonds with regard to finance, mathematical topics, numerical methods and Excel features. This is followed by a more detailed summary of the main topics covered in each chapter of the book.
1.1 FINANCE INSIGHTS
The genesis of modern finance as a subject separate from economics started with Markowitz's development of portfolio theory in 1952. Markowitz used utility theory to model the preferences of individual investors and to develop a mean-variance approach
Advanced Modelling in Finance
to examining the trade-off between return (as measured by an asset's mean return) and risk (measured by an asset's variance of return). This subsequently led to the development by Sharpe, Lintner and Treynor of the capital asset pricing model (CAPM), an equilibrium model describing expected returns on equities. The CAPM introduced beta as a measure of non-diversifiable risk, arguing that the creation of portfolios served to minimise the specific risk element of total risk (variance).
The next great theoretical development was the equity option pricing formula of Black and Scholes, which rested on the ability to create a (riskless) hedge portfolio. Contemporaneously, Merton extended the Black-Scholes formula to allow for continuous dividends and thus also options on commodities and currencies. The derivation of the original formula required the solving of the diffusion (or heat) equation familiar from physics, but was subsequently encompassed by the broader risk-neutral approach to the valuation of derivatives.
1.2 ASSET PRICE ASSUMPTIONS
Although portfolio theory was derived through individual preferences, it could also have been obtained by making assumptions about the distribution of asset price returns. The standard assumption is that equity returns follow a lognormal distribution-equivalently we can say that equity log returns follow a normal distribution. More recently, practitioners have examined the effect of departures from strict normality (as measured by skewness and kurtosis) and have also proposed different distributions (for example, the reciprocal gamma distribution).
Although bonds have characteristics that are different from equities, the starting point for bond option valuation is the short interest rate. This is frequently assumed to follow the lognormal or normal distribution. The result is that familiar results grounded in these probability distributions can be applied throughout finance.
1.3 MATHEMATICAL AND STATISTICAL PROBLEMS
Within the equities part, the mathematical problems concern optimisation. The optimisation can also include additional constraints, exemplified by Sharpe's development of returns-based style analysis. Beta is estimated as the slope coefficient in a linear regression. Options are valued in the risk-neutral framework as statistical expectations. The normal distribution of log equity prices can be approximated by an equivalent discrete binomial distribution. This binomial distribution provides the framework for calculating the expected option value.
1.4 NUMERICAL METHODS
In the context of portfolio optimisation, the optimisation involves portfolio variance, and the numerical method needed for optimisation is quadratic programming. Style analysis also uses quadratic programming, the quantity to be minimised being the error variance. Although not usually thought of as optimisation, linear regression chooses slope coefficients to minimise residual error. Here optimisation is of a different kind, regression analysis, which provides analytical formulas to calculate the beta coefficients.
Turning to option valuation, the binomial tree provides the structure within which the risk-neutral expectation can be calculated. We highlight the importance of parameter
choice by examining the convergence properties of three different binomial trees. Such trees also allow the valuation of American options, where the option can be exercised at any date prior to maturity.
With European options, techniques such as Monte Carlo simulation and numerical integration are also used. Numerical search methods, in particular the Newton-Raphson approach, ensure that volatilities implied by option prices in the market can be estimated.
1.5 EXCEL SOLUTIONS
The spreadsheets demonstrate how Excel can be used as a prototype for building models. Within the individual spreadsheets, all the formulas in the cells can easily be examined and we have endeavoured to incorporate all intermediate calculations in cells of their own. The spreadsheets also allow the hallmark ability to 'what-if by changing parameter values in cells.
The implementation of all the models and methods occurs twice: once in the spreadsheets and once in the VBA functions. This dual approach serves as an important check on the accuracy of the numerical calculations.
Some of the VBA procedures are macros, normally seen by others as the main purpose of VBA in Excel. However, the majority of the procedures we implement are user-defined functions. We demonstrate how easily these functions can be written in VBA and how they can incorporate Excel functions, including the powerful matrix functions.
The Goal Seek and Solver commands within Excel are used in the optimisation tasks. We show how these commands can be automated using VBA user-defined functions and macros. Another under-used aspect of Excel involves the application of array functions (invoked by the Ctrl+Shift+Enter keystroke combination) and we implement these in user-defined functions. To improve efficiency, our binomial trees in user-defined functions use one-dimensional arrays (vectors) rather than two-dimensional arrays (matrices).
1.6 TOPICS COVERED
There are four parts in the book, the first part illustrating the advanced modelling features in Excel followed by three parts with applications in finance. The three parts on applications cover equities, options on equities and options on bonds.
Chapter 2 emphasises the advanced Excel functions and techniques that we use in the remainder of the book. We pay particular attention to the array functions within Excel and provide a short section detailing the mathematics underlying matrix manipulation.
Chapter 3 introduces the VBA programming environment and illustrates a step-by-step approach to the writing of VBA subroutines (macros). The examples chosen demonstrate how macros can be used to automate and repeat tasks in Excel.
Chapter 4 moves on to VBA user-defined functions, which have a crucial role throughout the applications in finance. We emphasise how to deal with both scalar and array variables-as input variables to VBA functions, their use in calculations and finally as output variables. Again, we use a step-by-step approach for a number of examples. In particular, we write user-defined functions to value both European options (the Black—Scholes formula) and American options (binomial trees).
Chapter 5 introduces the first application part, that dealing with equities.
Chapter 6 covers portfolio optimisation, using both Solver and analytic solutions. As will become the norm in the remaining chapters, Solver is used both in the spreadsheet
Advanced Modelling in Finance
and automated in a VBA macro. By using the array functions in Excel and VBA, we detail how the points on the efficient frontier can be generated. The development of portfolio theory is divided into three generic problems, which recur in subsequent chapters.
Chapter 7 looks at (equity) asset pricing, starting with the single-index model and the capital asset pricing model (CAPM) and concluding with Value-at-Risk (VaR). This introduces the assumption that asset log returns follow a normal distribution, another recurrent theme.
Chapter 8 covers performance measurement, again ranging from single-parameter measures used in the very earliest days to multi-index models (such as style analysis) that represent current best practice. We show, for the first time in a textbook, how confidence intervals can be determined for the asset weights from style analysis.
Chapter 9 introduces the second application part, that dealing with options on equities. Building on the normal distribution assumed for equity log returns, we detail the creation of the hedge portfolio that is the key insight behind the Black-Scholes option valuation formula. The subsequent interpretation of the option value as the discounted expected value of the option payoff in a risk-neutral world is also introduced.
Chapter 10 looks at binomial trees, which can be viewed as a discrete approximation to the continuous normal distribution assumed for log equity prices. In practice, binomial trees form the backbone of numerical methods for option valuation since they can cope with early exercise and hence the valuation of American options. We illustrate three different parameter choices for binomial trees, including the little-known Leisen and Reimer tree that has vastly superior convergence and accuracy properties compared to the standard parameter choices. We use a nine-step tree in our spreadsheet examples, but the user-defined functions can cope with any number of steps.
Chapter 11 returns to the Black-Scholes formula and shows both its adaptability (allowing options on assets such as currencies and commodities to be valued) and its dependence on the asset price assumptions.
Chapter 12 covers two alternative ways of calculating the statistical expectation that lies behind the Black-Scholes formula for European options. These are Monte Carlo simulation and numerical integration. Although these perform less well for the simple options we consider, each of these methods has a valuable role in the valuation of more complicated options.
Chapter 13 moves away from the assumption of strict normality of asset log returns and shows how such deviation (typically through differing skewness and kurtosis parameters) leads to the so-called volatility smile seen in the market prices of options. Efficient methods for finding the implied volatility inherent in European option prices are described.
Chapter 14 introduces the third application part, that dealing with options on bonds. While bond prices have characteristics that are different from equity prices, there is a lot of commonality in the mathematical problems and numerical methods used to value options. We define the term structure based on a series of zero-coupon bond prices, and show how the short-term interest rate can be modelled in a binomial tree as a means of valuing zero-coupon bond cash flows.
Chapter 15 covers two models for interest rates, those of Vasicek and Cox, and Ingersoll and Ross. We detail analytic solutions for zero-coupon bond prices and options on zero-coupon bonds together with an iterative approach to the valuation of options on coupon bonds.
Chapter 16 shows how the short rate can be modelled in a binomial tree in order to match a given term structure of zero-coupon bond prices. We build the popular
Black-Derman-Toy interest rate tree (both in the spreadsheet and in user-defined functions) and show how it can be used to value both European and American options on zero-coupon bonds.
The final Appendix is a Pandora's box of other user-defined functions, that are less relevant to the chosen applications in finance. Nevertheless they constitute a useful toolbox, including as they do functions for ARIMA modelling, splines, eigenvalues and other calculation procedures.
1.7 RELATED EXCEL WORKBOOKS
Part I which concentrates on Excel functions and procedures and understanding VBA has three related workbooks, AMFEXCEL, VBSUB and VBFNS which accompany Chapters 2, 3 and 4 respectively.
Part II on equities has three related workbooks, EQUITY1, EQUITY2 and EQUITY3 which accompany Chapters 6, 7 and 8 respectively.
Part III on options on equities has four files, OPTION 1, OPTION2, OPTION3 and OPTION4 which accompany Chapters 10, 11, 12 and 13 respectively.
Part IV on bonds has two related workbooks, BOND1 and BOND2 which accompany Chapters 14, 15 and 16 as indicated in the text.
The Appendix has one workbook, OTHERFNS.
1.8 COMMENTS AND SUGGESTIONS
Having spent so much time developing the material and writing this book, we would very much appreciate any comments, suggestions and, dare we say, possible corrections and improvements. Please email firstname.lastname@example.org.