Professional Financial Computing Using Excel and VBA

Often financial computing guidebooks provide only quick-and-dirty implementations of financial models, rarely related to real-world applications. Professional Financial Computing Using Excel and VBA provides reusable, flexible, real-world implementations of financial models. The book explores financial models, like derivatives pricings, market and credit risk modeling, and advanced interest rate modeling. With step-by-step instructions, this resource reviews fundamental financial theories and concepts, as well as alternative approaches to ensure a comprehensive understanding of the different techniques. This text is an ideal reference for graduate students studying financial engineering and computing.

Table of Contents:
Preface ix CHAPTER 1 Financial Engineering and Computing 1 1.1 Financial Engineering and Spreadsheet Modeling 1 1.2 Lehman Brothers' Products for Retail Investors 3 1.3 Risk Management and Basel II 4 1.4 About the Book 4 1.5. Chapter Highlights 6 1.6 Other Remarks 7 CHAPTER 2 The GARCH(1,1) Model 9 2.1. The Model 9 2.2. Excel Implementation 10 2.3. Excel Plus VBA Implementation 15 CHAPTER 3 Finite Difference Methods 21 3.1. Difference Equations 21 3.2. Excel Implementation 24 3.3. VBA Implementation 28 3.4. Crank Nicholson Scheme 33 CHAPTER 4 Portfolio Mean-Variance Optimization 37 4.1. Portfolio Selection 37 4.2. Excel Implementation 42 4.3. Excel Plus VBA Implementation 48 CHAPTER 5 Newton Raphson Method 59 5.1. Newton Raphson Method for Systems of Equations 59 5.2. VBA Routine 61 CHAPTER 6 Yield Curve Construction Using Cubic Spline 67 6.1. Cubic Spline Interpolation 67 6.2. Yield Curve Construction 75 6.3. Excel Plus VBA Implementation 77 CHAPTER 7 Binomial Option Pricing Model 85 7.1. Risk-Neutral Option Pricing and the Binomial Tree 85 7.2. VBA Implementation 89 CHAPTER 8 The Black Derman Toy Model 95 8.1. The Term Structure Model and the Black Derman Toy Tree 95 8.2. Excel Plus VBA Implementation 98 CHAPTER 9 Monte Carlo Option Pricing 109 9.1. TheMonte Carlo Method 109 9.2. Risk-Neutral Valuation 112 9.3. VBA Implementation 114 9.4. Exotic Options 124 9.5. American Options 132 CHAPTER 10 Portfolio Value-at-Risk 143 10.1. Portfolio Risk Simulation 143 10.2. Monte Carlo Simulation for Multiple-Asset Portfolios 152 10.3. Historical Simulation for Multiple-Asset Portfolios 160 10.4. VBA Implementation of Portfolio Risk Simulation 164 10.5. Drill Down of Portfolio Risk 180 CHAPTER 11 The Hull White Model 189 11.1. Hull White Trinomial Tree 189 11.2. Excel Plus VBA Implementation 196 11.3. The General Hull White Model 203 11.4. Implementation of the General Hull White Model 210 CHAPTER 12 CreditMetrics Model 221 12.1. The CreditMetrics Model 221 12.2. Individual (Segregate) Asset Valuation Framework 221 12.3 Monte Carlo Simulation in Detail 225 12.4. Excel and VBA Implementation 227 CHAPTER 13 KMV Merton Model 243 13.1. KMV Merton Model of Credit Risk 243 13.2. Excel and VBA Implementation 248 APPENDIX A VBA Programming 255 A.1 Introduction 255 A.2 A Brief History of VBA 255 A.3 Essential Excel Elements for VBA 256 A.3.1 Excel Cell Reference 257 A.3.2 Excel Defined Names 261 A.3.3 Excel Worksheet Functions 264 A.4 The VBA Development Environment (VBE) 266 A.4.1 The Developer Tab in the Ribbon 266 A.4.2 The Windows of VBE 268 A.4.3 The Project Explorer 272 A.4.4 The VBA Project Structure 273 A.4.5 The Procedure to Create a VBA Subroutine 275 A.4.6 The Procedure to Create a VBA Function 278 A.5 Basic VBA Programming Concepts 280 A.5.1 Variables and Data Types 285 A.5.2 Declaration and Assignment Statements 287 A.5.3 Flow Control Statements 293 A.6 VBA Arrays 300 A.7 Using Worksheet Matrix Functions in VBA 304 A.8 Summary 311 APPENDIX B The Excel Object Model 315 APPENDIX C VBA Debugging Tools 321 APPENDIX D Summary of VBA Operators 327 APPENDIX E Summary of VBA Functions 331 APPENDIX F Summary of VBA Statements 333 APPENDIX G Excel Array Formula 341 Index 349