Professional Financial Computing Using Excel and VBA E-Book

Humphrey K. K. Tung

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Serie: Wiley Finance Editions
• Sprache: Englisch

Seitenzahl: 351

Veröffentlichungsjahr: 2011

Beschreibung

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.

Leseprobe

Contents

Cover

Title Page

Copyright

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

Review Question

Chapter 3: Finite Difference Methods

3.1 Difference Equations

3.2 Excel Implementation

3.3 VBA Implementation

3.4 Crank–Nicholson Scheme

Review Questions

Chapter 4: Portfolio Mean-Variance Optimization

4.1 Portfolio Selection

4.2 Excel Implementation

4.3 Excel Plus VBA Implementation

Review Questions

Chapter 5: Newton–Raphson Method

5.1 Newton–Raphson Method for Systems of Equations

5.2 VBA Routine

Review Questions

Chapter 6: Yield Curve Construction Using Cubic Spline

6.1 Cubic Spline Interpolation

6.2 Yield Curve Construction

6.3 Excel Plus VBA Implementation

Review Question

Chapter 7: Binomial Option Pricing Model

7.1 Risk-Neutral Option Pricing and the Binomial Tree

7.2 VBA Implementation

Review Questions

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

Review Questions

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

Review Questions

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

Review Question

Chapter 11: The Hull–White Model

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

Review Question

Chapter 12: CreditMetrics Model

12.1 The CreditMetrics Model

12.2 Individual (Segregate) Asset Valuation Framework

12.3 Monte Carlo Simulation in Detail

12.4 Excel and VBA Implementation

Review Questions

Chapter 13: KMV–Merton Model

13.1 KMV–Merton Model of Credit Risk

13.2 Excel and VBA Implementation

Review Question

Appendix A: VBA Programming

A.1 Introduction

A.2 A Brief History of VBA

A.3 Essential Excel Elements for VBA

A.4 The VBA Development Environment (VBE)

A.5 Basic VBA Programming Concepts

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

Download CD/DVD Content

Copyright © John Wiley & Sons (Asia) Pte. Ltd.

Published in 2010 by John Wiley & Sons (Asia) Pte. Ltd.

2 Clementi Loop, #02–01, Singapore 129809

All rights reserved.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as expressly permitted by law, without either the prior written permission of the Publisher, or authorization through payment of the appropriate photocopy fee to the Copyright Clearance Center. Requests for permission should be addressed to the Publisher, John Wiley & Sons (Asia) Pte. Ltd., 2 Clementi Loop, #02–01, Singapore 129809, tel: [phone], fax: [phone], e-mail: [email protected].

This publication is designed to provide accurate and authoritative information in regard to the subject matter covered. It is sold with the understanding that the publisher is not engaged in rendering professional services. If professional advice or other expert assistance is required, the services of a competent professional person should be sought.

Neither the authors nor the publisher are liable for any actions prompted or caused by the information presented in this book. Any views expressed herein are those of the authors and do not represent the views of the organizations they work for.

Other Wiley Editorial Offices

John Wiley & Sons, 111 River Street, Hoboken, NJ 07030, USA

John Wiley & Sons, The Atrium, Southern Gate, Chichester, West Sussex, P019 8SQ, United Kingdom

John Wiley & Sons (Canada) Ltd., 5353 Dundas Street West, Suite 400, Toronto, Ontario, M9B 6HB, Canada

John Wiley & Sons Australia Ltd., 42 McDougall Street, Milton, Queensland 4064, Australia

Wiley-VCH, Boschstrasse 12, D-69469 Weinheim, Germany

Library of Congress Cataloging-in-Publication Data

ISBN 978–0–470–82439–9

Preface

This book is a good company to Master degree programs in Financial Engineering, Financial Risk Management, Quantitative Investment, Computational Finance, or Mathematical Finance. Also, risk managers, traders, IT analysts, quantitative analysts working in investment banks and hedge fund will find it to be a good reference.

The book provides VBA examples on some widely-used finance and risk models. We expect that readers have prior training on these models because some of them require strong mathematical foundation. Through the examples, readers can easily build their implementable analytics and apply similar skills to other complex models.

Feedbacks from professors, students, analysts, and risk professionals are warmly welcome.

Humphrey Tung Donny Lai Michael Wong Stephen Ng Email: [email protected]

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 share with readers how to write relevant VBA programs. There is no standard programming route for a single problem. Readers may find faster programming methods to achieve the same outcome. These readers are welcome to contact us and share your better approaches with us. Practical exercises are provided at the end of each chapter that allow the readers to apply their technical skills acquired from the chapter. The solutions to these questions can be downloaded through the ftp link given by http://www.cs.cityu.edu.hk/~donny/humphrey/financial_computing.

1.6 Other Remarks

We would like to thank our students in Hong Kong for asking us challenging questions in class. This helps improve our thinking and sharpen our teaching performance. Among all the authors, Dr. Humphrey Tung contributed the most. He carefully reviewed every equation in the book. The other three authors would like to thank him for his passion in this project.

Chapter 2

The GARCH(1,1) Model

2.1 The Model

In this chapter, we discuss what is known as the GARCH(1,1) model, introduced by Bollerslev.1 The distinctive feature of this model is that volatilities of asset price returns are not constant. Under the stochastic regime, price return rt between, for example, the end of previous day t − 1 and the end of day t can be generated through random normal drawings as:

(2.1)

with dynamical volatility and constant mean . The model attempts to keep track and forecast the variations in the volatility through time. Applications of this so-called GARCH (generalized autoregressive conditional heteroscedasticity) volatility are widespread especially in the assessment of portfolio risk exposure over a short period of time.

In GARCH(1,1), future variance is a weighted average of its immediate past estimation , the most recent observation of squared residual 2, and a long-run average variance VL. It follows an iteration equation given by:

(2.2)

with weight factors , and . Since the total weight must sum up to one, we have:

Note that the constant mean μ in equation (2.2) can be estimated based on its historical average. There are all together three parameters in the model, namely that satisfy the constraints,

(2.3)

They can be estimated under the notion of maximum likelihood of seeing the historical data. Given the historical time series of price returns , we can first estimate the constant mean historically as:

For a particular choice of model parameters, GARCH volatilities can be generated through equation (2.2) where the iteration starts off from observation r1 and estimate . According to the random normal assumption in equation (2.1), the likelihood or chance of the entire historical data set being observed is proportional to:

(2.4)

The best model parameters should therefore generate the volatilities that maximize the likelihood in or equivalently the logarithm of likelihood ln() given by: