Reverse Engineering Deals on Wall Street with Microsoft Excel : A Step-by-Step Guide

  • ISBN13:


  • ISBN10:


  • Edition: 1st
  • Format: Paperback
  • Copyright: 2008-12-10
  • Publisher: Wiley
  • Purchase Benefits
  • Free Shipping On Orders Over $35!
    Your order must be $35 or more to qualify for free economy shipping. Bulk sales, PO's, Marketplace items, eBooks and apparel do not qualify for this offer.
  • Get Rewarded for Ordering Your Textbooks! Enroll Now
List Price: $95.00 Save up to $31.16
  • Buy New
    Add to Cart Free Shipping


Supplemental Materials

What is included with this book?

  • The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.


Modeling Structured Finance Cash Flows has attracted a nice audience by teaching the process of structure finance on Wall Street to those who need to learn the craft. Reverse Engineering Deals on Wall Street with Microsoft Excel looks at the problem through a new window - namely, how do you reverse engineer deal terms in a competitor's deal sheet or prospectus so you can understand or do similar deals yourself. The process of reverse engineering is an essential Wall Street business practice, which Allman demystifies using Microsoft Excel as a calculation worksheet. While Modeling Structured Finance Cash Flows showed the reader how to structure a deal from scratch, Reverse Engineers goes from deal sheet or prospectus and walks a user through the process of reverse engineering a deal. It is done step by step with textual excerpts from the prospectus and how that directly transfers to a model. The book would be done with the same detailed and rich instruction as Allman's first book, including Model Builder-type exercises, and working models for any reversed deals.

Author Biography

Keith A. Allman is a capital markets professional with a specialization in analytics and modeling. He is currently the principal trainer and founder of Enstruct, a quantitative finance training company, as well as a Managing Director with NSM Capital Management. Prior to this, Allman was a vice president at Citigroup's Global Corporate and Investment Bank. He has also worked for MBIA Corporation in their Quantitative Analytics division. Allman is the author of Modeling Structured Finance Cash Flows with Microsoft Excel, which is published by Wiley.

Table of Contents

Prefacep. ix
Acknowledgmentsp. xiii
About the Authorp. xv
Introductionp. 1
The Transactionp. 2
The Documentsp. 2
The Processp. 6
How This Book Worksp. 12
Determining Dates and Setting Up Timingp. 15
Differences in Timing Approachesp. 15
A First Look at the Prospectusp. 16
Important Datesp. 17
Transforming Dates and Timing from Words to a Modelp. 21
Model Builder 2.1: Reversing Dates and Timingp. 21
Conclusion of Dates and Timingp. 27
Creating Asset Cash Flow from Prospectus Datap. 29
It's All in the Prospectus Supplementp. 29
The Basics of Amortizationp. 32
Performance and the Prospectus Supplementp. 33
Delinquencyp. 34
Lossp. 35
Prepaymentp. 36
Recoveryp. 38
Creating Cash Flowp. 38
A Complex Implementationp. 39
Entering in the Raw Asset Informationp. 40
Entering in the Default and Prepayment Assumptionsp. 43
Interest Rates and Additional Asset Amortization Inputsp. 47
Introducing VBA and Moving Data In and Out of the Modelp. 50
Loading Loan Performance Assumptions into VBAp. 57
Global Functionsp. 60
Loan-Level Asset Amortizationp. 61
Setting Up Liability Assumptions, Paying Fees, and Distributing Interestp. 81
Identifying the Offered Securitiesp. 81
Transferring the Liability Information to a Consolidated Sheetp. 84
The Liability Waterfall: A System of Priorityp. 87
Starting the Waterfall with Feesp. 89
Interest: No Financing Is Freep. 91
Continuing the Waterfall with Interest Paid to the Certificate Holdersp. 93
More on Waterfalls and Wall Street's Risk Parsingp. 106
Mezzanine Interestp. 106
Continuing the Waterfall: It Only Gets More Complicatedp. 114
Principal Repayment and the Shifting Nature of a Wall Street Dealp. 115
The Deal State and Senior Principalp. 116
Mezzanine Principal Returnsp. 142
The Mezzanine Certificates' Priority of Paymentsp. 142
Number Games or Risk Parsing?p. 147
Credit Enhancement Mechanisms to Mitigate Lossp. 149
Excess Spread, Overcollateralization, and Credit Enhancementp. 151
Auditing the Modelp. 173
Model Builder 7.1p. 174
Conclusion of Example Transaction and Final Thoughts on Reverse Engineeringp. 187
Mortage Insurance and Servicer Advancesp. 187
Reverse Engineering in the Current and Future Marketp. 188
Appendixp. 189
Automatic Range Namingp. 189
About the CD-ROMp. 193
Indexp. 197
Table of Contents provided by Ingram. All Rights Reserved.

Rewards Program

Write a Review