Reverse Engineering Deals on Wall Street with Microsoft Excel (eBook)

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.

Preface.

Acknowledgments.

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 Certificate Holders.

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.

Automatic Range Naming.

About the CD-ROM.

Index.