Book description
A practical guide to building fully operational financial cash flow models for structured finance transactions
Structured finance and securitization deals are becoming more commonplace on Wall Street. Up until now, however, market participants have had to create their own models to analyze these deals, and new entrants have had to learn as they go. Modeling Structured Finance Cash Flows with Microsoft Excel provides readers with the information they need to build a cash flow model for structured finance and securitization deals. Financial professional Keith Allman explains individual functions and formulas, while also explaining the theory behind the spreadsheets. Each chapter begins with a discussion of theory, followed by a section called "Model Builder," in which Allman translates the theory into functions and formulas. In addition, the companion CD-ROM features all of the modeling exercises, as well as a final version of the model that is created in the text.
Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.
Table of contents
- Title Page
- Copyright Page
- Preface
- Acknowledgments
- About the Author
- Introduction
- CHAPTER 1 - Dates and Timing
-
CHAPTER 2 - Asset Cash Flow Generation
- LOAN LEVEL VERSUS REPRESENTATIVE LINE AMORTIZATION
- HOW ASSET GENERATION IS DEMONSTRATED IN MODEL BUILDER
- ASSET GENERATION ON THE INPUTS SHEET
- MODEL BUILDER 2.1: INPUTS SHEET ASSET ASSUMPTIONS AND THE VECTORS SHEET
- ASSET GENERATION ON THE CASH FLOW SHEET
- MODEL BUILDER 2.2: NOTIONAL ASSET AMORTIZATION ON THE CASH FLOW SHEET
- TOOLBOX
-
CHAPTER 3 - Prepayments
- HOW PREPAYMENTS ARE TRACKED
- HISTORICAL PREPAYMENT DATA FORMATS
- BUILDING PREPAYMENT CURVES
- PREPAYMENT CURVES IN PROJECT MODEL BUILDER
- THE EFFECT OF PREPAYMENTS ON STRUCTURED TRANSACTIONS
- MODEL BUILDER 3.1: HISTORICAL PREPAYMENT ANALYSIS AND CREATING A PROJECTED ...
- MODEL BUILDER 3.2: INTEGRATING PROJECTED PREPAYMENTS IN ASSET AMORTIZATION
- TOOLBOX
-
CHAPTER 4 - Delinquency, Default, and Loss Analysis
- DELINQUENCIES VERSUS DEFAULTS VERSUS LOSS
- THE IMPORTANCE OF ANALYZING DELINQUENCY
- MODEL BUILDER 4.1: BUILDING HISTORICAL DELINQUENCY CURVES
- DERIVING HISTORICAL LOSS CURVES
- MODEL BUILDER 4.2: BUILDING HISTORICAL AND PROJECTED LOSS CURVES
- ANALYZING HISTORICAL LOSS CURVES
- MODEL BUILDER 4.2 CONTINUED
- PROJECTING LOSS CURVES
- MODEL BUILDER 4.2 CONTINUED
- INTEGRATING LOSS PROJECTIONS
- MODEL BUILDER 4.3: INTEGRATING DEFAULTS IN ASSET AMORTIZATION
- CHAPTER 5 - Recoveries
- CHAPTER 6 - Liabilities and the Cash Flow Waterfall
-
CHAPTER 7 - Advanced Liability Structures
- TRIGGERS AND THEIR AFFECT ON THE LIABILITY STRUCTURE
- MODEL BUILDER 7.1: INCORPORATING TRIGGERS
- SWAPS
- MODEL BUILDER 7.2: INCORPORATING A BASIC INTEREST RATE SWAP
- FINAL NOTES ON SWAPS
- RESERVE ACCOUNTS
- MODEL BUILDER 7.3: INCORPORATING A CASH-FUNDED RESERVE ACCOUNT
- CONCLUSION OF THE CASH FLOW WATERFALL
- TOOLBOX
-
CHAPTER 8 - Analytics and Output Reporting
- INTERNAL TESTING
- MODEL BUILDER 8.1: CASH IN VERSUS CASH OUT TEST
- MODEL BUILDER 8.2: BALANCES AT MATURITY TESTS
- MODEL BUILDER 8.3: ASSET PRINCIPAL CHECK TEST
- PERFORMANCE ANALYTICS
- MODEL BUILDER 8.4: CALCULATING MONTHLY YIELD
- MODEL BUILDER 8.5: CALCULATING BOND-EQUIVALENT YIELD
- MODIFIED DURATION
- MODEL BUILDER 8.6: CALCULATING MODIFIED DURATION
- OUTPUT REPORTING
- MODEL BUILDER 8.7: CREATING THE OUTPUT REPORT
- TOOLBOX
- CHAPTER 9 - Understanding the Model
-
CHAPTER 10 - Automation Using Visual Basic Applications (VBA)
- CONVENTIONS OF THIS CHAPTER
- THE VISUAL BASIC EDITOR
- VBA CODE
- SIMPLE AUTOMATION FOR PRINTING AND GOAL SEEK
- MODEL BUILDER 10.1: AUTOMATING PRINT PROCEDURES
- MODEL BUILDER 10.2: AUTOMATING GOAL SEEK TO OPTIMIZE ADVANCE RATES
- UNDERSTANDING LOOPING TO AUTOMATE THE ANALYTICS SHEET
- MODEL BUILDER 10.3: AUTOMATING GOAL SEEK TO PERFORM TRANSACTION ANALYTICS
- AUTOMATED SCENARIO GENERATION
- MODEL BUILDER 10.4: CREATING A TRANSACTION SCENARIO GENERATOR
- WORKING WITH MACROS IN EXCEL
- CHAPTER 11 - Conclusion
- APPENDIX - Using This Book with Excel 2007
- About the CD-ROM
- Index
Product information
- Title: Modeling Structured Finance Cash Flows with Microsoft® Excel®: A Step-by-Step Guide
- Author(s):
- Release date: March 2007
- Publisher(s): Wiley
- ISBN: 9780470042908
You might also like
book
Encyclopedia of Financial Models, 3 Volume Set
An essential reference dedicated to a wide array of financial models, issues in financial modeling, and …
book
Handbook of Finance: Financial Markets and Instruments
Volume I: Financial Markets and Instruments skillfully covers the general characteristics of different asset classes, derivative …
book
Structured Products and Related Credit Derivatives: A Comprehensive Guide for Investors
Filled with the insights of numerous experienced contributors, Structured Products and Related Credit Derivatives takes a …
book
Financial Modeling and Valuation: A Practical Guide to Investment Banking and Private Equity
Written by the Founder and CEO of the prestigious New York School of Finance, this book …