Reverse Engineering Deals on Wall Street with Microsoft Excel E-Book

Keith A. Allman

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

Seitenzahl: 322

Veröffentlichungsjahr: 2008

Beschreibung

A serious source of information for those looking to reverse engineer business deals It's clear from the current turbulence on Wall Street that the inner workings of its most complex transactions are poorly understood. Wall Street deals parse risk using intricate legal terminology that is difficult to translate into an analytical model. Reverse Engineering Deals on Wall Street: A Step-By-Step Guide takes readers through a detailed methodology of deconstructing the public deal documentation of a modern Wall Street transaction and applying the deconstructed elements to create a fully dynamic model that can be used for risk and investment analysis. Appropriate for the current market climate, an actual residential mortgage backed security (RMBS) transaction is taken from prospectus to model by the end of the book. Step by step, Allman walks the reader through the reversing process with textual excerpts from the prospectus and discussions on how it directly transfers to a model. Each chapter begins with a discussion of concepts with exact references to an example prospectus, followed by a section called "Model Builder," in which Allman translates the theory into a fully functioning model for the example deal. Also included is valuable VBA code and detailed explanation that shows proper valuation methods including loan level amortization and full trigger modeling. Aside from investment analysis this text can help anyone who wants to keep track of the competition, learn from others public transactions, or set up a system to audit one's own models. Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Leseprobe

Table of Contents

Title Page

Copyright Page

Preface

Acknowledgements

About the Author

CHAPTER 1 - Introduction

THE TRANSACTION

THE DOCUMENTS

THE PROCESS

HOW THIS BOOK WORKS

CHAPTER 2 - Determining Dates and Setting Up Timing

DIFFERENCES IN TIMING APPROACHES

A FIRST LOOK AT THE PROSPECTUS

IMPORTANT DATES

TRANSFORMING DATES AND TIMING FROM WORDS TO A MODEL

MODEL BUILDER 2.1: REVERSING DATES AND TIMING

CONCLUSION OF DATES AND TIMING

CHAPTER 3 - Creating Asset Cash Flow from Prospectus Data

IT’S ALL IN THE PROSPECTUS SUPPLEMENT

THE BASICS OF AMORTIZATION

PERFORMANCE AND THE PROSPECTUS SUPPLEMENT

DELINQUENCY

LOSS

PREPAYMENT

RECOVERY

CREATING CASH FLOW

A COMPLEX IMPLEMENTATION

MODEL BUILDER 3.1: ENTERING IN THE RAW ASSET INFORMATION

MODEL BUILDER 3.2: ENTERING IN THE DEFAULT AND PREPAYMENT ASSUMPTIONS

MODEL BUILDER 3.3: INTEREST RATES AND ADDITIONAL ASSET AMORTIZATION INPUTS

MODEL BUILDER 3.4: INTRODUCING VBA AND MOVING DATA IN AND OUT OF THE MODEL

MODEL BUILDER 3.5: LOADING LOAN PERFORMANCE ASSUMPTIONS INTO VBA

MODEL BUILDER 3.6: GLOBAL FUNCTIONS

MODEL BUILDER 3.7: LOAN-LEVEL ASSET AMORTIZATION

CHAPTER 4 - Setting Up Liability Assumptions, Paying Fees, and Distributing Interest

IDENTIFYING THE OFFERED SECURITIES

MODEL BUILDER 4.1: TRANSFERRING THE LIABILITY INFORMATION TO A CONSOLIDATED SHEET

THE LIABILITY WATERFALL: A SYSTEM OF PRIORITY

MODEL BUILDER 4.2: STARTING THE WATERFALL WITH FEES

INTEREST: NO FINANCING IS FREE

MODEL BUILDER 4.3: CONTINUING THE WATERFALL WITH INTEREST PAID TO THE ...

MORE ON WATERFALLS AND WALL STREET’S RISK PARSING

MODEL BUILDER 4.4: MEZZANINE INTEREST

CONTINUING THE WATERFALL: IT ONLY GETS MORE COMPLICATED

CHAPTER 5 - Principal Repayment and the Shifting Nature of a Wall Street Deal

MODEL BUILDER 5.1: THE DEAL STATE AND SENIOR PRINCIPAL

MEZZANINE PRINCIPAL RETURNS

MODEL BUILDER 5.2: THE MEZZANINE CERTIFICATES’ PRIORITY OF PAYMENTS

NUMBER GAMES OR RISK PARSING?

CHAPTER 6 - Credit Enhancement Mechanisms to Mitigate Loss

MODEL BUILDER 6.1: EXCESS SPREAD, OVERCOLLATERALIZATION, AND CREDIT ENHANCEMENT

CHAPTER 7 - Auditing the Model

MODEL BUILDER 7.1

