Structured Finance Modeling with Object-Oriented VBA E-Book

Evan Tick

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

Seitenzahl: 435

Veröffentlichungsjahr: 2011

Beschreibung

A detailed look at how object-oriented VBA should be used to model complex financial structures This guide helps readers overcome the difficult task of modeling complex financial structures and bridges the gap between professional C++/Java programmers writing production models and front-office analysts building Excel spreadsheet models. It reveals how to model financial structures using object-oriented VBA in an Excel environment, allowing desk-based analysts to quickly produce flexible and robust models. Filled with in-depth insight and expert advice, it skillfully illustrates the art of object-oriented programming for the explicit purpose of modeling structured products. Residential mortgage securitization is used as a unifying example throughout the text.

Leseprobe

Contents

Preface

List of Acronyms

Acknowledgments

About the Author

Chapter 1: Cash-Flow Structures

1.1 Getting Started

1.2 Securitization

1.3 Synthetic Structures

1.4 Putting It All Together

Chapter 2: Modeling

2.1 Dipping a Toe in the Shallow End

2.2 Swimming Toward the Deep End

2.3 Types

2.4 Class Architecture

2.5 Exercises

Chapter 3: Assets

3.1 Replines

3.2 Portfolio Optimization

3.3 Losses, Prepayments, and Interest Rates

3.4 Cash-Flow Model

3.5 S&P Cash-Flow Model

3.6 Moody’s Cash-Flow Model

3.7 Option ARMs

3.8 Class Architecture: Multiple Inheritance

3.9 Doing It in Excel: SumProduct

3.10 Exercises

Chapter 4: Liabilities

4.1 Getting Started

4.2 Notation

4.3 Expenses

4.4 Interest

4.5 Over-collateralization

4.6 Principal

4.7 Writedowns and Recoveries

4.8 Derivatives

4.9 Triggers

4.10 Residuals: NIMs and Post-NIM

4.11 Class Architecture

4.12 Doing It in Excel: Data Tables

4.13 Exercises

Chapter 5: Sizing the Structure

5.1 Senior Sizing

5.2 Subordinate Sizing

5.3 Optimizations and Complexity

5.4 Example of Sizing

5.5 NIM and OTE Sizing

5.6 Class Architecture

5.7 Doing It in Excel: Solver

5.8 Exercises

Chapter 6: Analysis

6.1 Risk Factors

6.2 Mezzanine and Subordinate Classes

6.3 NIM Classes

6.4 Putting It All Together

6.5 Exercises

Chapter 7: Stochastic Models

7.1 Static versus Stochastic

7.2 Loss Model

7.3 Gaussian Copula

7.4 Monte Carlo Simulation

7.5 Synthetic Credit Indexes

7.6 Doing It in Excel

7.7 Exercises

Appendix A: Excel and VBA

Appendix B: Bond Math

References

Index

Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Australia, and Asia, Wiley is globally committed to developing and marketing print and electronic products and services for our customers’ professional and personal knowledge and understanding.

The Wiley Finance series contains books written specifically for finance and investment professionals as well as sophisticated individual investors and their financial advisors. Book topics range from portfolio management to e-commerce, risk management, financial engineering, valuation, and financial instrument analysis, as well as much more.

For a list of available titles, visit our Web site at www.WileyFinance.com.

Copyright © 2007 by Evan Tick. All rights reserved

Published by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

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 permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone], or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permission.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at [phone], outside the United States at [phone] or fax [phone].

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books. For more information about Wiley products, visit our Web site at www.wiley.com

Library of Congress Cataloging-in-Publication Data

Tick, Evan, 1959–

Structured finance modeling with object-oriented VBA / Evan Tick.

p. cm.—(Wiley finance series)

Includes bibliographical references and index.

ISBN-13: 978-0-470-09859-2 (cloth)

ISBN-10: 0-470-06859-7 (cloth)

1. Finance—Mathematical models. 2. Investment—Mathematical models.

3. Microsoft Visual Basic for applications. I. Title.

HG106.T53 2007

332.01'13—dc22

2006032748

For Lisa

Preface

