Advanced Modelling in Finance using Excel and VBA

MARY JACKSON and MIKE STAUNTON have worked together teaching spreadsheet modelling to both graduate students and practitioners since 1985. MARY JACKSON was Assistant Professor of Decision Sciences at London Business School. She is author of three previous books for John Wiley & Sons: Understanding Expert Systems (1992), Advanced Spreadsheet Modelling (1988) and Creative Modelling (1985). MIKE STAUNTON is Visiting Lecturer in Numerical Methods at City University Business School and Director of the London Share Price Datbase at London Business School. He is coauthor, with Elroy Dimson and Paul Marsh, of Millennium Book II: 101 Years of Investment Returns (2001) and The Millennium Book: A Century of Investment Returns (2000).

No. 4 bestseller in 'General Finance' (erivativesreview.com, December 2001)

• Preface xiAcknowledgements xii1 Introduction 11.1 Finance insights 11.2 Asset price assumptions 21.3 Mathematical and statistical problems 21.4 Numerical methods 21.5 Excel solutions 31.6 Topics covered 31.7 Related Excel workbooks 51.8 Comments and suggestions 5Part One Advanced Modelling in Excel 72 Advanced Excel functions and procedures 92.1 Accessing functions in Excel 92.2 Mathematical functions 102.3 Statistical functions 122.3.1 Using the frequency function 122.3.2 Using the quartile function 142.3.3 Using Excel’s normal functions 152.4 Lookup functions 162.5 Other functions 182.6 Auditing tools 192.7 Data Tables 202.7.1 Setting up Data Tables with one input 202.7.2 Setting up Data Tables with two inputs 222.8 XY charts 232.9 Access to Data Analysis and Solver 262.10 Using range names 272.11 Regression 282.12 Goal Seek 312.13 Matrix algebra and related functions 332.13.1 Introduction to matrices 332.13.2 Transposing a matrix 332.13.3 Adding matrices 342.13.4 Multiplying matrices 342.13.5 Matrix inversion 352.13.6 Solving systems of simultaneous linear equations 362.13.7 Summary of Excel’s matrix functions 37Summary 373 Introduction to VBA 393.1 Advantages of mastering VBA 393.2 Object-oriented aspects of VBA 403.3 Starting to write VBA macros 423.3.1 Some simple examples of VBA subroutines 423.3.2 MsgBox for interaction 433.3.3 The writing environment 443.3.4 Entering code and executing macros 443.3.5 Recording keystrokes and editing code 453.4 Elements of programming 473.4.1 Variables and data types 483.4.2 VBA array variables 483.4.3 Control structures 503.4.4 Control of repeating procedures 513.4.5 Using Excel functions and VBA functions in code 523.4.6 General points on programming 533.5 Communicating between macros and the spreadsheet 533.6 Subroutine examples 563.6.1 Charts 563.6.2 Normal probability plot 593.6.3 Generating the efficient frontier with Solver 61Summary 65References 65Appendix 3A The Visual Basic Editor 65Stepping through a macro and using other debug tools 68Appendix 3B Recording keystrokes in ‘relative references’ mode 694 Writing VBA user-defined functions 734.1 A simple sales commission function 734.2 Creating Commission(Sales) in the spreadsheet 744.3 Two functions with multiple inputs for valuing options 754.4 Manipulating arrays in VBA 784.5 Expected value and variance functions with array inputs 794.6 Portfolio variance function with array inputs 814.7 Functions with array output 844.8 Using Excel and VBA functions in user-defined functions 854.8.1 Using VBA functions in user-defined functions 854.8.2 Add-ins 864.9 Pros and cons of developing VBA functions 86Summary 87Appendix 4A Functions illustrating array handling 88Appendix 4B Binomial tree option valuation functions 89Exercises on writing functions 94Solution notes for exercises on functions 95Part Two Equities 995 Introduction to equities 1016 Portfolio optimisation 1036.1 Portfolio mean and variance 1036.2 Risk–return representation of portfolios 1056.3 Using Solver to find efficient points 1066.4 Generating the efficient frontier (Huang and Litzenberger’s approach) 1096.5 Constrained frontier portfolios 1116.6 Combining risk-free and risky assets 1136.7 Problem One–combining a risk-free asset with a risky asset 1146.8 Problem Two–combining two risky assets 1156.9 Problem Three–combining a risk-free asset with a risky portfolio 1176.10 User-defined functions in Module1 1196.11 Functions for the three generic portfolio problems in Module1 1206.12 Macros in ModuleM 121Summary 123References 1237 Asset pricing 1257.1 The single-index model 1257.2 Estimating beta coefficients 1267.3 The capital asset pricing model 1297.4 Variance–covariance matrices 1307.5 Value-at-Risk 1317.6 Horizon wealth 1347.7 Moments of related distributions such as normal and lognormal 1367.8 User-defined functions in Module1 136Summary 138References 1388 Performance measurement and attribution 1398.1 Conventional performance measurement 1408.2 Active–passive management 1418.3 Introduction to style analysis 1448.4 Simple style analysis 1458.5 Rolling-period style analysis 1468.6 Confidence intervals for style weights 1488.7 User-defined functions in Module1 1518.8 Macros in ModuleM 151Summary 152References 153Part Three Options on Equities 1559 Introduction to options on equities 1579.1 The genesis of the Black–Scholes formula 1589.2 The Black–Scholes formula 1589.3 Hedge portfolios 1599.4 Risk-neutral valuation 1619.5 A simple one-step binomial tree with risk-neutral valuation 1629.6 Put–call parity 1639.7 Dividends 1639.8 American features 1649.9 Numerical methods 1649.10 Volatility and non-normal share returns 165Summary 165References 16610 Binomial trees 16710.1 Introduction to binomial trees 16710.2 A simplified binomial tree 16810.3 The Jarrow and Rudd binomial tree 17010.4 The Cox, Ross and Rubinstein tree 17310.5 Binomial approximations and Black–Scholes formula 17510.6 Convergence of CRR binomial trees 17610.7 The Leisen and Reimer tree 17710.8 Comparison of CRR and LR trees 17810.9 American options and the CRR American tree 18010.10 User-defined functions in Module0 and Module1 182Summary 183References 18411 The Black–Scholes formula 18511.1 The Black–Scholes formula 18511.2 Black–Scholes formula in the spreadsheet 18611.3 Options on currencies and commodities 18711.4 Calculating the option’s ‘greek’ parameters 18911.5 Hedge portfolios 19011.6 Formal derivation of the Black–Scholes formula 19211.7 User-defined functions in Module1 194Summary 195References 19612 Other numerical methods for European options 19712.1 Introduction to Monte Carlo simulation 19712.2 Simulation with antithetic variables 19912.3 Simulation with quasi-random sampling 20012.4 Comparing simulation methods 20212.5 Calculating greeks in Monte Carlo simulation 20312.6 Numerical integration 20312.7 User-defined functions in Module1 205Summary 207References 20713 Non-normal distributions and implied volatility 20913.1 Black–Scholes using alternative distributional assumptions 20913.2 Implied volatility 21113.3 Adapting for skewness and kurtosis 21213.4 The volatility smile 21513.5 User-defined functions in Module1 217Summary 219References 220Part Four Options on Bonds 22114 Introduction to valuing options on bonds 22314.1 The term structure of interest rates 22414.2 Cash flows for coupon bonds and yield to maturity 22514.3 Binomial trees 22614.4 Black’s bond option valuation formula 22714.5 Duration and convexity 22814.6 Notation 230Summary 230References 23015 Interest rate models 23115.1 Vasicek’s term structure model 23115.2 Valuing European options on zero-coupon bonds, Vasicek’s model 23415.3 Valuing European options on coupon bonds, Vasicek’s model 23515.4 CIR term structure model 23615.5 Valuing European options on zero-coupon bonds, CIR model 23715.6 Valuing European options on coupon bonds, CIR model 23815.7 User-defined functions in Module1 239Summary 240References 24116 Matching the term structure 24316.1 Trees with lognormally distributed interest rates 24316.2 Trees with normal interest rates 24616.3 The Black, Derman and Toy tree 24716.4 Valuing bond options using BDT trees 24816.5 User-defined functions in Module1 250Summary 252References 252Appendix Other VBA functions 253Forecasting 253ARIMA modelling 254Splines 256Eigenvalues and eigenvectors 257References 258Index 259