Advanced Modelling in Finance using Excel and VBA (eBook)

Mary Jackson, Mike Staunton (Autoren)

eBook Download: PDF

2006
John Wiley & Sons (Verlag)
978-0-470-06166-4 (ISBN)

Infos

This new and unique book demonstrates that Excel and VBA can play an important role in the explanation and implementation of numerical methods across finance. Advanced Modelling in Finance provides a comprehensive look at equities, options on equities and options on bonds from the early 1950s to the late 1990s.

The book adopts a step-by-step 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: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

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).

Inhalt

Cover 7
Title Page 5
Copyright 6
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’s 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’s 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 ‘relative references’ 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–return representation of portfolios 119
6.3 Using Solver to find efficient points 120
6.4 Generating the efficient frontier (Huang and Litzenberger’s approach) 123
6.5 Constrained frontier portfolios 125
6.6 Combining risk-free and risky assets 127
6.7 Problem One–combining a risk-free asset with a risky asset 128
6.8 Problem Two–combining two risky assets 129
6.9 Problem Three–combining 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–covariance 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–passive 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–Scholes formula 172
9.2 The Black–Scholes 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–call 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–Scholes 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–Scholes formula 199
11.1 The Black–Scholes formula 199
11.2 Black–Scholes formula in the spreadsheet 200
11.3 Options on currencies and commodities 201
11.4 Calculating the option’s ‘greek’ parameters 203
11.5 Hedge portfolios 204
11.6 Formal derivation of the Black–Scholes 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–Scholes 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’s 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’s term structure model 245
15.2 Valuing European options on zero-coupon bonds, Vasicek’s model 248
15.3 Valuing European options on coupon bonds, Vasicek’s 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
EULA 278

Rezensionen

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

Produkt-Details

• Erscheint lt. Verlag: 30.8.2006
• Reihe/Serie: The Wiley Finance Series
• Reihe/Serie: Wiley Finance Series
• Sprache: englisch
• Themenwelt: Informatik ► Office Programme ► Excel
• Themenwelt: Recht / Steuern ► Wirtschaftsrecht
• Themenwelt: Wirtschaft ► Betriebswirtschaft / Management ► Finanzierung
• Schlagworte: accompanied • Approach • asset • Book • Capital • Elementary • Excel • Excel spreadsheets • explanations • Finance • Finance & Investments • Finanz- u. Anlagewesen • Finanzwirtschaft • Frontiers • Functions • graduate • Knowledge • Material • Mathematical • Modellbildung • Modelling • Practitioners • Pricing • Programs • source • stepbystep • students • VBA • Visual Basic für Applikationen • Visual BASIC für Applikationen
• ISBN-10: 0-470-06166-9 / 0470061669
• ISBN-13: 978-0-470-06166-4 / 9780470061664