Advanced modelling in finance using Excel and VBA

Advanced Modelling in Finance using Excel and VBA......Page 4
Contents......Page 8
Preface......Page 14
Acknowledgements......Page 15
1.1 Finance insights......Page 16
1.4 Numerical methods......Page 17
1.6 Topics covered......Page 18
1.8 Comments and suggestions......Page 20
Part One Advanced Modelling in Excel......Page 22
2.1 Accessing functions in Excel......Page 24
2.2 Mathematical functions......Page 25
2.3.1 Using the frequency function......Page 27
2.3.2 Using the quartile function......Page 29
2.3.3 Using Excel’s normal functions......Page 30
2.4 Lookup functions......Page 31
2.5 Other functions......Page 33
2.6 Auditing tools......Page 34
2.7.1 Setting up Data Tables with one input......Page 35
2.7.2 Setting up Data Tables with two inputs......Page 37
2.8 XY charts......Page 38
2.9 Access to Data Analysis and Solver......Page 41
2.10 Using range names......Page 42
2.11 Regression......Page 43
2.12 Goal Seek......Page 46
2.13.2 Transposing a matrix......Page 48
2.13.4 Multiplying matrices......Page 49
2.13.5 Matrix inversion......Page 50
2.13.6 Solving systems of simultaneous linear equations......Page 51
Summary......Page 52
3.1 Advantages of mastering VBA......Page 54
3.2 Object-oriented aspects of VBA......Page 55
3.3.1 Some simple examples of VBA subroutines......Page 57
3.3.2 MsgBox for interaction......Page 58
3.3.4 Entering code and executing macros......Page 59
3.3.5 Recording keystrokes and editing code......Page 60
3.4 Elements of programming......Page 62
3.4.2 VBA array variables......Page 63
3.4.3 Control structures......Page 65
3.4.4 Control of repeating procedures......Page 66
3.4.5 Using Excel functions and VBA functions in code......Page 67
3.5 Communicating between macros and the spreadsheet......Page 68
3.6.1 Charts......Page 71
3.6.2 Normal probability plot......Page 74
3.6.3 Generating the efficient frontier with Solver......Page 76
Appendix 3A The Visual Basic Editor......Page 80
Stepping through a macro and using other debug tools......Page 83
Appendix 3B Recording keystrokes in ‘relative references’ mode......Page 84
4.1 A simple sales commission function......Page 88
4.2 Creating Commission(Sales) in the spreadsheet......Page 89
4.3 Two functions with multiple inputs for valuing options......Page 90
4.4 Manipulating arrays in VBA......Page 93
4.5 Expected value and variance functions with array inputs......Page 94
4.6 Portfolio variance function with array inputs......Page 96
4.7 Functions with array output......Page 99
4.8.1 Using VBA functions in user-defined functions......Page 100
4.9 Pros and cons of developing VBA functions......Page 101
Summary......Page 102
Appendix 4A Functions illustrating array handling......Page 103
Appendix 4B Binomial tree option valuation functions......Page 104
Exercises on writing functions......Page 109
Solution notes for exercises on functions......Page 110
Part Two Equities......Page 114
5 Introduction to equities......Page 116
6.1 Portfolio mean and variance......Page 118
6.2 Risk–return representation of portfolios......Page 120
6.3 Using Solver to find efficient points......Page 121
6.4 Generating the efficient frontier (Huang and Litzenberger’s approach)......Page 124
6.5 Constrained frontier portfolios......Page 126
6.6 Combining risk-free and risky assets......Page 128
6.7 Problem One–combining a risk-free asset with a risky asset......Page 129
6.8 Problem Two–combining two risky assets......Page 130
6.9 Problem Three–combining a risk-free asset with a risky portfolio......Page 132
6.10 User-defined functions in Module1......Page 134
6.11 Functions for the three generic portfolio problems in Module1......Page 135
6.12 Macros in ModuleM......Page 136
References......Page 138
7.1 The single-index model......Page 140
7.2 Estimating beta coefficients......Page 141
7.3 The capital asset pricing model......Page 144
7.4 Variance–covariance matrices......Page 145
7.5 Value-at-Risk......Page 146
7.6 Horizon wealth......Page 149
7.8 User-defined functions in Module1......Page 151
References......Page 153
8 Performance measurement and attribution......Page 154
8.1 Conventional performance measurement......Page 155
8.2 Active–passive management......Page 156
8.3 Introduction to style analysis......Page 159
8.4 Simple style analysis......Page 160
8.5 Rolling-period style analysis......Page 161
8.6 Confidence intervals for style weights......Page 163
8.8 Macros in ModuleM......Page 166
Summary......Page 167
References......Page 168
Part Three Options on Equities......Page 170
9 Introduction to options on equities......Page 172
9.2 The Black–Scholes formula......Page 173
9.3 Hedge portfolios......Page 174
9.4 Risk-neutral valuation......Page 176
9.5 A simple one-step binomial tree with risk-neutral valuation......Page 177
9.7 Dividends......Page 178
9.9 Numerical methods......Page 179
Summary......Page 180
References......Page 181
10.1 Introduction to binomial trees......Page 182
10.2 A simplified binomial tree......Page 183
10.3 The Jarrow and Rudd binomial tree......Page 185
10.4 The Cox, Ross and Rubinstein tree......Page 188
10.5 Binomial approximations and Black–Scholes formula......Page 190
10.6 Convergence of CRR binomial trees......Page 191
10.7 The Leisen and Reimer tree......Page 192
10.8 Comparison of CRR and LR trees......Page 193
10.9 American options and the CRR American tree......Page 195
10.10 User-defined functions in Module0 and Module1......Page 197
Summary......Page 198
References......Page 199
11.1 The Black–Scholes formula......Page 200
11.2 Black–Scholes formula in the spreadsheet......Page 201
11.3 Options on currencies and commodities......Page 202
11.4 Calculating the option’s ‘greek’ parameters......Page 204
11.5 Hedge portfolios......Page 205
11.6 Formal derivation of the Black–Scholes formula......Page 207
11.7 User-defined functions in Module1......Page 209
Summary......Page 210
References......Page 211
12.1 Introduction to Monte Carlo simulation......Page 212
12.2 Simulation with antithetic variables......Page 214
12.3 Simulation with quasi-random sampling......Page 215
12.4 Comparing simulation methods......Page 217
12.6 Numerical integration......Page 218
12.7 User-defined functions in Module1......Page 220
References......Page 222
13.1 Black–Scholes using alternative distributional assumptions......Page 224
13.2 Implied volatility......Page 226
13.3 Adapting for skewness and kurtosis......Page 227
13.4 The volatility smile......Page 230
13.5 User-defined functions in Module1......Page 232
Summary......Page 234
References......Page 235
Part Four Options on Bonds......Page 237
14 Introduction to valuing options on bonds......Page 238
14.1 The term structure of interest rates......Page 239
14.2 Cash Flows for coupon bonds and yield to maturity......Page 240
14.3 Binomial trees......Page 241
14.4 Black’s bond option valuation formula......Page 242
14.5 Duration and convexity......Page 243
References......Page 245
15.1 Vasicek’s term structure model......Page 246
15.2 Valuing European options on zero-coupon bonds, Vasicek’s model......Page 249
15.3 Valuing European options on coupon bonds, Vasicek’s model......Page 250
15.4 CIR term structure model......Page 251
15.5 Valuing European options on zero-coupon bonds, CIR model......Page 252
15.6 Valuing European options on coupon bonds, CIR model......Page 253
15.7 User-defined functions in Module1......Page 254
Summary......Page 255
References......Page 256
16.1 Trees with lognormally distributed interest rates......Page 258
16.2 Trees with normal interest rates......Page 261
16.3 The Black, Derman and Toy tree......Page 262
16.4 Valuing bond options using BDT trees......Page 263
16.5 User-defined functions in Module1......Page 265
References......Page 267
Forecasting......Page 268
ARIMA modelling......Page 269
Splines......Page 271
Eigenvalues and eigenvectors......Page 272
References......Page 273
Index......Page 274