Ebook
Advanced Modelling in Finance using Excel and VBAISBN: 9780470061664
276 pages
August 2006

Description
The book adopts a stepbystep approach to understanding the more sophisticated aspects of Excel macros and VBA programming, showing how these programming techniques can be used to model and manipulate financial data, as applied to equities, bonds and options. The book is essential for financial practitioners who need to develop their financial modelling skill sets as there is an increase in the need to analyse and develop ever more complex 'what if' scenarios.
 Specifically applies Excel and VBA to the financial markets
 Packaged with a CD containing the software from the examples throughout the book
Note: CDROM/DVD and other supplementary materials are not included as part of eBook file.
Table of Contents
Preface xi
Acknowledgements xii
1 Introduction 1
1.1 Finance insights 1
1.2 Asset price assumptions 2
1.3 Mathematical and statistical problems 2
1.4 Numerical methods 2
1.5 Excel solutions 3
1.6 Topics covered 3
1.7 Related Excel workbooks 5
1.8 Comments and suggestions 5
Part One Advanced Modelling in Excel 7
2 Advanced Excel functions and procedures 9
2.1 Accessing functions in Excel 9
2.2 Mathematical functions 10
2.3 Statistical functions 12
2.3.1 Using the frequency function 12
2.3.2 Using the quartile function 14
2.3.3 Using Excel’s normal functions 15
2.4 Lookup functions 16
2.5 Other functions 18
2.6 Auditing tools 19
2.7 Data Tables 20
2.7.1 Setting up Data Tables with one input 20
2.7.2 Setting up Data Tables with two inputs 22
2.8 XY charts 23
2.9 Access to Data Analysis and Solver 26
2.10 Using range names 27
2.11 Regression 28
2.12 Goal Seek 31
2.13 Matrix algebra and related functions 33
2.13.1 Introduction to matrices 33
2.13.2 Transposing a matrix 33
2.13.3 Adding matrices 34
2.13.4 Multiplying matrices 34
2.13.5 Matrix inversion 35
2.13.6 Solving systems of simultaneous linear equations 36
2.13.7 Summary of Excel’s matrix functions 37
Summary 37
3 Introduction to VBA 39
3.1 Advantages of mastering VBA 39
3.2 Objectoriented aspects of VBA 40
3.3 Starting to write VBA macros 42
3.3.1 Some simple examples of VBA subroutines 42
3.3.2 MsgBox for interaction 43
3.3.3 The writing environment 44
3.3.4 Entering code and executing macros 44
3.3.5 Recording keystrokes and editing code 45
3.4 Elements of programming 47
3.4.1 Variables and data types 48
3.4.2 VBA array variables 48
3.4.3 Control structures 50
3.4.4 Control of repeating procedures 51
3.4.5 Using Excel functions and VBA functions in code 52
3.4.6 General points on programming 53
3.5 Communicating between macros and the spreadsheet 53
3.6 Subroutine examples 56
3.6.1 Charts 56
3.6.2 Normal probability plot 59
3.6.3 Generating the efficient frontier with Solver 61
Summary 65
References 65
Appendix 3A The Visual Basic Editor 65
Stepping through a macro and using other debug tools 68
Appendix 3B Recording keystrokes in ‘relative references’ mode 69
4 Writing VBA userdefined functions 73
4.1 A simple sales commission function 73
4.2 Creating Commission(Sales) in the spreadsheet 74
4.3 Two functions with multiple inputs for valuing options 75
4.4 Manipulating arrays in VBA 78
4.5 Expected value and variance functions with array inputs 79
4.6 Portfolio variance function with array inputs 81
4.7 Functions with array output 84
4.8 Using Excel and VBA functions in userdefined functions 85
4.8.1 Using VBA functions in userdefined functions 85
4.8.2 Addins 86
4.9 Pros and cons of developing VBA functions 86
Summary 87
Appendix 4A Functions illustrating array handling 88
Appendix 4B Binomial tree option valuation functions 89
Exercises on writing functions 94
Solution notes for exercises on functions 95
Part Two Equities 99
5 Introduction to equities 101
6 Portfolio optimisation 103
6.1 Portfolio mean and variance 103
6.2 Risk–return representation of portfolios 105
6.3 Using Solver to find efficient points 106
6.4 Generating the efficient frontier (Huang and Litzenberger’s approach) 109
6.5 Constrained frontier portfolios 111
6.6 Combining riskfree and risky assets 113
6.7 Problem One–combining a riskfree asset with a risky asset 114
6.8 Problem Two–combining two risky assets 115
6.9 Problem Three–combining a riskfree asset with a risky portfolio 117
6.10 Userdefined functions in Module1 119
6.11 Functions for the three generic portfolio problems in Module1 120
6.12 Macros in ModuleM 121
Summary 123
References 123
7 Asset pricing 125
7.1 The singleindex model 125
7.2 Estimating beta coefficients 126
7.3 The capital asset pricing model 129
7.4 Variance–covariance matrices 130
7.5 ValueatRisk 131
7.6 Horizon wealth 134
7.7 Moments of related distributions such as normal and lognormal 136
7.8 Userdefined functions in Module1 136
Summary 138
References 138
8 Performance measurement and attribution 139
8.1 Conventional performance measurement 140
8.2 Active–passive management 141
8.3 Introduction to style analysis 144
8.4 Simple style analysis 145
8.5 Rollingperiod style analysis 146
8.6 Confidence intervals for style weights 148
8.7 Userdefined functions in Module1 151
8.8 Macros in ModuleM 151
Summary 152
References 153
Part Three Options on Equities 155
9 Introduction to options on equities 157
9.1 The genesis of the Black–Scholes formula 158
9.2 The Black–Scholes formula 158
9.3 Hedge portfolios 159
9.4 Riskneutral valuation 161
9.5 A simple onestep binomial tree with riskneutral valuation 162
9.6 Put–call parity 163
9.7 Dividends 163
9.8 American features 164
9.9 Numerical methods 164
9.10 Volatility and nonnormal share returns 165
Summary 165
References 166
10 Binomial trees 167
10.1 Introduction to binomial trees 167
10.2 A simplified binomial tree 168
10.3 The Jarrow and Rudd binomial tree 170
10.4 The Cox, Ross and Rubinstein tree 173
10.5 Binomial approximations and Black–Scholes formula 175
10.6 Convergence of CRR binomial trees 176
10.7 The Leisen and Reimer tree 177
10.8 Comparison of CRR and LR trees 178
10.9 American options and the CRR American tree 180
10.10 Userdefined functions in Module0 and Module1 182
Summary 183
References 184
11 The Black–Scholes formula 185
11.1 The Black–Scholes formula 185
11.2 Black–Scholes formula in the spreadsheet 186
11.3 Options on currencies and commodities 187
11.4 Calculating the option’s ‘greek’ parameters 189
11.5 Hedge portfolios 190
11.6 Formal derivation of the Black–Scholes formula 192
11.7 Userdefined functions in Module1 194
Summary 195
References 196
12 Other numerical methods for European options 197
12.1 Introduction to Monte Carlo simulation 197
12.2 Simulation with antithetic variables 199
12.3 Simulation with quasirandom sampling 200
12.4 Comparing simulation methods 202
12.5 Calculating greeks in Monte Carlo simulation 203
12.6 Numerical integration 203
12.7 Userdefined functions in Module1 205
Summary 207
References 207
13 Nonnormal distributions and implied volatility 209
13.1 Black–Scholes using alternative distributional assumptions 209
13.2 Implied volatility 211
13.3 Adapting for skewness and kurtosis 212
13.4 The volatility smile 215
13.5 Userdefined functions in Module1 217
Summary 219
References 220
Part Four Options on Bonds 221
14 Introduction to valuing options on bonds 223
14.1 The term structure of interest rates 224
14.2 Cash flows for coupon bonds and yield to maturity 225
14.3 Binomial trees 226
14.4 Black’s bond option valuation formula 227
14.5 Duration and convexity 228
14.6 Notation 230
Summary 230
References 230
15 Interest rate models 231
15.1 Vasicek’s term structure model 231
15.2 Valuing European options on zerocoupon bonds, Vasicek’s model 234
15.3 Valuing European options on coupon bonds, Vasicek’s model 235
15.4 CIR term structure model 236
15.5 Valuing European options on zerocoupon bonds, CIR model 237
15.6 Valuing European options on coupon bonds, CIR model 238
15.7 Userdefined functions in Module1 239
Summary 240
References 241
16 Matching the term structure 243
16.1 Trees with lognormally distributed interest rates 243
16.2 Trees with normal interest rates 246
16.3 The Black, Derman and Toy tree 247
16.4 Valuing bond options using BDT trees 248
16.5 Userdefined functions in Module1 250
Summary 252
References 252
Appendix Other VBA functions 253
Forecasting 253
ARIMA modelling 254
Splines 256
Eigenvalues and eigenvectors 257
References 258
Index 259
Author Information
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 Millennium Book: A Century of Investment Returns (2000).
The Wiley Advantage
 Unlike other existing books on Excel and VBA, this book is specifically applied to the financial markets.
 The material is based on elective courses given to London Business School MBA students.