Professional Financial Computing Using Excel and VBA

Dr. Humphrey K. K. Tung received his BSc in Physics from the University of Alberta, both MSc and PhD in Theoretical Particle Physics from the University of Toronto. He was a quantitative analyst of C.ATS, a leading risk management software vendor in Silicon Valley. He is now a Visiting Assistant Professor in the Department of Economics and Finance of the City University of Hong Kong and has taught the option pricing and implementation for financial engineering program since 2003. Mr. Donny Lai is proficient in information systems development, IT project management, and applied finance. He has worked in the IT industry for over 20 years and received his Master Degree of Applied Finance from the University of Western Sydney, Australia. With his profound experience in e-commerce and e-finance, he is teaching in the department of Computer Science, City University of Hong Kong and has taught programming, data analysis, and spreading modeling since 2005. His current research interests include advanced web technologies, mobile computing, and financial computing.Dr. Michael Wong advised more than 20 banks on market risk management, credit risk management, Basel II credit ratings systems and due diligence for wealth management services. He served as a founding member of FRM Committee of Global Association of Risk Professionals (GARP) in 1998-2002 and trained more than 6,000 chief risk officers, senior risk managers and bank regulators in Hong Kong, Taiwan, China, Korea, Singapore, Malaysia, and Macau. He founded CTRISKS (www.ctrisks.com), an Asia-based credit rating agency and risk consulting firm. Dr. Wong has published more than 50 journal articles and book chapters, and authored four professional books. He is listed in Risk Who's Who, and awarded both Teaching Excellence Award and Best Doctoral Dissertation Award.Stephen Ng is an executive director of Canadian Imperial Bank of Commerce, who is responsible for coordinating market risk management initiatives in the Asia Pacific region. Previously, he was a quantitative investment manager at ING Investment Management where he developed investment strategies and conducted quantitative research in FX, rates and credit. In addition, he worked at Diversified Credit Investments, Deutsche Bank and Morgan Stanley in the past. He earned his MS in Mathematical Finance from University of Southern California and his BA in Economics from University of California, Berkeley. He is also a CFA charterholder and a Certified Financial Risk Manager.

• Preface ixChapter 1 Financial Engineering and Computing 11.1 Financial Engineering and Spreadsheet Modeling 11.2 Lehman Brothers’ Products for Retail Investors 31.3 Risk Management and Basel II 41.4 About the Book 41.5. Chapter Highlights 61.6 Other Remarks 7Chapter 2 The GARCH(1,1) Model 92.1. The Model 92.2. Excel Implementation 102.3. Excel Plus VBA Implementation 15Chapter 3 Finite Difference Methods 213.1. Difference Equations 213.2. Excel Implementation 243.3. VBA Implementation 283.4. Crank–Nicholson Scheme 33Chapter 4 Portfolio Mean-Variance Optimization 374.1. Portfolio Selection 374.2. Excel Implementation 424.3. Excel Plus VBA Implementation 48Chapter 5 Newton–Raphson Method 595.1. Newton–Raphson Method for Systems of Equations 595.2. VBA Routine 61Chapter 6 Yield Curve Construction Using Cubic Spline 676.1. Cubic Spline Interpolation 676.2. Yield Curve Construction 756.3. Excel Plus VBA Implementation 77Chapter 7 Binomial Option Pricing Model 857.1. Risk-Neutral Option Pricing and the Binomial Tree 857.2. VBA Implementation 89Chapter 8 The Black–Derman–Toy Model 958.1. The Term Structure Model and the Black–Derman–Toy Tree 958.2. Excel Plus VBA Implementation 98Chapter 9 Monte Carlo Option Pricing 1099.1. The Monte Carlo Method 1099.2. Risk-Neutral Valuation 1129.3. VBA Implementation 1149.4. Exotic Options 1249.5. American Options 132Chapter 10 Portfolio Value-at-Risk 14310.1. Portfolio Risk Simulation 14310.2. Monte Carlo Simulation for Multiple-Asset Portfolios 15210.3. Historical Simulation for Multiple-Asset Portfolios 16010.4. VBA Implementation of Portfolio Risk Simulation 16410.5. Drill Down of Portfolio Risk 180Chapter 11 The Hull–White Model 18911.1. Hull–White Trinomial Tree 18911.2. Excel Plus VBA Implementation 19611.3. The General Hull–White Model 20311.4. Implementation of the General Hull–White Model 210Chapter 12 CreditMetrics Model 22112.1. The CreditMetrics Model 22112.2. Individual (Segregate) Asset Valuation Framework 22112.3 Monte Carlo Simulation in Detail 22512.4. Excel and VBA Implementation 227Chapter 13 KMV–Merton Model 24313.1. KMV–Merton Model of Credit Risk 24313.2. Excel and VBA Implementation 248Appendix A VBA Programming 255A.1 Introduction 255A.2 A Brief History of VBA 255A.3 Essential Excel Elements for VBA 256A.3.1 Excel Cell Reference 257A.3.2 Excel Defined Names 261A.3.3 Excel Worksheet Functions 264A.4 The VBA Development Environment (VBE) 266A.4.1 The Developer Tab in the Ribbon 266A.4.2 The Windows of VBE 268A.4.3 The Project Explorer 272A.4.4 The VBA Project Structure 273A.4.5 The Procedure to Create a VBA Subroutine 275A.4.6 The Procedure to Create a VBA Function 278A.5 Basic VBA Programming Concepts 280A.5.1 Variables and Data Types 285A.5.2 Declaration and Assignment Statements 287A.5.3 Flow Control Statements 293A.6 VBA Arrays 300A.7 Using Worksheet Matrix Functions in VBA 304A.8 Summary 311Appendix B The Excel Object Model 315Appendix C VBA Debugging Tools 321Appendix D Summary of VBA Operators 327Appendix E Summary of VBA Functions 331Appendix F Summary of VBA Statements 333Appendix G Excel Array Formula 341Index 349