Using even the most conservative estimates, asset-backed securities (ABSs) and collateralized debt obligations (CDOs) have grown tremendously over the past 10 years. ABS includes asset sectors in credit card debt, auto loans, student loans, subprime mortgages, home-equity loans, and equipment loans. This doesn’t even include prime mortgages, which are categorized as mortgage-backed securities (MBSs). In 2004, the U.S. ABS supply reached $ 617 billion, with subprime mortgages and home-equity loans around half (J. P. Morgan 2005). These assets can be held as “raw” or whole loans on bank balance sheets, or bonds created through securitization. A percentage of the ABS bonds themselves are repackaged into CDOs. In 2004, $ 160 billion of cash CDOs were issued (Lucas 2006) of which about $ 50 billion were ABS CDOs (Bear Stearns 2006). There are also corporate, high-yield, and emerging market CDOs. CDO issuance has grown exponentially over the past 10 years. Synthetic CDOs (built with credit default swaps rather than cash assets) issuance is growing significantly faster than “cash” CDOs (Tavakoli 2003).

The phenomenal growth of these asset classes, and primarily subprime mortgages, can certainly be attributed to the structure of interest rates in the recent past. Historically low interest rates after the Internet bubble and 9/11 led to the rational response of increased debt levels. Subprime home buyers could borrow at affordable rates and prime home buyers could borrow against appreciated home values (home-equity loans). In addition, efficient credit scoring techniques and information on borrowers helped supply grow to meet demand. The Housing Affordability Index, measuring the average ratio of income to housing prices in the United States, reached 144% in 2003, a 30-year high (Molony 2003). The series of Fed interest rate hikes during 2004–2006 has damped growth, and perhaps we have seen the plateau of ABS supply. The big story of the past year has been one of squeezed margins of subprime originators, leading them to relax underwriting standards to goose volume. The chickens started to come home to roost in late 2005—recent vintages appear to be the worst ever in terms of delinquencies and defaults (Zimmerman 2007). But the lasting innovations during these past years are the financial structures for efficiently packaging debt.

Why such explosive growth? Two things: innovative assets and financial engineering. The continual evolution of assets makes borrowing more affordable. Securitization enables unparalleled partitioning and transfer of risk. The repackaging of risk, for example, has allowed banks to buy investment-grade pieces while hedge funds buy lesser-rated (and higher-return) residuals. Issuance has grown to fill market demand among these different niches. Regulated banks, insurance companies, and other investors that could not own whole loans on their balance sheets, either by charter or by severe capital requirements, found they could economically own securitized assets.

Over the past 10 years, on the whole, these investments have fared rather well. Comparing the historical constant annual default rates and recoveries of MBS and corporates, MBS (residential and commercial) do better across the ratings (Lucas 2006). ABS did not perform as well as corporates, but averaged with MBS, the ABS/MBS market as a whole is competitive with corporates, given this metric for risk.

These products are derivatives and hence can grow faster than the underlying assets. Debt is resecuritized—loans into bonds, bonds into CDOs, even CDOs built from other CDOs. On top of this, synthetic structures allow cash assets to be resecuritized any number of times. Thus, talking about the growth of these products may not mean much. Risk is not growing at the same rate because the products are often hedged and much of the risk cancels out.

Two things loom large on the horizon: market rates and regulation. ABS/MBS assets migrate as market conditions change. For example, recently with flat forward rates and uncertain outlook for inflation, U.S. borrowers are switching from floating- to fixed-rate loans. As affordability declines, subprime borrowers increase at the expense of prime borrowers. With rates high enough in the short term, new debt creation will decrease. However, derivatives built from these fixed-income assets will not necessarily decline. Basel II regulations give low-risk tranches better capital treatment, and risky tranches get penalized more (Fitch 2005). As these regulations are adopted, banks and other investors will likely shift their appetites for securitized product. This flexibility is conducive to the long-term health of the securitization market, which is second only to equities in the United States. It is a market that cannot be ignored; it is represented in any significant fixed-income portfolio. Lastly, new products are continually being innovated. Four years ago, synthetic baskets of corporate credits (IBOXX) started trading, evolving into trading standard tranches and then bespokes. In early 2006, synthetic baskets of ABS credits (ABX) started trading, leading to standard tranches in 2007 (TABX) (Morgan Stanley 2006). TABX had birth pains due to declining home prime appreciation creating a bearish one-sided market. If history is any indicator, a more liquid market will develop once participants converge on a pricing model.

