Reverse Engineering Deals on Wall Street with Microsoft Excel

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.