CHAPTER 8 - Conclusion of Example Transaction and Final Thoughts on Reverse Engineering

MORTGAGE INSURANCE AND SERVICER ADVANCES

REVERSE ENGINEERING IN THE CURRENT AND FUTURE MARKET

Appendix

About the CD-ROM

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 Trading series features books by traders who have survived the market’s ever-changing temperament and have prospered—some by reinventing systems, others by getting back to basics. Whether a novice trader, professional, or somewhere in-between, these books will provide the advice and strategies needed to prosper today and well into the future.

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

Copyright © 2009 by Keith A. Allman. 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/permissions.

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.

Designations used by companies to distinguish their products are often claimed as trademarks. In all instances where John Wiley & Sons, Inc. is aware of a claim, the product names appear in initial capital or all capital letters. Readers, however, should contact the appropriate companies for more complete information regarding trademarks and registration.

Microsoft and Excel are registered trademarks of Microsoft Corporation. Microsoft Excel screenshots reprinted with permission from Microsoft Corporation.

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:Allman, Keith A., 1977- Reverse engineering deals on Wall Street with Microsoft Excel : a step-by-step guide / Keith A. Allman. p. cm.—(Wiley finance series) Includes index.

eISBN : 978-0-470-47215-6

1. Financial engineering—Mathematical models. 2. Investments—Mathematical models. 3. Deals—Mathematical models. 4. Microsoft Excel (Computer file) I. Title. HG176.7.A45 2009 338.8’30285554-dc22 2008025012.

Preface

Years after starting my career in financial modeling at a bond insurer, I decided it was time to move on to Citigroup’s conduit to advance my knowledge of the securitization industry. I was no longer a newbie analyst with the lurking fear of not knowing enough about modeling or structured finance to justify my employment. Instead, I joined as a semiseasoned associate, questioning if the skills and knowledge I had thus far accumulated justified the lateral hiring. Luckily I was presented with a task my first week of work at Citigroup that would provide the answer to such a question, and given my place on the corporate food chain at the time, I would have to accept that answer whether I liked it or not.

The task at hand was to validate the conduit’s mortgage model to ensure that all calculation processes were correct and that the model essentially returned accurate durations, yields, and, ultimately, rating assessments of a transaction. “No problem,” I thought. “Enter data, push a few buttons, determine some durations and yields, and I complete my first task.” Like any great underestimation in life those were the thoughts of grandeur prior to the fall. I quickly learned that the process was going to be much more intense.

To validate the model I had to have one of the top four auditing firms provide a letter stating that the conduit’s model returned the same results as the auditing firm’s model. To obtain such a letter I had to select a deal with the auditor that was publicly rated and would cover many mortgage modeling concepts. The auditor and I would have to model the deals on our systems and tie durations and yields to the fifth decimal place. Still, it was my first week and I thought, “Well, that’s a bit more complicated than I thought, but they have a mortgage model, so how difficult could it be?”

Let’s just say, it was difficult. Opening the existing mortgage model, I found that it was a standard amortization engine. For those new to structured finance, this means that only the asset amortization was mostly done. There was essentially no liability structure in place and the deal we selected had nine tranches of debt, ratio-stripped classes, prepayment lockouts, and a host of other complexities. At this realization I took a breath, peered above my cube to see if somehow my boss had sensed the fear emanating from outside his office, and sat down again to refocus. How would I accomplish this task in a relatively short period of time? I stared at the 273-page document on my desk that would be my savior: the deal prospectus.

I got to know the deal prospectus for that transaction very well. I took it with me everywhere. I read it at home, on the subway, in my cube, on planes, and any other imaginable place. I realized that the prospectus was a very large map to proving my competence. I navigated through dates, timing issues, special amortization assumptions, complex liabilities, and advanced structuring concepts. Each page represented a section in my model. After a few weeks, I transformed legal jargon into functions, formulas, and code. The end result was, in my mind, a beautiful, harmonic merger of words and numbers. I use the words “in my mind” because as readers of finance material, you probably know the looks you get when trying to convey any excitement about this topic. Regardless of my enthusiasm level, I did tie to the fifth decimal place with the auditor’s output sheets and successfully completed my first task.

I often compare that model audit experience to when I first started in the finance industry and had to build a more basic model from scratch. I was overwhelmed by the task and worked incredibly hard to get a simple senior subordinated structure to work correctly. Similarly, reverse engineering the prospectus to be able to tie to the auditor’s model took hours of reading and rereading lawyers’ prose. Testing amortization scenarios and checking the resulting yields and durations consumed entire days. I had to constantly flip between reading sections of the prospectus to understand the details, working on my model to implement them, and then jumping back to the prospectus and the auditor’s printouts to check if I was correct.

