A Fast-Track to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA / Edition 1

Hardcover (Print)
Used and New from Other Sellers
Used and New from Other Sellers
from $57.00
Usually ships in 1-2 business days
(Save 50%)
Other sellers (Hardcover)
  • All (11) from $57.00   
  • New (6) from $76.76   
  • Used (5) from $57.00   

Overview

An introductory work to structured modeling in Visual Basic Application (VBA), A Fast-Track to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA provides you with a concise, immediate, and practical guide to developing a meaningful level of modeling expertise on problems in structured finance. Designed to allow you to cross from the pure Excel world to the much more powerful and flexible world of VBA/Excel, this straightforward book is built around a structured finance case study in asset-based securitization.

Starting with basic examples that progressively develop your confidence to take on more complicated tasks, this book teaches you how to break complex tasks down into simple tasks and then implement them into VBA code using a critical subset of the features of the language. This hands-on, step-by-step guide features coverage of:

  • General modeling concepts
  • The Cash Flow Waterfall in Excel code
  • Organizing and creating VBA modules to hold your model code
  • Generating VBA code from recorded macros, and how to simplify and extract it for use in your model
  • Efficiently using VBA statements to control the flow of the model
  • How to use template files for efficient and flexible report writing
  • Writing a collateral selection and reporting capability into your model
  • How to generate collateral cash flows on a loan-by-loan, period-by-period basis
  • Calculation optimization applied to collateral cash flow amortization
  • How to use the VBA debugger to detect and correct different types of errors
  • How to use the completed model to structure and size a finance deal
  • Modifying the model to allow multiple scenario runs
  • Protecting and evolving the model so it continues to create value for the business unit
  • How to add additional reporting and analytical features to the existing model
  • How to convert the structuring model into a monitoring and valuation model
  • Allowing for the use of monthly empirical performance information
  • Use of the monitoring and valuation model to track the performance of the deal
  • Valuing the deal based on its performance, market conditions, and rating criteria

Accompanied by an online resource containing a complete working model and all the data and files professionals need to run it, A Fast-Track to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA is the only book written for the complete novice, enhancing your ability to break complex, seemingly insoluble problems down into a set of manageable tasks, through the use of VBA.

Read More Show Less

Product Details

  • ISBN-13: 9780470398128
  • Publisher: Wiley
  • Publication date: 2/9/2009
  • Series: Wiley Finance Series, #487
  • Edition description: New Edition
  • Edition number: 1
  • Pages: 744
  • Sales rank: 1,169,766
  • Product dimensions: 7.50 (w) x 10.00 (h) x 2.40 (d)

Meet the Author

William Preinitz is a Managing Director with Citigroup Global Markets' Global Special Situations Group. He oversees new business origination-structured finance issuance, serv-icing operations, rating agency relationships, credit wrap providers, investors, analytical development, internal risk control, audit activities, hedging, and residual arbitrage activities.

Read More Show Less

Table of Contents

Preface xvii

About the Web Site xxi

PART ONE Introduction 1

CHAPTER 1 Why? What? Who? Where? and How? 3

The Immortal Question(s) 3

What Are the Advantages of Learning VBA? 6

What Are the Disadvantages of Learning VBA? 8

What Is a Model? 10

Why Is Modeling a Valuable Skill? 12

What Are the Stages of Model Design and Construction? 12

Other Aspects of Modeling 20

Perspective of This Book 22

Structure of the Book 23

Putting the Deliverables “On the Target” 24

CHAPTER 2 Common Sense 27

Overview 27

Deliverables 27

Do Not Eat Anything Bigger Than Your Head 27

You Only Hurt the One You Love 28

It is OK to Be Right; Just Do Not Be Dead-Right 29

Know When to Hold ‘Em 30

Next Steps 30

PART TWO The Securitization Process 31

CHAPTER 3 Securitizing a Loan Portfolio 33

Overview 33

Deliverables 34

Financing a Loan Portfolio 34

Description of the Collateral 37

Collateral Cash Flows 39

Expense and Liability Structure 43

Measuring the Performance of the Structure 45

Functional Requirements of the Model 46

The Role of the Model in the Process of Securitization 47

Deliverables Checklist 49

Next Steps 49

On the Web Site 49

PART THREE Designing the Model 51

CHAPTER 4 Understanding the Excel Waterfall 53

Overview 53

Deliverables 54

Under Construction 54

Waterfalls 54

Structure of the Cash Flow Waterfall 55

Collateral Cash Flows Section 56

Deal Expenses Section 58

Conduit Interest Section 59

Conduit Principal Section 60

Excess Cash Treatment Section 60

Conduit Summary Section 61

Delinquency Reserve Section 63

Deal Triggers Section 65

Debt Costs Section 66

Default Tests Section 66

Debt Performance Calculations Support Section 67

Deal Wind-down Trigger 68

