Professional Financial Computing Using Excel and VBA

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

• Publisher: Wiley
• Year: 2011
• Print ISBN: 9780470824399
• eBook ISBN: 9781118179086
• Edition: 1
• Topic: Business
• Subtopic: Finance
• Index: Business

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.

Chapter 1

Financial Engineering and Computing

1.1 Financial Engineering and Spreadsheet Modeling

“Spreadsheet Modeling for Finance” has long been a popular course in the MSc Financial Engineering program at the university we served in Hong Kong. The course is different from introductory Excel courses in financial management. It is an advanced course offered mainly to students with solid training in mathematical finance, option pricing, and risk modeling. Most of the students in the course have been designated a chartered financial analyst (CFA) or certified as a financial risk manager (FRM). The financial engineering program mainly recruits part-time students working in various financial institutions. There are around 40 to 60 new recruits each year. Many of them are derivatives traders, bank risk managers, bank IT specialists, fund managers, product structurers, bank regulators, and product auditors. In 1997–2008, the program trained more than 500 graduates. Most of them successfully applied the knowledge gained to their daily work.

Some may ask why no “quantitative analysts” are mentioned. Loosely speaking, these financial engineering graduates are quantitative analysts in nature. Strictly speaking, none of them carries the job title “quantitative analyst.” A global investment bank may have one or two quantitative analysts and/or financial engineers in Hong Kong. Given the presence of 15 global institutions, there are a maximum of 10 quantitative analyst job vacancies a year. This number cannot satisfy the continuous supply of financial engineering graduates every year. Although our graduates are not called quantitative analysts, their training in financial engineering did help their fast career development. Also, their quantitative skills have enabled Hong Kong to excel in financial services.

When we planned this book in 2007, the financial market in Hong Kong was very bullish. Many China initial public offering (IPO) deals were completed in Hong Kong. The Hang Seng Index reached over 30,000 points. Structured products and hedge funds were prevalent in corporate banking and private banking. Equity-linked notes, minibonds, and currency-linked products were common in retail banking.

In addition to sizable financial institutions, Hong Kong is a hub of boutique hedge funds. It is believed that there are 600 or more. These hedge funds employ few people, but their asset under management (AUM) can be over US$100 million each. In these hedge funds, financial and risk analysis is mostly based on Excel and Visual Basic for Applications (VBA) programming. This is a reason why the course “Spreadsheet Modeling” is very popular.

Our progress in writing this book was hindered by the financial tsunami in 2008. High market volatility, depreciation of wealth, and massive layoffs in the banking sector brought a lot of frustration to financial practitioners and financial educators. When we completed this book in June 2009, the market remained very weak. Many wealthy individuals suffered huge losses in the past 12 months; financial institutions cut their manpower seriously; selling complex products became difficult; and new regulations were enacted relating to structured products. In 2009, students in the course “Spreadsheet Modeling” still enjoyed the class but were slightly worried outside of the class. This is because the next round, which would be the fourth or fifth round, of massive layoffs would affect them. Investment banking follows obvious business cycles. This applies to study programs in financial engineering as well.

Mature students are always pragmatic in acquiring knowledge. Complex mathematics is very fancy, but our mature students tend to take it for granted and focus mostly on the applications of the mathematics. The course “Spreadsheet Modeling” makes those fancy mathematical concepts more easily applicable. From the perspective of educators, this mindset of the students is not harmful. After using Excel and VBA to build their models, some students become more interested in complex mathematics. What we would like them to know is not simply building models for financial analysis. We wish that they could understand model risks and estimate when these risks are likely to occur. The increased curiosity of our students after the course made us feel satisfied about our educational efforts.

Many new financial products have no mathematical models. Due to the advancement of technology, an analyst can easily apply Monte Carlo simulation on related variables and find out an average value. Our students especially like this analytical approach because there is less of a mathematical foundation required. In fact, Excel and VBA can easily handle Monte Carlo simulation.

1.2 Lehman Brothers’ Products for Retail Investors

Since 2005, Lehman Brothers began actively distributing a wide range of structured products via retail banks in Hong Kong, as well as in Singapore. One of our former financial engineering students came from France. After graduation, he worked in Lehman Brothers (Tokyo). A major part of his job was to structure products, which were finally sold to Hong Kong retail investors via local retail banks.

These products included equity-linked notes, minibonds (collateralized debt obligation [CDO] with total return swaps), and index-linked guaranteed notes. The equity-linked notes could provide an annual yield of 30 percent. Obviously the distribution of stock returns at that time was asymmetric with high upside potential and limited downside risk. The minibonds offered yields much better than bank deposits and the principle was guaranteed by an AA/A-rated institution—Lehman Brothers. This rating is better than that of many local banks.