Originally, banks used securitization for balance-sheet arbitrage. Then other parties became involved in securitization, such as mortgage originators and hedge funds issuing CDOs. The growth of demand for mezzanine bonds was critical to this development. From 1998 to 2005 balance-sheet arbitrage dropped from 48% to 18% of new CDO issuance, replaced by transactions wherein the equity investor aims to arbitrage the excess spread (Mahadevan 2006a). The banks did and will continue to buy seniors, whatever the capital requirements are. High-risk investors (investment funds and hedge funds) will continue to buy the equity pieces. The weak link may very well be the mezzanine investors. Mezzanine demand comes from both “real” money investors and other securitizations. A key question is: How robust and diverse is this class of investors? How correlated are the assets due to the investor base? Should demand slow, can pricing adjust to shift investors outward to senior and equity tranches, keeping securitization as a viable business?

Modeling is essentially abstraction and simplification while producing an accurate estimate of some aspect of a complex system. If the system is physical or financial, the attributes of a good model remain the same. By modeling I am talking about a broader area than simply a mathematical representation of a system. I am also referring to the implementation of the model. Of the financial engineering innovations developed over the past 10 years of feverish ABS growth, the cash flow securitization model is key. In general, this model has three components: loss generation, collateral cash flow generation, and bond cash flow generation. Loss generation models the loss distribution of the assets. The collateral model takes the loss characteristics and produces asset cash flows. The bond model takes the asset cash flows and produces liability cash flows. Be it a vanilla securization or a CDO of CDOs, be it supported by mortgages, loans, or bonds, be it cash or synthetic, the valuation model is essentially the same.

This book introduces this model and its implementation. Illustrations of the model in action are given with empirical studies of the sensitivities of actual structures. To concretize the discussion, subprime mortgage securitization is used throughout the book as a unifying example. It was chosen because in combination with prime and commercial mortgages, mortgage assets and their securitizations make up the bulk of the securitized market. Modeling lessons learned in this sector can certainly be applied to other asset classes and sectors. Subprime is also topical because of the recent efforts to model TABX, perhaps with a combination of cash flow securitization model and market-spread-driven copula model.

The main topics covered in this book are:

Securitization: asset and liability cash flow models, waterfalls, rating agency stresses, residuals, hedging, bond allocation, and sensitivity analyses. The details of the financial model are uncovered, with formal specifications given.Stochastic models: Monte Carlo, using copula to account for correlations, and credit index modeling. The previous static models are converted here to dynamically simulate (correlated) random variables. This increases accuracy and depth. For example, rather than boil loss down to a single expected value, the entire loss distribution can be used.Optimization techniques: simulated annealing, zero-one programming, search methods. Several problems arise in securitization that benefit from optimization, for example, allocating bonds and selecting collateral. Practical nonlinear methods are emphasized.Object-oriented architecture: classes, methods, and inheritance. Effective programming methodology is introduced that facilitates the implementation of these models. These techniques are popular in science, engineering, and certain areas of finance such as exotic derivatives. The same tools are leveraged here for cash flow modeling.Excel and VBA: advanced techniques, recommended style, and extensive examples. Many books introduce financial applications in Excel. Raise the level of your game with modular programming in VBA.

Various sections of offering materials are reproduced in this book for illustrative purposes only, and make no representation as to the accuracy of the information so reproduced. Readers are encouraged to contact the author with comments and corrections at evan [email protected].

List of Acronyms

ABCDSAsset-Backed Credit Default SwapABSAsset-Backed SecurityABXAsset-Backed indeXADBAmortized Defaulted BalanceAFCAvailable Funds CapARMAdjustable-Rate MortgageAPIApplication Program InterfaceBETBinomial Expansion TechniqueBEYBond Equivalent YieldBRCFABasis Risk Carry-Forward AmountCADRConstant Annual Default RateCDICMO Description InformationCDOCollateralized Debt ObligationCDRConstant Default RateCDSCredit Default SwapCLTVCombined Loan-To-Value (ratio)CMBSCommercial Mortgage-Backed SecurityCMOCollateralized Mortgage ObligationCMTConstant Maturity TreasuryCPRConstant Prepayment RateDLLDynamically Linked LibraryERFAExcess Reserve Fund AccountFICOFair Isaac CompanyFRMFixed-Rate MortgageGUIGraphical User InterfaceHELHome-Equity LoanICInterest Coverage (test)IOInterest Only (bond)IRRInternal Rate of ReturnLCLoss CoverageLGDLoss Given DefaultLIBORLondon Inter-Bank Offered RateLTVLoan-To-Value (Ratio)MBSMortgage-Backed SecurityMPRMonthly Payment Rate (credit cards)MPSMathematical Programming SystemNASNon-Accelerating Senior (bonds)NIMNet Interest MarginO/COver-collateralization (Test)OTEOwner’s Trust EquityPACPlanned Amortization Class (bond)PIKPay In Kind (bond)PMFProbability Mass FunctionPOPrincipal Only (bond)PPRPrincipal Payment Rate (credit cards)PSAPublic Securities Association–Bond Market AssociationREMICReal Estate Mortgage Investment ConduitRICORacketeer-Influenced Corrupt Organization (act)RMBSResidential Mortgage-Backed SecurityS&PStandard & Poor’sSMMSingle Monthly MortalitySTCDOSingle-Tranche CDOVBAVisual Basic for ApplicationsWACWeighted-Average CouponWALWeighted-Average LifeWAMWeighted-Average MaturityWARFWeighted-Average Rating FactorZPBZero Prepay Balance

