Professional Financial Computing Using Excel and VBA

by Lai, Donny C. F.; Tung, Humphrey K. K.; Wong, Michael C. S.; Ng, Stephen

• ISBN13: 9780470824399
• ISBN10: 0470824395
• Edition: 1st
• Format: Hardcover
• Copyright: 2010-06-15
• Publisher: Wiley

Summary

Professional Financial Computing Using Excel and VBA is an admirable exposition that bridges the theoretical underpinnings of financial engineering and its application which usually appears as a "black-box" software application. The book opens the black-box and reveals the architecture of risk-modeling and financial engineering based on industry-standard stochastic models by utilizing Excel and VBA functionality to create a robust and practical modeling tool-kit. Financial engineering professionals who purchase this book will have a jumpstart advantage for their customized financial engineering and modeling needs.Dr. Cameron Wicentowich Vice President, Treasury Analytics Canadian Imperial Bank of Commerce (CIBC)Spreadsheet modeling for finance has become a standard course in the curriculum of many Quantitative Finance programs since the Excel-based Visual Basic programming is now widely used in constructing optimal portfolios, pricing structured products and managing risks. Professional Financial Computing Using Excel and VBA is written by a unique team of finance, physics and computer academics and practitioners. It is a good reference for those who are studying for a Masters degree in Financial Engineering and Risk Management. It can also be useful for financial engineers to jump-start a project on designing structured products, modeling interest term structure or credit risks.Dr. Jin Zhang Director of Master of Finance Program and Associate Professor The University of Hong KongExcel has been one of the most powerful tools for financial planning and computing over the last few years. Most users utilize a fraction of its capabilities. One of the reasons is the limited availability of books that cover the advanced features of Excel for Finance. Professional Financial Computing Using Excel and VBA goes the extra mile and deals with the Excel tools many professionals call for. This book is a must for professionals or students dealing with financial engineering, financial risk management, computational finance or mathematical finance. I loved the way the authors covered the material using real life, hands-on examples.Dr. Isaac Gottlieb Temple University Author, Next Generation Excel: Modeling in Excel for Analysts and MBAs

Author Biography

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 CATS, 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 banks on market risk management, credit risk management, Basel II credit ratings systems and due diligence for wealth management services. He served Committee of Global Association of Risk Professionals (GARP) in 1998-2002 and trained more than 6000 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 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.

Table of Contents

Preface	p. ix
Financial Engineering and Computing	p. 1
Financial Engineering and Spreadsheet Modeling	p. 1
Lehman Brothers' Products for Retail Investors	p. 3
Risk Management and Basel II	p. 4
About the Book	p. 4
Chapter Highlights	p. 6
Other Remarks	p. 7
The GARCH(1,1) Model	p. 9
The Model	p. 9
Excel Implementation	p. 10
Excel Plus VBA Implementation	p. 15
Finite Difference Methods	p. 21
Difference Equations	p. 21
Excel Implementation	p. 24
VBA Implementation	p. 28
Crank-Nicholson Scheme	p. 33
Portfolio Mean-Variance Optimization	p. 37
Portfolio Selection	p. 37
Excel Implementation	p. 42
Excel Plus VBA Implementation	p. 48
Newton-Raphson Method	p. 59
Newton-Raphson Method for Systems of Equations	p. 59
VBA Routine	p. 61
Yield Curve Construction Using Cubic Spline	p. 67
Cubic Spline Interpolation	p. 67
Yield Curve Construction	p. 75
Excel Plus VBA Implementation	p. 77
Binomial Option Pricing Model	p. 85
Risk-Neutral Option Pricing and the Binomial Tree	p. 85
VBA Implementation	p. 89
The Black-Derman-Toy Model	p. 95
The Term Structure Model and the Black-Derman-Toy Tree	p. 95
Excel Plus VBA Implementation	p. 98
Monte Carlo Option Pricing	p. 109
The Monte Carlo Method	p. 109
Risk-Neutral Valuation	p. 112
VBA Implementation	p. 114
Exotic Options	p. 124
American Options	p. 132
Portfolio Value-at-Risk	p. 143
Portfolio Risk Simulation	p. 143
Monte Carlo Simulation for Multiple-Asset Portfolios	p. 152
Historical Simulation for Multiple-Asset Portfolios	p. 160
VBA Implementation of Portfolio Risk Simulation	p. 164
Drill Down of Portfolio Risk	p. 180
The Hull-White Model	p. 189
Hull-White Trinomial Tree	p. 189
Excel Plus VBA Implementation	p. 196
The General Hull-White Model	p. 203
Implementation of the General Hull-White Model	p. 210
CreditMetrics Model	p. 221
The CreditMetrics Model	p. 221
Individual (Segregate) Asset Valuation Framework	p. 221
Monte Carlo Simulation in Detail	p. 225
Excel and VBA Implementation	p. 227
KMV-Merton Model	p. 243
KMV-Merton Model of Credit Risk	p. 243
Excel and VBA Implementation	p. 248
VBA Programming	p. 255
Introduction	p. 255
A Brief History of VBA	p. 255
Essential Excel Elements for VBA	p. 256
Excel Cell Reference	p. 257
Excel Defined Names	p. 261
Excel Worksheet Functions	p. 264
The VBA Development Environment (VBE)	p. 266
The Developer Tab in the Ribbon	p. 266
The Windows of VBE	p. 268
The Project Explorer	p. 272
The VBA Project Structure	p. 273
The Procedure to Create a VBA Subroutine	p. 275
The Procedure to Create a VBA Function	p. 278
Basic VBA Programming Concepts	p. 280
Variables and Data Types	p. 285
Declaration and Assignment Statements	p. 287
Flow Control Statements	p. 293
VBA Arrays	p. 300
Using Worksheet Matrix Functions in VBA	p. 304
Summary	p. 311
The Excel Object Model	p. 315
VBA Debugging Tools	p. 321
Summary of VBA Operators	p. 327
Summary of VBA Functions	p. 331
Summary of VBA Statements	p. 333
Excel Array Formula	p. 341
Index	p. 349
Table of Contents provided by Ingram. All Rights Reserved.