Luckily, I already had a background in understanding deal documentation from my prior work in the financial guarantee business. As a third party to transactions providing financial guarantees, the company I worked for rarely wrote the bulk of the documents. Instead, we had to adapt a large amount of other bankers’ and lawyers’ writing into our analyses. Reading literally hundreds of term sheets and indentures made me relatively fluent in legal terminology and conventions.

Even with my prior experience, the task of reverse engineering a deal was not simple. It required many hours spent coming to a solution that could have easily been explained to me by a more senior professional. Unfortunately, given division budgets, such a senior professional on hand to answer modeling questions is a fantasy. Obtaining that knowledge in a text is much more of a reality, which was the logic for writing my first book on building a basic structured finance model from a blank spreadsheet.

However, reverse engineering a complete Wall Street transaction is much more complicated than just building a basic model. These complications have been highlighted by the subprime crisis that started in mid-2007. Some investors, risk managers, and many financial professionals responsible for structuring, purchasing, and trading Wall Street products only took rudimentary approaches to analyzing these complex securities, often relying on credit ratings alone. Whereas the collateral posed a major problem, with underwriters offering risky products to poor credit quality borrowers, the structures of these transactions became so complicated that, as the markets deteriorated, people with exposure became unsure of how the transactions would perform. Ultimately, investors were not clear if the deteriorated assets would produce enough cash to pay their tranches of debt. Complicated triggers and alterations in cash priority further exacerbated the problem. With sometimes hundreds of securities having similar collateral and virtually meaningless ratings, investors did not know how to price their securities, and chaos reigned in the market.

A properly trained staff of reverse engineers can solve this problem for any company. Most of the information required to model individual deals is available from multiple public sources. Understanding how to translate that information into an intelligible form is a challenge that this book addresses. I firmly believe that whether you are an investor, banker, auditor, or a student learning the business, thoroughly understanding the documentation and how it is translated into a computer-based model ultimately provides a complete understanding of deal mechanics and gives you the power to make confident, well-informed decisions.

KEITH A. ALLMAN

New York, New YorkOctober 2008

Acknowledgments

The idea for this book started right after a training session I facilitated through my financial training company Enstruct. It was a three-day course on financial modeling for a large bank that wanted to focus on understanding the calculations behind the complex terminology in deal documentation. I cannot divulge the bank’s name, but I thank them for helping stimulate the idea. From that point on, a number of people have helped me along the way. Primarily, Ralph Armenta provided a great recommendation in using the example deal that is reversed in this book and assisted with materials collection. Another excellent resource was Permjit Singh, who reviewed material that I sent and offered corrections and detail verification. Permjit is extremely detail oriented and incredible at finding even the smallest discrepancy. Finally, I would like to thank all of the staff at John Wiley & Sons who work on my books: Emilie Herman, Laura Walsh, Mary Daniello, and Bill Falloon.

K. A. A.

About the Author

Keith Allman is the founder and principal trainer of Enstruct, a financial training company that specializes in quantitative finance and modeling instruction. He began Enstruct as a structured finance-focused training company, but has expanded the core curriculum to cover other topics such as corporate modeling, valuation, programming for finance, and using applications outside of Excel for more robust financial analysis. Mr. Allman also leads the consultancy work that Enstruct has been engaged in, which has largely been structured finance-related, such as mortgage and auto securitizations. His particular area of expertise is international in scope, with training and transaction work in most of Latin America, the Caribbean, the Middle East, South East Asia, Australia, Russia, and parts of Southern and Western Africa.

Prior to his current position, he was a Vice President in the Global Special Situations Group at Citigroup, where he focused on principal finance in emerging markets. Previously, he worked in Citigroup’s Global Securitized Markets division modeling conduit transactions and in MBIA Corporation’s Quantitative Analytics group. Mr. Allman is also the author of Modeling Structured Finance Cash Flows with Excel: A Step-by-Step Guide (Wiley & Sons 2007). His education includes a master’s degree in international affairs with a concentration in finance and banking from Columbia University and dual bachelor degrees from UCLA.

CHAPTER 1

Introduction

In my first book, Modeling Structured Finance Cash Flows with Microsoft Excel: A Step-by-Step Guide, I took readers through building a basic structured finance model from a blank worksheet. The text is a practical guide to transforming the concepts of a structured finance deal into an Excel-based model. However, in the finance industry, few people rely on a concept to close a deal. Instead, they rely on strict legal documentation that dictates the precise mechanics of the transaction. The difference between a deal based on general concepts and one based on well-defined rules can be substantial. This is why documentation exists for every concept in a deal. Attorneys spend hours writing terms sheets and indentures, banking associates review every word and integrate documents into a deal prospectus, and finally junior analysts lose sleep formatting and making charts to enhance the final prospectus.