Structured Finance Modeling with Object-Oriented VBA

Evan Tick is a director at IXIS Capital Markets, and has worked on Wall Street for ten years. His expertise is fixed income and structured finance modeling in the areas of risk management, asset-backed securities (ABS), residential mortgages, and credit derivatives.

• Preface xiList of Acronyms xvAcknowledgments xviiAbout the Author xixChapter 1 Cash-Flow Structures 11.1 Getting Started 11.2 Securitization 31.3 Synthetic Structures 101.4 Putting It All Together 13Chapter 2 Modeling 162.1 Dipping a Toe in the Shallow End 172.2 Swimming Toward the Deep End 222.3 Types 292.4 Class Architecture 332.4.1 Weak Inheritance 372.4.2 Parameterized Class 422.4.3 Which Is Better? 432.5 Exercises 46Chapter 3 Assets 483.1 Replines 493.2 Portfolio Optimization 523.2.1 Zero-One Program 533.2.2 Simulated Annealing 563.3 Losses, Prepayments, and Interest Rates 603.4 Cash-Flow Model 613.4.1 Zero-Prepay Cash Flows 633.4.2 Actual Cash Flows 663.4.3 Examples 743.5 S&P Cash-Flow Model 753.5.1 Model Parameters 773.6 Moody’s Cash-Flow Model 803.6.1 Model Parameters 823.6.2 Algorithm 843.7 Option ARMs 863.8 Class Architecture: Multiple Inheritance 893.9 Doing It in Excel: SumProduct 943.10 Exercises 94Chapter 4 Liabilities 984.1 Getting Started 984.2 Notation 1024.3 Expenses 1084.4 Interest 1104.5 Over-collateralization 1164.5.1 Current Subordinated Amount 1164.5.2 Stepdown Date 1184.5.3 Target Subordinated Amount 1194.6 Principal 1224.6.1 Gross Principal Distributions 1224.6.2 Detailed Principal Distributions 1244.7 Writedowns and Recoveries 1284.8 Derivatives 1304.8.1 Corridors 1324.8.2 Swaps 1344.8.3 Excess Reserve Fund Account 1354.9 Triggers 1374.9.1 Call Features 1384.9.2 Overcollateralization Test 1384.9.3 Interest Coverage Test 1394.9.4 Delinquency Trigger 1404.9.5 Loss Trigger 1414.10 Residuals: NIMs and Post-NIM 1414.11 Class Architecture 1444.11.1 Passive Approach 1444.11.2 Active Approach 1584.11.3 Comparison 1704.12 Doing It in Excel: Data Tables 1704.13 Exercises 176Chapter 5 Sizing the Structure 1795.1 Senior Sizing 1825.2 Subordinate Sizing 1855.2.1 Fully Funded vs. Non–Fully Funded 1905.3 Optimizations and Complexity 1925.4 Example of Sizing 1965.5 NIM and OTE Sizing 1985.6 Class Architecture 2035.6.1 Inheritance Revisited 2035.6.2 Odds and Ends 2075.7 Doing It in Excel: Solver 2105.8 Exercises 213Chapter 6 Analysis 2176.1 Risk Factors 2176.1.1 Prefunding 2176.1.2 Prepayments 2176.1.3 Buybacks and Cleanup Calls 2196.1.4 Defaults 2196.1.5 Interest Rates 2216.1.6 Spreads 2216.1.7 Miscellaneous 2226.1.8 Residual Sensitivities 2226.2 Mezzanine and Subordinate Classes 2236.3 NIM Classes 2306.4 Putting It All Together 2326.5 Exercises 234Chapter 7 Stochastic Models 2357.1 Static versus Stochastic 2357.2 Loss Model 2387.2.1 Probability of Default from Transition Matrix 2387.2.2 Probability of Default from Spread 2417.2.3 Probability of Time to Default 2427.3 Gaussian Copula 2447.4 Monte Carlo Simulation 2497.5 Synthetic Credit Indexes 2517.5.1 Loss Lets 2537.5.2 Analysis 2567.5.3 Hedging 2647.6 Doing It in Excel 2707.7 Exercises 279Appendix A Excel and VBA 285A.1 Spreadsheet Style 286A.2 Code Style 290A.3 Compilation 295A.4 Bloomberg 299Appendix B Bond Math 303B.1 Mortgage Payment 303B.2 Yield to Price 305B.3 Price to Yield 306B.4 Duration 307B.4.1 Index or Interest-Rate Duration 308B.4.2 Discount Spread Duration 308B.5 Hazard Rate 312B.6 Static Credit Card Model 315References 321Index 325