Structured Finance Modeling with Object-Oriented VBA

by Tick, Evan

• ISBN13: 9780470098592
• ISBN10: 0470098597
• Edition: 1st
• Format: Hardcover
• Copyright: 2007-05-25
• Publisher: Wiley

Summary

A detailed look at how object-oriented VBA should be used to model complex financial structures This guide helps readers overcome the difficult task of modeling complex financial structures and bridges the gap between professional C++/Java programmers writing production models and front-office analysts building Excel spreadsheet models. It reveals how to model financial structures using object-oriented VBA in an Excel environment, allowing desk-based analysts to quickly produce flexible and robust models. Filled with in-depth insight and expert advice, it skillfully illustrates the art of object-oriented programming for the explicit purpose of modeling structured products. Residential mortgage securitization is used as a unifying example throughout the text.

Author Biography

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.

Table of Contents

Preface	p. xi
List of Acronyms	p. xv
Acknowledgments	p. xvii
About the Author	p. xix
Cash-Flow Structures	p. 1
Getting Started	p. 1
Securitization	p. 3
Synthetic Structures	p. 10
Putting It All Together	p. 13
Modeling	p. 16
Dipping a Toe in the Shallow End	p. 17
Swimming Toward the Deep End	p. 22
Types	p. 29
Class Architecture	p. 33
Weak Inheritance	p. 37
Parameterized Class	p. 42
Which Is Better?	p. 43
Exercises	p. 46
Assets	p. 48
Replines	p. 49
Portfolio Optimization	p. 52
Zero-One Program	p. 53
Simulated Annealing	p. 56
Losses, Prepayments, and Interest Rates	p. 60
Cash-Flow Model	p. 61
Zero-Prepay Cash Flows	p. 63
Actual Cash Flows	p. 66
Examples	p. 74
S&P Cash-Flow Model	p. 75
Model Parameters	p. 77
Moody's Cash-Flow Model	p. 80
Model Parameters	p. 82
Algorithm	p. 84
Option ARMs	p. 86
Class Architecture: Multiple Inheritance	p. 89
Doing It in Excel: SumProduct	p. 94
Exercises	p. 94
Liabilities	p. 98
Getting Started	p. 98
Notation	p. 102
Expenses	p. 108
Interest	p. 110
Over-collateralization	p. 116
Current Subordinated Amount	p. 116
Stepdown Date	p. 118
Target Subordinated Amount	p. 119
Principal	p. 122
Gross Principal Distributions	p. 122
Detailed Principal Distributions	p. 124
Writedowns and Recoveries	p. 128
Derivatives	p. 130
Corridors	p. 132
Swaps	p. 134
Excess Reserve Fund Account	p. 135
Triggers	p. 137
Call Features	p. 138
Overcollateralization Test	p. 138
Interest Coverage Test	p. 139
Delinquency Trigger	p. 140
Loss Trigger	p. 141
Residuals: NIMs and Post-NIM	p. 141
Class Architecture	p. 144
Passive Approach	p. 144
Active Approach	p. 158
Comparison	p. 170
Doing It in Excel: Data Tables	p. 170
Exercises	p. 176
Sizing the Structure	p. 179
Senior Sizing	p. 182
Subordinate Sizing	p. 185
Fully Funded vs. Non-Fully Funded	p. 190
Optimizations and Complexity	p. 192
Example of Sizing	p. 196
NIM and OTE Sizing	p. 198
Class Architecture	p. 203
Inheritance Revisited	p. 203
Odds and Ends	p. 207
Doing It in Excel: Solver	p. 210
Exercises	p. 213
Analysis	p. 217
Risk Factors	p. 217
Prefunding	p. 217
Prepayments	p. 217
Buybacks and Cleanup Calls	p. 219
Defaults	p. 219
Interest Rates	p. 221
Spreads	p. 221
Miscellaneous	p. 222
Residual Sensitivities	p. 222
Mezzanine and Subordinate Classes	p. 223
NIM Classes	p. 230
Putting It All Together	p. 232
Exercises	p. 234
Stochastic Models	p. 235
Static versus Stochastic	p. 235
Loss Model	p. 238
Probability of Default from Transition Matrix	p. 238
Probability of Default from Spread	p. 241
Probability of Time to Default	p. 242
Gaussian Copula	p. 244
Monte Carlo Simulation	p. 249
Synthetic Credit Indexes	p. 251
Loss Lets	p. 253
Analysis	p. 256
Hedging	p. 264
Doing It in Excel	p. 270
Exercises	p. 279
Excel and VBA	p. 285
Spreadsheet Style	p. 286
Code Style	p. 290
Compilation	p. 295
Bloomberg	p. 299
Bond Math	p. 303
Mortgage Payment	p. 303
Yield to Price	p. 305
Price to Yield	p. 306
Duration	p. 307
Index or Interest-Rate Duration	p. 308
Discount Spread Duration	p. 308
Hazard Rate	p. 312
Static Credit Card Model	p. 315
References	p. 321
Index	p. 325
Table of Contents provided by Ingram. All Rights Reserved.