Modeling Structured Finance Cash Flows with Microsoft® Excel®: A Step-by-Step Guide

Modeling Structured Finance Cash Flows with Microsoft® Excel®: A Step-by-Step Guide

by KEITH A. ALLMAN
Released March 2007
Publisher(s): Wiley
ISBN: 9780470042908

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to live online training experiences, plus books, videos, and digital content from O’Reilly and nearly 200 trusted publishing partners.

Start your free trial

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

  1. Title Page
  2. Copyright Page
  3. Preface
  4. Acknowledgments
  5. About the Author
  6. Introduction
    1. THE THREE BASIC ELEMENTS OF A CASH FLOW MODEL
    2. THE PROCESS OF BUILDING A CASH FLOW MODEL
    3. HOW THIS BOOK IS DESIGNED
  7. CHAPTER 1 - Dates and Timing
    1. TIME PROGRESSION
    2. DATES AND TIMING ON THE INPUTS SHEET
    3. DAY-COUNT SYSTEMS: 30/360 VERSUS ACTUAL/360 VERSUS ACTUAL/365
    4. MODEL BUILDER 1.1: INPUTS SHEET—DATES AND TIMING
    5. DATES AND TIMING ON THE CASH FLOW SHEET
    6. MODEL BUILDER 1.2: CASH FLOW SHEET—DATES AND TIMING
    7. TOOLBOX
  8. CHAPTER 2 - Asset Cash Flow Generation
    1. LOAN LEVEL VERSUS REPRESENTATIVE LINE AMORTIZATION
    2. HOW ASSET GENERATION IS DEMONSTRATED IN MODEL BUILDER
    3. ASSET GENERATION ON THE INPUTS SHEET
    4. MODEL BUILDER 2.1: INPUTS SHEET ASSET ASSUMPTIONS AND THE VECTORS SHEET
    5. ASSET GENERATION ON THE CASH FLOW SHEET
    6. MODEL BUILDER 2.2: NOTIONAL ASSET AMORTIZATION ON THE CASH FLOW SHEET
    7. TOOLBOX
  9. CHAPTER 3 - Prepayments
    1. HOW PREPAYMENTS ARE TRACKED
    2. HISTORICAL PREPAYMENT DATA FORMATS
    3. BUILDING PREPAYMENT CURVES
    4. PREPAYMENT CURVES IN PROJECT MODEL BUILDER
    5. THE EFFECT OF PREPAYMENTS ON STRUCTURED TRANSACTIONS
    6. MODEL BUILDER 3.1: HISTORICAL PREPAYMENT ANALYSIS AND CREATING A PROJECTED ...
    7. MODEL BUILDER 3.2: INTEGRATING PROJECTED PREPAYMENTS IN ASSET AMORTIZATION
    8. TOOLBOX
  10. CHAPTER 4 - Delinquency, Default, and Loss Analysis
    1. DELINQUENCIES VERSUS DEFAULTS VERSUS LOSS
    2. THE IMPORTANCE OF ANALYZING DELINQUENCY
    3. MODEL BUILDER 4.1: BUILDING HISTORICAL DELINQUENCY CURVES
    4. DERIVING HISTORICAL LOSS CURVES
    5. MODEL BUILDER 4.2: BUILDING HISTORICAL AND PROJECTED LOSS CURVES
    6. ANALYZING HISTORICAL LOSS CURVES
    7. MODEL BUILDER 4.2 CONTINUED
    8. PROJECTING LOSS CURVES
    9. MODEL BUILDER 4.2 CONTINUED
    10. INTEGRATING LOSS PROJECTIONS
    11. MODEL BUILDER 4.3: INTEGRATING DEFAULTS IN ASSET AMORTIZATION
  11. CHAPTER 5 - Recoveries
    1. MODEL BUILDER 5.1: HISTORICAL RECOVERY ANALYSIS
    2. PROJECTING RECOVERIES IN A CASH FLOW MODEL
    3. MODEL BUILDER 5.2: INTEGRATING RECOVERIES INTO PROJECT MODEL BUILDER
    4. FINAL POINTS REGARDING RECOVERIES
  12. CHAPTER 6 - Liabilities and the Cash Flow Waterfall
    1. PRIORITY OF PAYMENTS AND THE CASH FLOW WATERFALL
    2. TYPES OF LIABILITIES
    3. MODEL BUILDER 6.1: CALCULATING FEES IN THE WATERFALL
    4. MODEL BUILDER 6.2: CALCULATING INTEREST IN THE WATERFALL
    5. MODEL BUILDER 6.3: CALCULATING PRINCIPAL IN THE WATERFALL
    6. UNDERSTANDING BASIC ASSET AND LIABILITY INTERACTIONS
  13. CHAPTER 7 - Advanced Liability Structures
    1. TRIGGERS AND THEIR AFFECT ON THE LIABILITY STRUCTURE
    2. MODEL BUILDER 7.1: INCORPORATING TRIGGERS
    3. SWAPS
    4. MODEL BUILDER 7.2: INCORPORATING A BASIC INTEREST RATE SWAP
    5. FINAL NOTES ON SWAPS
    6. RESERVE ACCOUNTS
    7. MODEL BUILDER 7.3: INCORPORATING A CASH-FUNDED RESERVE ACCOUNT
    8. CONCLUSION OF THE CASH FLOW WATERFALL
    9. TOOLBOX
  14. CHAPTER 8 - Analytics and Output Reporting
    1. INTERNAL TESTING
    2. MODEL BUILDER 8.1: CASH IN VERSUS CASH OUT TEST
    3. MODEL BUILDER 8.2: BALANCES AT MATURITY TESTS
    4. MODEL BUILDER 8.3: ASSET PRINCIPAL CHECK TEST
    5. PERFORMANCE ANALYTICS
    6. MODEL BUILDER 8.4: CALCULATING MONTHLY YIELD
    7. MODEL BUILDER 8.5: CALCULATING BOND-EQUIVALENT YIELD
    8. MODIFIED DURATION
    9. MODEL BUILDER 8.6: CALCULATING MODIFIED DURATION
    10. OUTPUT REPORTING
    11. MODEL BUILDER 8.7: CREATING THE OUTPUT REPORT
    12. TOOLBOX
  15. CHAPTER 9 - Understanding the Model
    1. THE COMPLETE MODEL IN REVIEW
    2. UNDERSTANDING THE EFFECTS OF INCREASED LOSS
    3. VARYING PRINCIPAL ALLOCATION METHODOLOGIES
    4. VARYING LOSS TIMING
    5. VARYING RECOVERY RATE AND LAG
    6. THE VALUE OF A SWAP
    7. ADDITIONAL TESTING
  16. CHAPTER 10 - Automation Using Visual Basic Applications (VBA)
    1. CONVENTIONS OF THIS CHAPTER
    2. THE VISUAL BASIC EDITOR
    3. VBA CODE
    4. SIMPLE AUTOMATION FOR PRINTING AND GOAL SEEK
    5. MODEL BUILDER 10.1: AUTOMATING PRINT PROCEDURES
    6. MODEL BUILDER 10.2: AUTOMATING GOAL SEEK TO OPTIMIZE ADVANCE RATES
    7. UNDERSTANDING LOOPING TO AUTOMATE THE ANALYTICS SHEET
    8. MODEL BUILDER 10.3: AUTOMATING GOAL SEEK TO PERFORM TRANSACTION ANALYTICS
    9. AUTOMATED SCENARIO GENERATION
    10. MODEL BUILDER 10.4: CREATING A TRANSACTION SCENARIO GENERATOR
    11. WORKING WITH MACROS IN EXCEL
  17. CHAPTER 11 - Conclusion
    1. THE INVESTMENT BANKER’S PERSPECTIVE
    2. THE INVESTOR’S PERSPECTIVE
    3. THE ISSUER’S PERSPECTIVE
    4. THE FINANCIAL GUARANTOR’S PERSPECTIVE
    5. THE BIG PICTURE PERSPECTIVE
  18. APPENDIX - Using This Book with Excel 2007
  19. About the CD-ROM
  20. Index

Product information

  • Title: Modeling Structured Finance Cash Flows with Microsoft® Excel®: A Step-by-Step Guide
  • Author(s): KEITH A. ALLMAN
  • Release date: March 2007
  • Publisher(s): Wiley
  • ISBN: 9780470042908