Cash Flow Waterfall “Box Score” Section 70

Deliverables Checklist 72

Next Steps 72

On the Web Site 72

CHAPTER 5 Designing the VBA Model 73

Overview 73

Deliverables 74

Under Construction 75

What Are the Desired Results? 75

What Processes Must the Model Perform? 76

Introduction to Template Files 77

Collateral Selection Reports 81

Ineligible Collateral Reports 82

Eligible Collateral Reports 85

Cash Flow Waterfall Reports 89

Cash Flow Matrix Reports 89

Designing the Menus 89

Designing the Collateral Selection Output Screen 94

It Should Look Like This 94

Deliverables Checklist 94

Next Steps 95

On the Web Site 95

PART FOUR Learning the VBA Language 97

CHAPTER 6 Laying the Model Groundwork 99

Overview 99

Deliverables 101

Under Construction 101

Creating the External Model Environment 102

Creating the Internal Model Environment 105

Writing the Main Program in Pseudo Code 112

Deliverables Checklist 114

Next Steps 114

On the Web Site 114

CHAPTER 7 Recorded Macros: A First Look at the VBA Language 117

Overview 117

Deliverables 118

Under Construction 119

Recording VBA Code 119

Running the Edited Code 128

Using Recorded Macros to Build a Simple Model 134

Deliverables Checklist 135

Next Steps 136

On the Web Site 136

CHAPTER 8 Writing Menus: An Introduction to Data, Ranges, Arrays, and Objects 139

Overview 139

Deliverables 139

Under Construction 141

Role of the Menu 142

Structural Elements of a Menu 145

Introduction to VBA Variables 147

Variable Types 147

How to Create and Use Variables 148

Scope of Variables 152

Assigning Values to Variables 156

Constants 157

Option Explicit Statement 161

Performing Calculations with Variables 161

Ranges 163

Objects 171

Building a Menu from Scratch 173

Deliverables Checklist 179

Next Steps 180

On the Web Site 180

CHAPTER 9 Controlling the Flow of the Model 183

Overview 183

Deliverables 183

Under Construction 185

Decision Structures 185

Looping Structures 192

Subroutines 199

Functions 203

Deliverables Checklist 205

Next Steps 206

On the Web Site 206

CHAPTER 10 Building Messaging Capabilities 207

Overview 207

Deliverables 207

Under Construction 208

Progress Messages 208

Error Messages 211

Run-time Option Error Checking 223

Deliverables Checklist 224

Next Steps 225

On the Web Site 225

CHAPTER 11 Designing the Model’s Reports 227

Overview 227

Deliverables 227

Under Construction 227

Collateral Reporting Activity 228

Ineligible Collateral Reporting 229

Eligible Collateral Reporting 235

Cash Flow Waterfall Report 236

Deal Summary Matrix Reports 237

Matrix Report Package 239

Assumptions Report 239

Deliverables Checklist 240

Next Steps 241

On the Web Site 241

PART FIVE Writing the Model 243

CHAPTER 12 Main Program and Menus 245

Overview 245

Deliverables 246

Under Construction 247

Main Program 249

Typical Subroutine Called by the Main Program 252

Three Subroutine Calls from the Main Program 252

Building Menu Error Checking 253

Reading the Contents of All the Menus 257

Reading the Contents of the Collateral File 264

Deliverables Checklist 266

Next Steps 268

On the Web Site 268

CHAPTER 13 Writing the Collateral Selection Code 271

Overview 271

Deliverables 271

Under Construction 272

The Collateral Reporting Package 273

Ineligible Collateral Report Package 278

Producing the Ineligibility Report Package 289

Helping Make It All Happen 304

Eligible Collateral Report Package 305

Writing Out the Assumptions of the Model Run 316

Deliverables Checklist 318

Next Steps 318

On the Web Site 318

CHAPTER 14 Calculating the Cash Flows 321

Overview 321

Deliverables 321

Under Construction 323

Pause Awhile and Reflect 323

Overview of the Cash Flow Generator 333

Writing the VBA Code 336

Setting Up the Scenario Loops 355

Cash Flow Calculation Sequence 356

Building a Cash Flow Trace Capability 361

Concluding Remarks 362

Deliverables Checklist 362

Next Steps 363

On the Web Site 363

CHAPTER 15 Running the Waterfall: Producing Initial Results 365

Overview 365

Deliverables 366

Under Construction 367

Running the Waterfall Spreadsheet 370

Loading the Collateral Cashflows 370

Capturing the Waterfall Results 373

Reporting the Summary Report or “Box Score” Information 374

Loading the Matrix Reports Information 375

Producing the Waterfall Spreadsheet Report Package 376

Producing the Matrix Report Package 382

Deliverables Checklist 386

Next Steps 387

On the Web Site 387

CHAPTER 16 Debugging the Model 389

Overview 389

Deliverables 390

Under Construction 390