Unfortunately, Lehman Brothers collapsed in September 2008. More than 40,000 retail investors in Hong Kong became victims. Some lost almost all their wealth. These victims continuously demonstrated in the street, at the front doors of various banks, and at the entrance of the Hong Kong Monetary Authority. Regulators encouraged banks to buy back the Lehman products. Banks were unwilling to do so. The Hong Kong banking industry experienced unprecedented exposure to reputational risk. In fact, this risk has never been discussed seriously and measured properly.

The Lehman incident made financial regulators extremely busy. Many of our financial engineering students are working for the regulatory bodies in Hong Kong. They were under serious pressure in the six-month period after September 2008. To mitigate regulatory risk, the regulators in Hong Kong announced a series of measures to prevent ordinary citizens from mistakenly buying high-risk products. These measures included mystery shopper programs (that is somebody pretending to be a bank client in order to test the selling process of frontline people) and audio-recording all relevant transactions. At the same time, the legal risk of banks intensified. Misrepresentation and insufficient duty of care became the words surrounding all financial institutions in Hong Kong. As a result, one of our authors was appointed to be an expert witness in some legal disputes relating to complex products. Risk management in banks suddenly became crisis management. Quantitative risk measures seemed less appealing.

1.3 Risk Management and Basel II

This book does not cover much about Basel II, which is the standard of risk management for the banking sector. There is a chapter about value-at-risk (VaR) and a chapter about probability of default (PD). Both VaR and PD are fundamental to bank capital charge. This book intends to share how complex financial products can be priced properly with simple programming tools. Asset pricing is a cornerstone of risk management. If an asset does not have any pricing model, we find it hard to measure its risk and evaluate its fair value. A pricing model facilitates scenario analysis: how much the asset will gain or lose in different scenarios, including some stress scenarios.

After the financial tsunami, Basel II has lost its credibility. Regulators obviously underestimated the impact of pro-cyclicality on credit risk. In 2002–2006, our university worked closely with the Hong Kong Monetary Authority to promote Basel II discussion in the Hong Kong banking sector. One of our authors was also an architect of the first internal-ratings-based system in Hong Kong. Basel II did help banks save capital charge. This could be an incentive for banks to invest heavily in risk management systems. This is also a reason why banks were undercapitalized in the crisis.

Basel II imposes capital requirements on market risk, credit risk, and operational risk. However, the interrelationship of these three risks has not been considered seriously. The VaR methodology assumes normal distribution of asset returns. Many credit-linked products, such as CDOs, collateralized mortgage obligations (CMOs), and others, are marketable securities subject to both interest rate risk plus credit migration risk. Actual or expected increase in credit risk can substantially lower asset prices. It seems that the Basel II capital requirement does not adequately address this issue. How should the correlation of credit risk and market risk be modeled? That is beyond the scope of this book.

Liquidity risk and stress testing risk are key issues in the collapse of banks. These risks are covered in Pillar II of Basel II. How can liquidity risk be modeled? Excel and VBA may help, but there is no consensus on what assumptions should be adopted. Stress testing usually involves many assumptions and a subjective selection of scenarios. Stress tests can be easily done and regulators usually find it hard to challenge those test results.

1.4 About the Book

The main topic of this book is the practical implementation of financial models using Excel and VBA programming. Too often, books on spreadsheet modeling provide only quick-and-dirty implementations of financial models that have very little use in real-world applications. This book focuses on the programming practices and skills to perform real-world implementation of financial models that are robust, reusable, and flexible. It takes an in-depth look at how to implement financial models using both Excel and VBA, and discusses the essential programming practices and skills in structuring complex financial models through advanced VBA features. It provides comprehensive coverage of financial models in the areas of derivatives pricing, market and credit risk modeling, and advanced interest rate modeling. Each of the later chapters on model implementation starts with a review of all the necessary financial theory and concepts from a practitioner's perspective. Step-by-step instructions on the implementation are then provided to explain the programming techniques involved for models with different complexities. Alternative approaches are also discussed to enable readers a comprehensive understanding of different techniques.

This book is suitable for those who have solid backgrounds in financial engineering, financial modeling, and financial risk management; a master's degree in financial mathematics, financial engineering, or computational finance is preferable. CFA, FRM, or professional risk manager (PRM) qualifications will be helpful to readers, but these readers must have prior training in calculus and matrix algebra. When we wrote this book, we surveyed books with relevant titles. None of them were advanced enough for our MSc (Financial Engineering) students. Most books with titles such as Financial Modeling, Excel Modeling in Finance, or Spreadsheet Modeling in Finance are targeted at undergraduate students in Finance or MBA students. Our book is targeted at financial engineering or mathematical finance students at business schools or engineering schools.

