Select Page

Enstruct’s 4-Day Structured Finance Analysis training expands upon the 3-day course by integrating instruction on Visual Basic Programming (VBA). The use of VBA allows users to complete loan level default, prepayment, and recovery analyses.  Participants further learn how to amortize a loan in VBA, which is then expanded to teach how to amortize hundreds of thousands of loans and break free from a representative line approach. After this course participants can model nearly any structured finance transaction in detail.

##### Introduction to Structured Financial Modeling

I. What is Financial Modeling?
II. Why Professionals Build Models
III. Which Models to Implement
IV. Basic Model Layout & Design

Exercise: Working with a Bad Model

##### Building a Toolkit

I. Time Value of Money Functions
II. Mathematical/Statistical Functions
III. Logical Functions
IV. Lookup Functions
V. Naming Ranges / Data Validation Lists
VI. Function Combinations

Exercise: Time Value of Money Functions
Exercise: Mathematical/Statistical Functions
Exercise: Logical functions
Exercise: Lookup Functions
Exercise: Naming Ranges / Data Validation Lists

##### Scenario Selectors: The First Step in a Model

I. Overview of a Scenario Selector
II. Specific Scenario Selector Setup & Use

Exercise: Scenario Selectors

##### Dates and Timing: The Framework for a Model

I. Overview of Dates and Timing Set Up
II. Specific Date and Timing Functions and Organization

Exercise: Dates & Timing

##### Data Requirements and Data Management

I. Tape Cracking & Stratification Reporting

Exercise: Creating Stratification Reports

##### Introduction to Performance History

I. Delinquency: How to Measure Historical Delinquency for Trigger
II. Transition Matrices

Exercise: Creating a Transition Matrix

III. Default: Static Loss Analysis

Exercise: Creating an Aggregated Static Loss Analysis

##### Introduction to Object-Oriented Programming

I. Introduction to Visual Basic Applications (VBA)
II. Variables
III. Moving Data between Excel & VBA

Exercise: Simple Data Movement & Storage

IV. IF THEN ELSE Statements
V. FOR NEXT Loops
VI. Functions and Subroutines

##### Revisiting Performance History with VBA

I. Static Loss Analysis Using VBA

Exercise: Creating a Static Loss Based on Loan Level Information

II. Prepayments: How to Measure Historical Prepayments & Projecting Future Prepayment Rates

Exercise: Historical Prepayment Analysis & Simple Regression

III. Recoveries: Rates & Lags
Exercise: Analyzing Historical Recovery Rates & Lags

##### Asset Amortization

I. Basic Theory & Application

Exercise: Fixed Rate, Floating Rate, & Interest Only
Amortization

II. Amortizing Assets in VBA

Exercise: Amortizing a Single Loan in VBA

III. Complete Loan Level Amortization

Exercise: Amortizing an Unlimited Number of Loans in VBA

IV. Integrating Loan Level Amortization into a Structuring Model

Exercise: Working with a Structuring Model

##### Liabilities and Priority of Payments

I. Liabilities & the Cash Flow Waterfall: Fees, Interest and Principal

Exercise: Transforming a Legal Priority of Payments into Excel

II. Liability Amortization Methodologies: Pro Rata versus Sequential

Exercise: Implementing Multiple Payment Priorities

III. Advanced Liability Features: Swaps, Reserve Accounts, and Triggers

Exercise: Building in a Fixed for Floating Swap
Exercise: How to Set Up Reserve Accounts
Exercise: Creating and Using Triggers in a Model

##### Deal Analytics and Reporting

I. Internal Tests to Ensure Model Integrity
II. Appropriate Outputs for a Structured Finance Model
III. Controlling the Output System

Exercise: Bond Pricing, Weighted Average Life, and Yields
Exercise: Implementing Automated Output Reporting

##### Sensitivity Scenarios

I. Optimizing Tranches for Given Risk Ratings

Exercise: Optimizing a Tranche under Different Scenarios

Rating Agency Methodologies: First Dollar of Loss vs. Expected Default