Structured Finance Modeling with Object-Oriented VBA

by Evan Tick

Overview

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.

Product Details

ISBN-13:	9781118160664
Publisher:	Wiley
Publication date:	07/28/2011
Series:	Wiley Finance , #390
Sold by:	JOHN WILEY & SONS
Format:	eBook
Pages:	352
File size:	17 MB
Note:	This product may take a few minutes to download.

About the Author

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.

Read an Excerpt

Click to read or download

Table of Contents

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