About the Book

Too often, books on financial computing provide only quick-and-dirty implementations of financial models that have very little use in real-world applications. Professional Financial Computing Using Excel and VBA aims at providing real-world implementations of financial models that are robust, reusable and flexible. The book covers a wide range of financial models in the areas of derivatives pricings, market and credit risk modeling and advanced interest rate modeling. It first takes an in-depth look at how to implement financial models using both Excel 2007 and VBA and discusses the essential programming practices and skills in structuring complex financial models through advanced VBA features. In each of the later chapters on model implementations, it starts with a review on all the necessary financial theories and concepts from a practitioner's perspective.

About the Author

Donny C. F. Lai has 20 years’ experience in information technology, including e-commerce and e-finance technologies. He taught information system and VBA programming for computer science and financial engineering students at City University of Hong Kong. Dr. Humphrey K. K. Tung has 15-year experience in quantitative analysis for investment banking and risk consulting. He is a Visiting Assistant Professor of City University of Hong Kong, teaching mainly financial engineering courses. Dr. Michael C. S. Wong is the founding President of CTRISKS, a credit rating agency and risk consulting firm based in the Greater China region and an Associate Professor of Finance of City University of Hong Kong. He has advised more than 20 Asian banks on risk process reengineering, risk system development and risk management strategies. Stephen Ng has extensive experience in the field of quantitative investment and risk management. He conducted quantitative research in credit, FX and coordinating risk management initiatives in the Asia Pacific region for ING Investment Management. In the USA, he worked at Diversified Credit Investments; a hedge fund specialized in credit product investment.

Table of Contents:
Preface Chapter 1 Financial Engineering and Computing 1.1 Financial Engineering and Spreadsheet Modeling 1.2 Lehman Brothers' Products for Retail Investors 1.3 Risk Management and Basel II 1.4 About the Book 1.5. Chapter Highlights 1.6 Other Remarks Chapter 2 The GARCH (1, 1) Model 2.1. The Model 2.2. Excel Implementation 2.3. Excel plus VBA Implementation Chapter 3 Finite Difference Methods 3.1. Difference Equations 3.2. Excel Implementation 3.3. VBA Implementation 3.4. Crank--Nicholson Scheme Chapter 4 Portfolio Mean-Variance Optimization 4.1. Portfolio Selection 4.2. Excel Implementation 4.3. Excel plus VBA Implementation Chapter 5 Newton--Raphson Method 5.1. Newton--Raphson Method for Systems of Equations 5.2. VBA Routine Chapter 6 Yield Curve Construction Using Cubic Spline 6.1. Cubic Spline Interpolation 6.2. Yield Curve Construction 6.3. Excel plus VBA Implementation Chapter 7 Binomial Option Pricing Model 7.1. Risk-Neutral Option Pricing and the Binomial Tree 7.2. VBA Implementation Chapter 8 The Black--Derman--Toy Model 8.1. The Term Structure Model and the Black--Derman--Toy Tree 8.2. Excel plus VBA Implementation Chapter 9 Monte Carlo Option Pricing 9.1. The Monte Carlo Method 9.2. Risk-Neutral Valuation 9.3. VBA Implementation 9.4. Exotic Options 9.5. American Options Chapter 10 Portfolio Value-at-Risk 10.1. Portfolio Risk Simulation 10.2. Monte Carlo Simulation for Multiple-Asset Portfolios 10.3. Historical Simulation for Multiple-Asset Portfolios 10.4. VBA Implementation of Portfolio Risk Simulation 10.5. Drill Down of Portfolio Risk Chapter 11 The Hull--White Mode 11.1. Hull--White Trinomial Tree 11.2. Excel plus VBA Implementation 11.3. The General Hull--White Model 11.4. Implementation of the General Hull--White Model Chapter 12 Credit Metrics Model 12.1. The Credit Metrics Model 12.2. Individual (Segregate) Asset Valuation Framework 12.3 Monte Carlo Simulation in Detail 12.4. Excel and VBA Implementation Chapter 13 KMV--Merton Model 13.1. KMV--Merton Model of Credit Risk 13.2. Excel and VBA Implementation Appendix A VBA Programming A.1 Introduction A.2 A Brief History of VBA A.3 Essential Excel Elements for VBA A.3.1 Excel Cell Reference A.3.2 Excel Defined Names A.3.3 Excel Worksheet Functions A.4 The VBA Development Environment (VBE) A.4.1 The Developer Tab in the Ribbon A.4.2 The Windows of VBE A.4.3 The Project Explorer A.4.4 The VBA Project Structure A.4.5 The Procedure to Create a VBA Subroutine A.4.6 The Procedure to Create a VBA Function A.5 Basic VBA Programming Concepts A.5.1 Variables and Data Types A.5.2 Declaration and Assignment Statements A.5.3 Flow Control Statements A.6 VBA Arrays A.7 Using Worksheet Matrix Functions in VBA A.8 Summary Appendix B The Excel Object Model Appendix C VBA Debugging Tools Appendix D Summary of VBA Operators Appendix E Summary of VBA Functions Appendix F Summary of VBA Statements Appendix G Excel Array Formula Index