Modeling Structured Finance Cash Flows with Microsoft Excel A Step-By-Step Guide by Keith A. Allman

Modeling Structured Finance Cash Flows with Microsoft Excel

Modeling Structured Finance Cash Flows with Microsoft Excel A Step-By-Step Guide by Keith A. Allman

Contents of Modeling Structured Finance Cash Flows with Microsoft Excel

  • Introduction
  • The Three Basic Elements of a Cash Flow Model
  • Inputs
  • Cash Flow Structure
  • Outputs
  • The Process of Building a Cash Flow Model
  • Plan and Design
  • Obtain All Necessary Information
  • Construct Basic Framework
  • Develop Advanced Structure
  • Validate Assumptions
  • Test Model
  • How This Book Is Designed
  • CHAPTER 1. Dates and Timing
  • Time Progression
  • Dates and Timing on the Inputs Sheet
  • Day-Count Systems: 30/360 versus Actual/360 versus Actual/36
  • Model Builder 1.1: Inputs Sheet—Dates and Timing
  • Dates and Timing on the Cash Flow Sheet
  • Model Builder 1.2: Cash Flow Sheet—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
  • Fixed Rate Amortization Inputs
  • Floating Rate Amortization Inputs
  • 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
  • CHAPTER 3. Prepayments
  • How Prepayments Are Tracked
  • SMM: Single Monthly Mortality
  • CPR: Conditional Prepayment Rate
  • PSA: Public Securities Association
  • ABS: Absolute Prepayment Speed
  • 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 Prepayment Curve
  • Model Builder 3.2: Integrating Projected Prepayments in Asset Amortization
  • 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
  • The Effects of Seasoning and Default Timing
  • CHAPTER 5. Recoveries
  • Recoveries
  • Model Builder 5.1: Historical Recovery Analysis
  • Projecting Recoveries in a Cash Flow Model
  • Model Builder 5.2: Integrating Recoveries into Project Model Builder
  • Final Points Regarding Recoveries
  • CHAPTER 6. Liabilities and the Cash Flow Waterfall
  • Priority of Payments and the Cash Flow Waterfall
  • The Movement of Cash for an Individual Liability
  • Types of Liabilities
  • Fees
  • Model Builder 6.1: Calculating Fees in the Waterfall
  • Interest
  • Model Builder 6.2: Calculating Interest in the Waterfall
  • Principal
  • Model Builder 6.3: Calculating Principal in the Waterfall
  • Understanding Basic Asset and Liability Interactions
  • CHAPTER 7. Advanced Liability Structures: Triggers, Interest Rate Swaps, and Reserve Accounts
  • 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
  • CHAPTER 8. Analytics and Output Reporting
  • Internal Testing
  • Cash In versus Cash Out
  • Model Builder 8.1: Cash In versus Cash Out Test
  • Balances at Maturity
  • Model Builder 8.2: Balances at Maturity Tests
  • Asset Principal Check
  • Model Builder 8.3: Asset Principal Check Test
  • Performance Analytics
  • Monthly Yield
  • Model Builder 8.4: Calculating Monthly Yield
  • Calculating the Monthly Yield
  • Bond-Equivalent 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
  • The Importance of Testing and Output
  • Conditional Formatting
  • Goal Seek
  • Array Formulas
  • CHAPTER 9. Understanding the Model
  • The Complete Model in Review
  • Understanding the Effects of Increased Loss
  • Varying Principal Allocation Methodologies
  • Varying Prepayment Rates
  • Varying Loss Timing
  • Varying Recovery Rate and Lag
  • The Value of a Swap
  • Additional Testing
  • CHAPTER 10. Automation Using Visual Basic Applications (VBA)
  • Conventions of This Chapter
  • The Visual Basic Editor
  • The Menu Bar
  • The Project Explorer and the Properties Window
  • 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
  • The Investment Banker’s Perspective
  • The Investor’s Perspective
  • The Issuer’s Perspective
  • The Financial Guarantor’s Perspective
  • The Big Picture Perspective

Request your PDF Book

Write the name of the book in detail (Name, Author, Edition...)

What's the problem with this file?