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
Introduction to Structured Finance
Created by the experienced author team of Frank Fabozzi, Henry Davis, and Moorad Choudhry, Introduction to …
book
A Pragmatist’s Guide to Leveraged Finance: Credit Analysis for Bonds and Bank Debt
The high-yield leveraged bond and loan market (“junk bonds”) is now valued at $3+ trillion in …
book
Mastering Cash Flow and Valuation Modelling
Your practical step-by-step guide to planning and building cash valuation models. Through a set of comprehensive …
book
Mastering Financial Modeling: A Professional’s Guide to Building Financial Models in Excel
All the precision of financial modeling--and none of the complexity Evidence-based decision making is only as …