Advanced Modelling in Finance using Excel and VBA

Advanced Modelling in Finance using Excel and VBA

Incl CD-ROM, Wiley Finance Series

Aus der Reihe

Fr. 119.00

inkl. gesetzl. MwSt.

Advanced Modelling in Finance using Excel and VBA

Ebenfalls verfügbar als:

Gebundenes Buch

Gebundenes Buch

ab Fr. 119.00
eBook

eBook

ab Fr. 75.00

Beschreibung

Details

Einband

Gebundene Ausgabe

Erscheinungsdatum

08.06.2001

Verlag

John Wiley & Sons Inc

Seitenzahl

264

Maße (L/B/H)

25/17.5/2 cm

Beschreibung

Rezension

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

Details

Einband

Gebundene Ausgabe

Erscheinungsdatum

08.06.2001

Verlag

John Wiley & Sons Inc

Seitenzahl

264

Maße (L/B/H)

25/17.5/2 cm

Gewicht

666 g

Auflage

1. Auflage

Sprache

Englisch

ISBN

978-0-471-49922-0

Weitere Bände von Wiley Finance Series

Unsere Kundinnen und Kunden meinen

0.0

0 Bewertungen

Informationen zu Bewertungen

Zur Abgabe einer Bewertung ist eine Anmeldung im Konto notwendig. Die Authentizität der Bewertungen wird von uns nicht überprüft. Wir behalten uns vor, Bewertungstexte, die unseren Richtlinien widersprechen, entsprechend zu kürzen oder zu löschen.

Verfassen Sie die erste Bewertung zu diesem Artikel

Helfen Sie anderen Kund*innen durch Ihre Meinung

Erste Bewertung verfassen

Unsere Kundinnen und Kunden meinen

0.0

0 Bewertungen filtern

  • Advanced Modelling in Finance using Excel and VBA
  • 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 Object-oriented 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 user-defined 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 user-defined functions 85

    4.8.1 Using VBA functions in user-defined functions 85

    4.8.2 Add-ins 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 risk-free and risky assets 113

    6.7 Problem One-combining a risk-free asset with a risky asset 114

    6.8 Problem Two-combining two risky assets 115

    6.9 Problem Three-combining a risk-free asset with a risky portfolio 117

    6.10 User-defined 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 single-index model 125

    7.2 Estimating beta coefficients 126

    7.3 The capital asset pricing model 129

    7.4 Variance-covariance matrices 130

    7.5 Value-at-Risk 131

    7.6 Horizon wealth 134

    7.7 Moments of related distributions such as normal and lognormal 136

    7.8 User-defined 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 Rolling-period style analysis 146

    8.6 Confidence intervals for style weights 148

    8.7 User-defined 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 Risk-neutral valuation 161

    9.5 A simple one-step binomial tree with risk-neutral 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 non-normal 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 User-defined 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 User-defined 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 quasi-random sampling 200

    12.4 Comparing simulation methods 202

    12.5 Calculating greeks in Monte Carlo simulation 203

    12.6 Numerical integration 203

    12.7 User-defined functions in Module1 205

    Summary 207

    References 207

    13 Non-normal 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 User-defined 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 zero-coupon 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 zero-coupon bonds, CIR model 237

    15.6 Valuing European options on coupon bonds, CIR model 238

    15.7 User-defined 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 User-defined 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