Advanced modelling in finance using Excel and VBA /

Contents 7
Preface 13
Acknowledgements 14
1 Introduction 15
1.1 Finance insights 15
1.2 Asset price assumptions 16
1.3 Mathematical and statistical problems 16
1.4 Numerical methods 16
1.5 Excel solutions 17
1.6 Topics covered 17
1.7 Related Excel workbooks 19
1.8 Comments and suggestions 19
Part One Advanced Modelling in Excel 21
2 Advanced Excel functions and procedures 23
2.1 Accessing functions in Excel 23
2.2 Mathematical functions 24
2.3 Statistical functions 26
2.3.1 Using the frequency function 26
2.3.2 Using the quartile function 28
2.3.3 Using Excel\u2019s normal functions 29
2.4 Lookup functions 30
2.5 Other functions 32
2.6 Auditing tools 33
2.7 Data Tables 34
2.7.1 Setting up Data Tables with one input 34
2.7.2 Setting up Data Tables with two inputs 36
2.8 XY charts 37
2.9 Access to Data Analysis and Solver 40
2.10 Using range names 41
2.11 Regression 42
2.12 Goal Seek 45
2.13 Matrix algebra and related functions 47
2.13.1 Introduction to matrices 47
2.13.2 Transposing a matrix 47
2.13.3 Adding matrices 48
2.13.4 Multiplying matrices 48
2.13.5 Matrix inversion 49
2.13.6 Solving systems of simultaneous linear equations 50
2.13.7 Summary of Excel\u2019s matrix functions 51
Summary 51
3 Introduction to VBA 53
3.1 Advantages of mastering VBA 53
3.2 Object-oriented aspects of VBA 54
3.3 Starting to write VBA macros 56
3.3.1 Some simple examples of VBA subroutines 56
3.3.2 MsgBox for interaction 57
3.3.3 The writing environment 58
3.3.4 Entering code and executing macros 58
3.3.5 Recording keystrokes and editing code 59
3.4 Elements of programming 61
3.4.1 Variables and data types 62
3.4.2 VBA array variables 62
3.4.3 Control structures 64
3.4.4 Control of repeating procedures 65
3.4.5 Using Excel functions and VBA functions in code 66
3.4.6 General points on programming 67
3.5 Communicating between macros and the spreadsheet 67
3.6 Subroutine examples 70
3.6.1 Charts 70
3.6.2 Normal probability plot 73
3.6.3 Generating the efficient frontier with Solver 75
Summary 79
References 79
Appendix 3A The Visual Basic Editor 79
Stepping through a macro and using other debug tools 82
Appendix 3B Recording keystrokes in \u2018relative references\u2019 mode 83
4 Writing VBA user-defined functions 87
4.1 A simple sales commission function 87
4.2 Creating Commission(Sales) in the spreadsheet 88
4.3 Two functions with multiple inputs for valuing options 89
4.4 Manipulating arrays in VBA 92
4.5 Expected value and variance functions with array inputs 93
4.6 Portfolio variance function with array inputs 95
4.7 Functions with array output 98
4.8 Using Excel and VBA functions in user-defined functions 99
4.8.1 Using VBA functions in user-defined functions 99
4.8.2 Add-ins 100
4.9 Pros and cons of developing VBA functions 100
Summary 101
Appendix 4A Functions illustrating array handling 102
Appendix 4B Binomial tree option valuation functions 103
Exercises on writing functions 108
Solution notes for exercises on functions 109
Part Two Equities 113
5 Introduction to equities 115
6 Portfolio optimisation 117
6.1 Portfolio mean and variance 117
6.2 Risk\u2013return representation of portfolios 119
6.3 Using Solver to find efficient points 120
6.4 Generating the efficient frontier (Huang and Litzenberger\u2019s approach) 123
6.5 Constrained frontier portfolios 125
6.6 Combining risk-free and risky assets 127
6.7 Problem One\u2013combining a risk-free asset with a risky asset 128
6.8 Problem Two\u2013combining two risky assets 129
6.9 Problem Three\u2013combining a risk-free asset with a risky portfolio 131
6.10 User-defined functions in Module1 133
6.11 Functions for the three generic portfolio problems in Module1 134
6.12 Macros in ModuleM 135
Summary 137
References 137
7 Asset pricing 139
7.1 The single-index model 139
7.2 Estimating beta coefficients 140
7.3 The capital asset pricing model 143
7.4 Variance\u2013covariance matrices 144
7.5 Value-at-Risk 145
7.6 Horizon wealth 148
7.7 Moments of related distributions such as normal and lognormal 150
7.8 User-defined functions in Module1 150
Summary 152
References 152
8 Performance measurement and attribution 153
8.1 Conventional performance measurement 154
8.2 Active\u2013passive management 155
8.3 Introduction to style analysis 158
8.4 Simple style analysis 159
8.5 Rolling-period style analysis 160
8.6 Confidence intervals for style weights 162
8.7 User-defined functions in Module1 165
8.8 Macros in ModuleM 165
Summary 166
References 167
Part Three Options on Equities 169
9 Introduction to options on equities 171
9.1 The genesis of the Black\u2013Scholes formula 172
9.2 The Black\u2013Scholes formula 172
9.3 Hedge portfolios 173
9.4 Risk-neutral valuation 175
9.5 A simple one-step binomial tree with risk-neutral valuation 176
9.6 Put\u2013call parity 177
9.7 Dividends 177
9.8 American features 178
9.9 Numerical methods 178
9.10 Volatility and non-normal share returns 179
Summary 179
References 180
10 Binomial trees 181
10.1 Introduction to binomial trees 181
10.2 A simplified binomial tree 182
10.3 The Jarrow and Rudd binomial tree 184
10.4 The Cox, Ross and Rubinstein tree 187
10.5 Binomial approximations and Black\u2013Scholes formula 189
10.6 Convergence of CRR binomial trees 190
10.7 The Leisen and Reimer tree 191
10.8 Comparison of CRR and LR trees 192
10.9 American options and the CRR American tree 194
10.10 User-defined functions in Module0 and Module1 196
Summary 197
References 198
11 The Black\u2013Scholes formula 199
11.1 The Black\u2013Scholes formula 199
11.2 Black\u2013Scholes formula in the spreadsheet 200
11.3 Options on currencies and commodities 201
11.4 Calculating the option\u2019s \u2018greek\u2019 parameters 203
11.5 Hedge portfolios 204
11.6 Formal derivation of the Black\u2013Scholes formula 206
11.7 User-defined functions in Module1 208
Summary 209
References 210
12 Other numerical methods for European options 211
12.1 Introduction to Monte Carlo simulation 211
12.2 Simulation with antithetic variables 213
12.3 Simulation with quasi-random sampling 214
12.4 Comparing simulation methods 216
12.5 Calculating greeks in Monte Carlo simulation 217
12.6 Numerical integration 217
12.7 User-defined functions in Module1 219
Summary 221
References 221
13 Non-normal distributions and implied volatility 223
13.1 Black\u2013Scholes using alternative distributional assumptions 223
13.2 Implied volatility 225
13.3 Adapting for skewness and kurtosis 226
13.4 The volatility smile 229
13.5 User-defined functions in Module1 231
Summary 233
References 234
Part Four Options on Bonds 236
14 Introduction to valuing options on bonds 237
14.1 The term structure of interest rates 238
14.2 Cash Flows for coupon bonds and yield to maturity 239
14.3 Binomial trees 240
14.4 Black\u2019s bond option valuation formula 241
14.5 Duration and convexity 242
14.6 Notation 244
Summary 244
References 244
15 Interest rate models 245
15.1 Vasicek\u2019s term structure model 245
15.2 Valuing European options on zero-coupon bonds, Vasicek\u2019s model 248
15.3 Valuing European options on coupon bonds, Vasicek\u2019s model 249
15.4 CIR term structure model 250
15.5 Valuing European options on zero-coupon bonds, CIR model 251
15.6 Valuing European options on coupon bonds, CIR model 252
15.7 User-defined functions in Module1 253
Summary 254
References 255
16 Matching the term structure 257
16.1 Trees with lognormally distributed interest rates 257
16.2 Trees with normal interest rates 260
16.3 The Black, Derman and Toy tree 261
16.4 Valuing bond options using BDT trees 262
16.5 User-defined functions in Module1 264
Summary 266
References 266
Appendix Other VBA functions 267
Forecasting 267
ARIMA modelling 268
Splines 270
Eigenvalues and eigenvectors 271
References 272
Index 273