Compiling the Model 390

Types of VBA Errors 391

Common Syntax Errors 393

Common Compile Errors 393

How to Run the VBA Debugger 395

Runtime Errors 404

Logical Errors 406

Deliverables Checklist 412

Next Steps 412

On the Web Site 413

PART SIX Testing, Use, and Deployment 415

CHAPTER 17 Validating the Model 417

Overview 417

Deliverables 417

Under Construction 418

Steps in the Validation Procedure 420

Validating the Menu Error Checking Code 421

Validating the Menu Inputs 422

Validating the Code for Reading the Portfolio Data File 423

Validating the Collateral Selection Code 423

Validating the Eligible Collateral Reporting Code 427

Validating the Floating Rate Loan Reset Period Levels 428

Validating the Cash Flow Calculation Code 439

Validating the Cash Flow Waterfall Spreadsheet 449

Future Back Testing Activity 452

Validation Activities by Third Parties 453

Deliverables Checklist 453

Next Steps 453

On the Web Site 453

CHAPTER 18 Running the Model 455

Overview 455

Deliverables 456

Under Construction 456

Installing a “Run Button” 457

Steps of the Structuring Process 458

Organizing the Output 459

Step 1: Determine Composition of Proposed Collateral Portfolio 460

Step 2: Initial Collateral Selection 462

Step 3: Addressing Portfolio Concentration Issues 464

Step 4: Correcting Concentration Issues 467

Step 5: Running the Expected Case Cash Flows 468

Step 6: Expected Case Sensitivity Analysis 472

Time Out! Building a Batch Processing Capability 474

Sensitivity Analysis Results 477

Help! A Post-Processing Program 487

Step 7: Rating Agency Stress Tests 488

Step 8: Adjusting the Seller Interest 490

Sizing Results Table 492

We Are Done! 493

Deliverables Checklist 493

Next Steps 493

On the Web Site 494

Extractor.xls Program 495

Batch Program Inputs Menu Entries 499

CHAPTER 19 Building Additional Capabilities 503

Overview 503

Under Construction 503

Deliverables 504

Peek-A-Boo! Immediate Access to Model Results 504

Single Scenario Report File 506

Ability to Run Interest Rate Sensitivities 513

Adding Information to the Assumptions Page 529

Next Steps 534

On the Web Site 534

PART SEVEN After the Model Is Written 537

CHAPTER 20 Documentation of the Model 539

Overview 539

Deliverables 540

Under Construction 540

Online Documentation 540

External Documentation 545

User Training Documentation 546

Deliverables Checklist 546

Next Steps 547

On the Web Site 547

CHAPTER 21 Managing the Growth of the Model 549

Overview 549

Deliverables 549

Under Construction 550

Maintaining the Model Environment 550

Streamlining the Validation and Promotion Process 554

Evolution of the Form of the Model 557

Evolution of the Structure of the Model 558

Deliverables Checklist 558

Next Steps 559

On the Web Site 559

PART EIGHT Risk Assessment and Valuation 561

CHAPTER 22 Building Portfolio Monitoring Model 563

Overview 563

Deliverables 563

Under Construction 564

How Much of the Structuring Model Can We Use? 566

Why Two Models? 567

Designing the New Report Package 568

You Can’t Make an Omelet Without Breaking Some Eggs 576

Modifying Some of the Code 594

What a Brave New Model That Has Such Features in It! 606

Validating the Model 613

Deliverables Checklist 614

Next Steps 614

On the Web Site 614

CHAPTER 23 Valuation Techniques: How Do We Determine Price? 615

Overview 615

Deliverables 616

Under Construction 616

Introduction to Pricing a Security 617

Assessing Risk 622

Adding the Pricing Functionality to the Monitoring Model 627

Deliverables Checklist 636

Next Steps 637

On the Web Site 637

CHAPTER 24 Challenging Times for the Deal 639

Overview 639

Deliverables 639

Under Construction 639

Using the Monitoring Model to Assess the Performance and Value of the Deal 639

Three Months from Issuance 643

Six Months from Issuance 644

Nine Months from Issuance 646

Twelve Months from Issuance 648

Fifteen Months from Issuance 654

Eighteen Months from Issuance 655

Twenty-One Months from Issuance 659

Twenty-Four Months from Issuance 660

End of the Exercise 662

On the Web Site 664

PART NINE Farewell 665

CHAPTER 25 Parting Admonitions 667

What We Have Learned 667

Next Steps 669

APPENDIX A Mortgage Math 671

Overview 671

Loan Amortization Terms 672

Components of the Cash Flows of a Loan 674

Next Steps 688

APPENDIX B Bond Math 689

Overview 689

Future Value 690

Present Value 693

Tenor Measurements 695

Yield and Return Measurements 697

Loss and Coverage Measurements 701

Next Steps 705

Exhibits Index 707

Subject Index 727

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)