Intermediate Structured Finance Modeling Leveraging Excel, VBA, Access, and Powerpoint

• Main Authors: Preinitz, William, Niedermaier, Matthew
• Format: eBook
• Language: English
• Published: Newark John Wiley & Sons, Incorporated 2011; Wiley-Blackwell
• Edition: 1
• Series: Wiley finance series
• Subjects: Computer simulation; Finance

After first outlining the fundamental concepts of asset backed securitisation, this book presents a basic Structured finance model that can be downloaded from the companion website and run in a series of cases.

Table of Contents:

• CHAPTER 7 Establishing the Model Environment -- Overview -- Deliverables -- Under Construction -- Importance of a Standardized Directory Structure -- Creating Directories and Defining Their Functions -- Operating Directories -- Administrative Directories -- Creating New Directories for the Model as We Need Them -- On the Web Site -- PART TWO Building the New Assets Model -- CHAPTER 8 Designing the New Collateral Cash Flow Generator -- Overview -- Deliverables -- Under Construction -- Improving the CCFG Menus: Conversion to UserForms -- Improving the CCFG Data-Handling Capabilities -- Improving the CCFG Collateral Selection Process -- Improving the CCFG Cash Flow Generation Process -- Improving the CCFG Report Generation Process -- Improving the CCFG Messaging Process -- On the Web Site -- CHAPTER 9 Writing the CCFG Menus and Data Sheets -- Overview -- Deliverables -- Under Construction -- Menus and UserForms -- Menus of the CCFG -- Main Menu -- Run Options Menu -- Collateral Pool Menu -- Collateral Geographic Selection Criteria Menu -- Financial Selection Criteria Menu -- Cash Flow Amortization Parameters Menu -- Collateral Reports Menu -- On the Web Site -- CHAPTER 10 Writing the Collateral Data Handling Code -- Overview -- Deliverables -- Under Construction -- Managing Multiple Portfolio Files -- Initial Data Screening -- Writing the Screening Process VBA Code -- Writing the Initial Data Screening Reports -- Writing the Demographic Methodology Risk Reports -- Building a Representative Line Generator Program -- Building the Rep Line Generator Program -- On the Web Site -- CHAPTER 11 Writing the Collateral Selection Code -- Overview -- Deliverables -- Under Construction -- Building the Code -- Financial and Demographic Selection Code -- Financial Collateral Selection Process Reporting -- Geographic Selection Code -- Geographic Concentration Code
• PART FOUR Access, PowerPoint, and Outlook -- CHAPTER 17 Access: An Introduction -- Overview -- Deliverables -- Under Construction -- Basics of Access -- Opening Access and Creating a Database -- Access Tables -- Getting the Data In: Entering Data -- Getting the Data Back Out: Queries -- Basics of Structured Query Language -- Concluding Remarks -- On the Web Site -- CHAPTER 18 Implementing Access in the CCFG and LWM -- Overview -- Deliverables -- Under Construction -- Access and the Collateral Cash Flow Generator -- Collateral Portfolio Data -- Geographic Selection Criteria -- Geographic Concentration Criteria -- Initial Data Screening Criteria -- Custom Financial Selection Criteria -- Run Options Elections -- Generating the Loan Portfolio -- Monthly Cash Flows Scenarios -- Access in CCFG Reporting -- Access and the Liabilities Waterfall Model -- Connecting the LWM to the CCFG -- Structure Worksheet Objects -- Retrieving the Monthly Cash Flow Scenarios -- Storing LWM Run Results in the Database -- Concluding Remarks -- On the Web Site -- CHAPTER 19 Implementing PowerPoint and Outlook in the CCFG -- Overview -- Deliverables -- Under Construction -- Using PowerPoint in the CCFG -- Data Requirements -- Adding a New Class of Reports -- Introduction to PowerPoint in VBA -- Preparing the Data for Use -- First Time Homeowners Slide -- Creating the Null Report -- MS Outlook -- On the Web Site -- PART FIVE Running the CCFG and the LWM -- CHAPTER 20 Running the Models -- Overview -- Deliverables -- Under Construction -- Running the CCFG, Analyzing the Asset Side of the Deal -- Liabilities Side of the Deal -- Preparing a PowerPoint Presentation about Geographic Concentration -- On the Web Site -- Afterword -- Exhibits Index -- Subject Index
• Intro -- Intermediate Structured Finance Modeling -- Contents -- Preface -- Goals of the Book -- What You Should Know -- Setting the Context for Learning -- The Structure of the Book -- A Fish Story -- A Perspective on Modeling -- Approaching This Material -- Style -- A Parting Remark -- On the Web Site -- Acknowledgments -- PART ONE First Steps -- CHAPTER 1 Introduction -- Overview -- Why Was This Book Written? -- Who Is the Target Audience? -- What Is the Purpose of the Book? -- Expanding Your Software Skills -- Expanding Your Model Design Skills -- Expanding Your Finance Knowledge -- Organized to Teach -- Chapter Organization -- Accompanying Web Site -- Learning the "Hard" Way -- Note -- CHAPTER 2 The Existing Model -- Overview -- Deliverables -- Under Construction -- Crisis du Jour -- Overview of the Current Model -- Current Model Environment -- On the Web Site -- CHAPTER 3 Conventions and Advice -- Overview -- Deliverables -- VBA Conventions -- Common Sense -- On the Web Site -- CHAPTER 4 Segregation of the Existing Model's Functionality -- Overview -- Deliverables -- Under Construction -- Deliverables Checklist -- Breaking Up Is Hard to Do -- Accommodating Our Design Needs -- Advantages of Functional Segregation -- Disadvantages of Functional Segregation -- On the Web Site -- CHAPTER 5 Creating the Base Asset Model -- Overview -- Deliverables -- Under Construction -- The Big Picture: "Just the Assets, Ma'am" -- Stepping through the Model -- Testing the Completed Base Asset Model -- On the Web Site -- CHAPTER 6 Building the Base Liabilities Model -- Overview -- Deliverables -- Under Construction -- Liabilities Side of the Model -- What to Leave In -- Stepping through the Model -- Reading the Cash Flows and Assumptions from a File -- Testing the Completed Base Liabilities Model -- On the Web Site
• Introduction to Geographic Reporting -- On the Web Site -- CHAPTER 12 Writing the Collateral Cash Flow Amortization Code -- Overview -- Deliverables -- Under Construction -- Quick Review of Existing CF Generation Code -- Adding New Mortgage Types -- New Prepayment and Default Methodologies -- Uniform Methodology -- Geographic Methodology -- Demographic Methodology -- Reporting the Results of the Cash Flow Calculations -- Writing the Assumptions Report Package -- On the Web Site -- CHAPTER 13 Writing the CCFG Reporting Capability -- Overview -- Deliverables -- Under Construction -- Recapping the CCFG Report Package -- CCFG Activities and Reports -- Eligible Collateral Assessment Process -- Geographic/Demographic Reports -- Presentation Reports -- On the Web Site -- PART THREE Building the New Liabilities Model -- CHAPTER 14 Designing the Liabilities Waterfall Model -- Overview -- Deliverables -- Under Construction -- Structure of the Deal: Sources and Uses of Funds -- Design Elements of the LWM -- Main Menu -- Report Package Menu UserForm -- Structure Inputs Menu -- Liabilities Waterfall Worksheet -- Performance Summary Page -- VBA Code Modules -- Report Template Files -- On the Web Site -- CHAPTER 15 Writing the Liabilities Waterfall Model Spreadsheet -- Overview -- Deliverables -- Under Construction -- Deal Structure Inputs -- Building the Liabilities Waterfall Model -- Period Factors Worksheet -- Total Cash Flows Section -- Results Page Worksheet -- On the Web Site -- CHAPTER 16 Writing the LWM VBA Code -- Overview -- Deliverables -- Under Construction -- VBA Requirements of the LWM -- Menus of the LWM -- Main Program -- Error Checking the Main Menu Inputs -- Importing the Liability Structure Inputs -- Importing the Cash Flows of the CCFG -- Loading and Running the LWM -- Reporting the Results -- Other Functionality -- On the Web Site