The book title “Financial Computing” is modified from “Computational Finance.” When our MSc (Financial Engineering) program was first launched in the 1990s, a number of professors from Carnegie Mellon University (CMU) served as our program advisors and teaching fellows. CMU offers a well-known program—MSc (Computational Finance). Computational Finance focuses on financial models that are based on mathematical theories and computational intelligence. Our book places less emphasis on financial models although we provide brief summaries on the theories mentioned in the book. We place more emphasis on how to implement these advanced models with Excel and VBA programming. This helps quantitative analysts quickly develop some models for their analytical work. This is the reason we named the book “Financial Computing” instead of “Computational Finance.” Our book covers a small number of well-known models and illustrates how Excel and VBA programming can be applied to implement these models. Through these models, readers can pick up Excel and VBA skills easily and apply these skills to other complex models. We believe that the book will be a good companion to any degree program in financial engineering or financial mathematics.

1.5 Chapter Highlights

Chapter 2 deals with the GARCH(1,1) model, which is used to predict the volatility of asset prices. Volatility estimates are critical for derivatives pricing and the volatility index can be traded. We introduce an effective way to use Solver in conjunction with VBA routines to enhance the functionality of Solver. Chapter 3 looks at the finite difference model, which is frequently used in derivatives pricing based on the Black–Scholes partial differential equation. We discuss the use of matrix manipulation under Excel as well as the VBA programming environment. A general framework that may be used to price a variety of options is formulated. Chapter 4 turns to portfolio mean-variance optimization. This is the base of modern investment theory and investment portfolio formation. We pay particular attention to the implementation of the Markowitz algorithm under short-selling restrictions. In all these chapters, we discuss the deficiency in taking a simple Excel implementation and demonstrate the necessity of using VBA programming in efficiently coping with complex conditions.

Chapter 5 introduces the Newton–Raphson method. This numerical procedure is powerful in solving a system of equations, and the routine developed here will be useful throughout the book. Chapter 6 discusses yield curve construction with cubic spline interpolation. We describe a generalized bootstrapping method, a computer-intensive statistical method, in the construction of a smooth yield curve given any available data set of bond prices. This enables the construction of an interest rate tree discussed in later chapters.

Chapters 7 and 8 deal with two different tree models in option pricings: the binomial model and the Black–Derman–Toy model. The binomial model can be applied to a wide range of equity derivatives. It can be implemented very easily using VBA programming. The Black–Derman–Toy model is particularly useful for pricing interest rate derivatives. We introduce an effective way to implement this model in VBA taking bond options as our working example.

Chapter 9 discusses option pricing using the Monte Carlo simulation method, which is a powerful tool in the valuation of exotic options with complex payoff conditions. We discuss various important issues regarding this method and look at the implementation for a number of exotic options. In particular, we take a closer look at the Monte Carlo pricing of American-style options with early exercising features.

Chapter 10 applies simulation techniques to determine portfolio value-at-risk. This chapter aims at providing the necessary programming skills to build a flexible and expandable risk engine for portfolio risk simulation.

Chapter 11 looks at the state-of-the-art Hull–White model of interest rates, which is commonly adopted by the industry for pricing interest rate derivatives. We discuss an effective way to implement the complex structure of this model taking bond options again as an example.

Chapters 12 and 13 discuss two well-known credit risk models: the CreditMetrics model and the KMV–Merton model. We start the discussion of the CreditMetrics model with a single issuer and then move to credit migration risk of credit portfolios. Chapter 12 focuses on the implementation of the credit RiskMetrics framework with the use of Monte Carlo simulation. In Chapter 13 we introduce the structural model developed by Robert C. Merton and extend our discussion to the KMV–Merton model. The KMV–Merton model is best applied to publicly traded firms and its underlying methodology predicts the probability of default of a firm within a given time horizon.

Appendices A to G provide a review of Excel and VBA programming. Many engineering school graduates may be familiar with Fortran, C, or Java and seldom touch Excel or VBA. The appendices will help these readers.

In all chapters, mathematical models are briefly mentioned. Our focus is to shar...

Table of contents

• Cover
• Title Page
• Copyright
• Preface
• Chapter 1: Financial Engineering and Computing
• Chapter 2: The GARCH(1,1) Model
• Chapter 3: Finite Difference Methods
• Chapter 4: Portfolio Mean–Variance Optimization
• Chapter 5: Newton–Raphson Method
• Chapter 6: Yield Curve Construction Using Cubic Spline
• Chapter 7: Binomial Option Pricing Model
• Chapter 8: The Black–Derman–Toy Model
• Chapter 9: Monte Carlo Option Pricing
• Chapter 10: Portfolio Value-at-Risk
• Chapter 11: The Hull–White Model
• Chapter 12: CreditMetrics Model
• Chapter 13: KMV–Merton Model
• Appendix A: VBA Programming
• 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
• Download CD/DVD Content