Acknowledgments

I thank my colleagues at IXIS Capital Markets for their friendship over the years and for creating a great working environment. Rob Catarella, Joe Falcone, Bill Greenberg, John Hatzoglou, Rick Martino, Rene Mendez, Chris Nolle, Steve Pasko, Vaclav Polasek, Eric Raiten, and Andre Romain all shared their insights and expertise in developing this book. I am especially grateful to Andre, Eric, and Joe for reviewing early drafts, and William Dellal and Paul Monaghan for their support over the years. I also thank Young-Sup Lee at Morgan Stanley, Tim McLaughlin at Nomura Securities, Sylvain Jousseaume at Merrill Lynch and Ian Adelson for ongoing friendships and technical discussions. Special thanks go to Bill Falloon and his staff at Wiley.

About the Author

Evan Tick studied Electrical Engineering at MIT (MS, BS, 1982) and Stanford (PhD, 1987), before teaching at The University of Tokyo and The University of Oregon. He moved to New York in 1996 to work for Morgan Stanley and then Caisse des Dépôts CDC (now IXIS) soon after. He has been involved in fixed-income markets, focusing on portfolio optimization, risk management, asset-backed conduits, mortgage securitization, and credit derivatives. Perhaps his most crowning achievement was coaching the Douglass Panthers, who finished in sixth place in the 2005 First Lego League NYC-wide robotics tournament. Or it could have been cycling from Bolzano to the top of the Passo di Stelvio one horrible day in a freezing rainstorm in August 2000.

CHAPTER 1

Cash-Flow Structures

If I listened to my customers, I would have invented a very fast horse.

—Henry Ford

1.1 GETTING STARTED

A simplified “cash” structure, also known as a “true sale” structure, is illustrated in Figure 1.1. A seller sells assets into a trust from which bonds are issued to investors. The adjective “cash” is used to denote that real assets are purchased with cash collected from issued bonds. This is opposed to a synthetic structure where credit default swaps (CDSs) are entered into (discussed later in this chapter). The adjective “true sale” refers to the transfer of the assets into a trust or special-purpose vehicle (SPV) from the sellers. The transfer is a legal sale, isolating the assets from the seller.

FIGURE 1.1 Simplified Cash Securitization Structure (Not Drawn to Scale)

The assets reside in the trust and generate interest and principal (I + P) cash flows. These collateral cash flows are routed to the various bonds that were issued as liabilities. The rationale behind this generic structure is to transform a group of assets with certain average credit risk into a set of bonds of distinct credit risks. These risks may be formalized by virtue of having a rating agency assign ratings (e.g., senior bonds are rated AAA).1 The bonds are partitioned in an attempt to meet investor demand for different risks. The structure satisfies various counterparties in different ways. Senior investors may gain the ability to invest in asset types that were previously unavailable to them because of their raw risk. Equity investors may exploit the excess spread “arbitrage” between asset yields and issued bond costs. Seller/securitizers, underwriters, guarantors, and the like look to earn fees.

The AAA senior bonds, for example, achieve their low risk and high rating because they are supported (at a higher priority than other bonds) by asset cash flows and they have various forms of credit enhancement. One type of credit enhancement is the subordinate bonds shown in . These bonds absorb any losses before the seniors do— the subordinates need to be written down before the seniors realize loss. The alchemy of transforming collateral of one rating into a security of another rating is subtle. Do the senior bonds have the same credit risk as other securities rated AAA (perhaps backed by different collateral) by the same rating agency? Where does the similarity end, making the rating nonequivalent (Davies 2006)?