{"product_id":"modeling-structured-finance-cash-flows-with-microsoft-excel-isbn-9780470042908","title":"Modeling Structured Finance Cash Flows with Microsoft Excel","description":"\u003cb\u003eA practical guide to building fully operational financial cash flow models for structured finance transactions\u003c\/b\u003e \u003cp\u003eStructured finance and securitization deals are becoming more commonplace on Wall Street. Up until now, however, market participants have had to create their own models to analyze these deals, and new entrants have had to learn as they go. \u003ci\u003eModeling Structured Finance Cash Flows with Microsoft Excel\u003c\/i\u003e provides readers with the information they need to build a cash flow model for structured finance and securitization deals. Financial professional Keith Allman explains individual functions and formulas, while also explaining the theory behind the spreadsheets. Each chapter begins with a discussion of theory, followed by a section called \"Model Builder,\" in which Allman translates the theory into functions and formulas. In addition, the companion website features all of the modeling exercises, as well as a final version of the model that is created in the text.\u003c\/p\u003e \u003cp\u003e\u003cb\u003eNote:\u003c\/b\u003e Companion website and other supplementary materials are not included as part of eBook file.\u003c\/p\u003e \u003cp\u003ePreface xi\u003c\/p\u003e \u003cp\u003eAcknowledgments xiii\u003c\/p\u003e \u003cp\u003eAbout the Author xv\u003c\/p\u003e \u003cp\u003e\u003cb\u003eIntroduction 1\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eThe Three Basic Elements of a Cash Flow Model 3\u003c\/p\u003e \u003cp\u003eInputs 3\u003c\/p\u003e \u003cp\u003eCash Flow Structure 4\u003c\/p\u003e \u003cp\u003eOutputs 5\u003c\/p\u003e \u003cp\u003eThe Process of Building a Cash Flow Model 5\u003c\/p\u003e \u003cp\u003ePlan and Design 5\u003c\/p\u003e \u003cp\u003eObtain All Necessary Information 6\u003c\/p\u003e \u003cp\u003eConstruct Basic Framework 6\u003c\/p\u003e \u003cp\u003eDevelop Advanced Structure 6\u003c\/p\u003e \u003cp\u003eValidate Assumptions 6\u003c\/p\u003e \u003cp\u003eTest Model 7\u003c\/p\u003e \u003cp\u003eHow This Book Is Designed 7\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 1 Dates and Timing 9\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eTime Progression 9\u003c\/p\u003e \u003cp\u003eDates and Timing on the Inputs Sheet 10\u003c\/p\u003e \u003cp\u003eDay-Count Systems: 30\/360 versus Actual\/360 versus Actual\/ 365 11\u003c\/p\u003e \u003cp\u003eModel Builder 1.1: Inputs Sheet—Dates and Timing 12\u003c\/p\u003e \u003cp\u003eDates and Timing on the Cash Flow Sheet 14\u003c\/p\u003e \u003cp\u003eModel Builder 1.2: Cash Flow Sheet—Dates and Timing 15\u003c\/p\u003e \u003cp\u003eToolbox 18\u003c\/p\u003e \u003cp\u003eNaming Cells and Ranges 18\u003c\/p\u003e \u003cp\u003eData Validation Lists 19\u003c\/p\u003e \u003cp\u003eEDATE 21\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 2 Asset Cash Flow Generation 23\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eLoan Level versus Representative Line Amortization 23\u003c\/p\u003e \u003cp\u003eHow Asset Generation Is Demonstrated in Model Builder 27\u003c\/p\u003e \u003cp\u003eAsset Generation on the Inputs Sheet 27\u003c\/p\u003e \u003cp\u003eFixed Rate Amortization Inputs 28\u003c\/p\u003e \u003cp\u003eFloating Rate Amortization Inputs 28\u003c\/p\u003e \u003cp\u003eModel Builder 2.1: Inputs Sheet Asset Assumptions and the Vectors Sheet 29\u003c\/p\u003e \u003cp\u003eAsset Generation on the Cash Flow Sheet 33\u003c\/p\u003e \u003cp\u003eModel Builder 2.2: Notional Asset Amortization on the Cash Flow Sheet 33\u003c\/p\u003e \u003cp\u003eTOOLBOX 40\u003c\/p\u003e \u003cp\u003eOFFSET 40\u003c\/p\u003e \u003cp\u003eMATCH 40\u003c\/p\u003e \u003cp\u003eMOD 41\u003c\/p\u003e \u003cp\u003ePMT 41\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 3 Prepayments 43\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eHow Prepayments Are Tracked 43\u003c\/p\u003e \u003cp\u003eSMM: Single Monthly Mortality 44\u003c\/p\u003e \u003cp\u003eCPR: Conditional Prepayment Rate 44\u003c\/p\u003e \u003cp\u003ePSA: Public Securities Association 44\u003c\/p\u003e \u003cp\u003eABS: Absolute Prepayment Speed 45\u003c\/p\u003e \u003cp\u003eHistorical Prepayment Data Formats 46\u003c\/p\u003e \u003cp\u003eBuilding Prepayment Curves 46\u003c\/p\u003e \u003cp\u003ePrepayment Curves in Project Model Builder 47\u003c\/p\u003e \u003cp\u003eThe Effect of Prepayments on Structured Transactions 48\u003c\/p\u003e \u003cp\u003eModel Builder 3.1: Historical Prepayment Analysis and Creating a Projected Prepayment Curve 48\u003c\/p\u003e \u003cp\u003eModel Builder 3.2: Integrating Projected Prepayments in Asset Amortization 53\u003c\/p\u003e \u003cp\u003eToolbox 56\u003c\/p\u003e \u003cp\u003eWeighted Averages Using SUMPRODUCT and SUM 56\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 4 Delinquency, Default, and Loss Analysis 59\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eDelinquencies versus Defaults versus Loss 59\u003c\/p\u003e \u003cp\u003eThe Importance of Analyzing Delinquency 60\u003c\/p\u003e \u003cp\u003eModel Builder 4.1: Building Historical Delinquency Curves 62\u003c\/p\u003e \u003cp\u003eDeriving Historical Loss Curves 64\u003c\/p\u003e \u003cp\u003eModel Builder 4.2: Building Historical and Projected Loss Curves 67\u003c\/p\u003e \u003cp\u003eAnalyzing Historical Loss Curves 69\u003c\/p\u003e \u003cp\u003eModel Builder 4.2 Continued 69\u003c\/p\u003e \u003cp\u003eProjecting Loss Curves 70\u003c\/p\u003e \u003cp\u003eModel Builder 4.2 Continued 71\u003c\/p\u003e \u003cp\u003eIntegrating Loss Projections 73\u003c\/p\u003e \u003cp\u003eThe Effects of Seasoning and Default Timing 75\u003c\/p\u003e \u003cp\u003eModel Builder 4.3: Integrating Defaults in Asset Amortization 76\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 5 Recoveries 83\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eModel Builder 5.1: Historical Recovery Analysis 85\u003c\/p\u003e \u003cp\u003eProjecting Recoveries in a Cash Flow Model 86\u003c\/p\u003e \u003cp\u003eModel Builder 5.2: Integrating Recoveries into Project Model Builder 87\u003c\/p\u003e \u003cp\u003eFinal Points Regarding Recoveries 88\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 6 Liabilities and the Cash Flow Waterfall 89\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003ePriority of Payments and the Cash Flow Waterfall 89\u003c\/p\u003e \u003cp\u003eThe Movement of Cash for an Individual Liability 90\u003c\/p\u003e \u003cp\u003eTypes of Liabilities 91\u003c\/p\u003e \u003cp\u003eFees 91\u003c\/p\u003e \u003cp\u003eModel Builder 6.1: Calculating Fees in the Waterfall 91\u003c\/p\u003e \u003cp\u003eInterest 94\u003c\/p\u003e \u003cp\u003eModel Builder 6.2: Calculating Interest in the Waterfall 95\u003c\/p\u003e \u003cp\u003ePrincipal 100\u003c\/p\u003e \u003cp\u003eModel Builder 6.3: Calculating Principal in the Waterfall 100\u003c\/p\u003e \u003cp\u003eUnderstanding Basic Asset and Liability Interactions 105\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 7 Advanced Liability Structures: Triggers, Interest Rate Swaps, and Reserve Accounts 107\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eTriggers and Their Affect on the Liability Structure 107\u003c\/p\u003e \u003cp\u003eModel Builder 7.1: Incorporating Triggers 108\u003c\/p\u003e \u003cp\u003eSwaps 113\u003c\/p\u003e \u003cp\u003eModel Builder 7.2: Incorporating a Basic Interest Rate Swap 114\u003c\/p\u003e \u003cp\u003eFinal Notes on Swaps 117\u003c\/p\u003e \u003cp\u003eReserve Accounts 117\u003c\/p\u003e \u003cp\u003eModel Builder 7.3: Incorporating a Cash-Funded Reserve Account 118\u003c\/p\u003e \u003cp\u003eConclusion of the Cash Flow Waterfall 122\u003c\/p\u003e \u003cp\u003eToolbox 123\u003c\/p\u003e \u003cp\u003eAND and OR 123\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 8 Analytics and Output Reporting 125\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eInternal Testing 125\u003c\/p\u003e \u003cp\u003eCash In versus Cash Out 125\u003c\/p\u003e \u003cp\u003eModel Builder 8.1: Cash In versus Cash Out Test 126\u003c\/p\u003e \u003cp\u003eBalances at Maturity 128\u003c\/p\u003e \u003cp\u003eModel Builder 8.2: Balances at Maturity Tests 128\u003c\/p\u003e \u003cp\u003eAsset Principal Check 129\u003c\/p\u003e \u003cp\u003eModel Builder 8.3: Asset Principal Check Test 129\u003c\/p\u003e \u003cp\u003ePerformance Analytics 130\u003c\/p\u003e \u003cp\u003eMonthly Yield 130\u003c\/p\u003e \u003cp\u003eModel Builder 8.4: Calculating Monthly Yield 130\u003c\/p\u003e \u003cp\u003eCalculating the Monthly Yield 132\u003c\/p\u003e \u003cp\u003eBond-Equivalent Yield 133\u003c\/p\u003e \u003cp\u003eModel Builder 8.5: Calculating Bond-Equivalent Yield 133\u003c\/p\u003e \u003cp\u003eModified Duration 133\u003c\/p\u003e \u003cp\u003eModel Builder 8.6: Calculating Modified Duration 134\u003c\/p\u003e \u003cp\u003eOutput Reporting 135\u003c\/p\u003e \u003cp\u003eModel Builder 8.7: Creating the Output Report 136\u003c\/p\u003e \u003cp\u003eThe Importance of Testing and Output 140\u003c\/p\u003e \u003cp\u003eToolbox 140\u003c\/p\u003e \u003cp\u003eConditional Formatting 140\u003c\/p\u003e \u003cp\u003eGoal Seek 141\u003c\/p\u003e \u003cp\u003eArray Formulas 142\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 9 Understanding the Model 145\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eThe Complete Model in Review 145\u003c\/p\u003e \u003cp\u003eUnderstanding the Effects of Increased Loss 147\u003c\/p\u003e \u003cp\u003eVarying Principal Allocation Methodologies 150\u003c\/p\u003e \u003cp\u003eVarying Prepayment Rates 151\u003c\/p\u003e \u003cp\u003eVarying Loss Timing 152\u003c\/p\u003e \u003cp\u003eVarying Recovery Rate and Lag 152\u003c\/p\u003e \u003cp\u003eThe Value of a Swap 153\u003c\/p\u003e \u003cp\u003eAdditional Testing 153\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 10 Automation Using Visual Basic Applications (VBA) 155\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eConventions of This Chapter 155\u003c\/p\u003e \u003cp\u003eThe Visual Basic Editor 156\u003c\/p\u003e \u003cp\u003eThe Menu Bar 156\u003c\/p\u003e \u003cp\u003eThe Project Explorer and the Properties Window 157\u003c\/p\u003e \u003cp\u003eVBA Code 157\u003c\/p\u003e \u003cp\u003eSimple Automation for Printing and Goal Seek 158\u003c\/p\u003e \u003cp\u003eModel Builder 10.1: Automating Print Procedures 158\u003c\/p\u003e \u003cp\u003eModel Builder 10.2: Automating Goal Seek to Optimize Advance Rates 161\u003c\/p\u003e \u003cp\u003eUnderstanding Looping to Automate the Analytics Sheet 164\u003c\/p\u003e \u003cp\u003eModel Builder 10.3: Automating Goal Seek to Perform Transaction Analytics 164\u003c\/p\u003e \u003cp\u003eAutomated Scenario Generation 167\u003c\/p\u003e \u003cp\u003eModel Builder 10.4: Creating a Transaction Scenario Generator 167\u003c\/p\u003e \u003cp\u003eWorking with Macros in Excel 173\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 11 Conclusion 175\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eThe Investment Banker’s Perspective 175\u003c\/p\u003e \u003cp\u003eThe Investor’s Perspective 176\u003c\/p\u003e \u003cp\u003eThe Issuer’s Perspective 176\u003c\/p\u003e \u003cp\u003eThe Financial Guarantor’s Perspective 177\u003c\/p\u003e \u003cp\u003eThe Big Picture Perspective 177\u003c\/p\u003e \u003cp\u003eAppendix: Using This Book with Excel 2007 179\u003c\/p\u003e \u003cp\u003eAbout the CD-ROM 189\u003c\/p\u003e \u003cp\u003eIndex 193\u003c\/p\u003e \u003cb\u003eKeith Allman\u003c\/b\u003e is currently the principal trainer and founder of Enstruct, a structured finance analytics training company. Previously he was a Vice President at Citigroup in their Global Fixed Income, Structured Finance Division. He has built and reviewed hundreds of models from many different sectors. Allman received his master's degree in international affairs from Columbia University. \u003cb\u003ePraise for \u003ci\u003eModeling Structured Finance Cash Flows with Microsoft Excel\u003c\/i\u003e\u003c\/b\u003e\u003cbr\u003e\u003cbr\u003e \u003cp\u003e\"You can roll up your sleeves, dive into this book, and shortly thereafter, you have a full-blown honest-to- goodness working model of a transaction. This sure beats the old method of slaving at the feet of gurus for years in the hopes that they'll throw you a scrap. It almost feels as though Allman has given away a shortcut through the 'hazing' period of learning, but then I am grateful I have a resource with which to train new analysts or remind myself how some things work.\"\u003cbr\u003e—\u003cb\u003eOmar Haneef\u003c\/b\u003e, Global Securitized Markets, Citigroup\u003c\/p\u003e \u003cp\u003e\"Allman has written a book that reveals to the reader\/user a robust framework for using Excel to model structured finance transactions. Both seasoned cash flow modelers and those new to, or curious about, how structured finance transactions are analyzed can benefit from this book.\"\u003cbr\u003e—\u003cb\u003eHenry Wilson\u003c\/b\u003e, Bond Insurance Company Analyst\u003c\/p\u003e \u003cp\u003e\"A powerful, concise, and tiered approach. It's a bedrock foundation to build on for both the art and science of structured finance modeling. An excellent platform from which to leverage your financial knowledge and personal capital.\"\u003cbr\u003e—\u003cb\u003eWilliam Preinitz\u003c\/b\u003e, Managing Director, Citigroup\u003c\/p\u003e \u003cp\u003e\"An essential tool for any credit officer wishing to gain a general or in-depth understanding of cash flow modeling for new or existing deals.\"\u003cbr\u003e—\u003cb\u003eJoseph B. Keener\u003c\/b\u003e, Structured Portfolio Management, Citigroup London\u003c\/p\u003e \u003cp\u003eWritten by industry expert Keith Allman, this well-rounded resource provides detailed guidance on constructing one of today's most adaptable models—the cash flow model. Filled with in-depth insight, expert advice, and illustrative examples, \u003ci\u003eModeling Structured Finance Cash Flows with Microsoft Excel\u003c\/i\u003e explains individual functions and formulas, while also examining the theory behind the spreadsheets. Each chapter begins with a discussion of theory, followed by a section called \"Model Builder,\" in which Allman translates the theory into functions and formulas. In addition, the companion website features all of the modeling exercises, as well as a final version of the model that is created in the text.\u003c\/p\u003e \u003cp\u003eBy combining specific application instruction with theory, \u003ci\u003eModeling Structured Finance Cash Flows with Microsoft Excel\u003c\/i\u003e will teach you skills that can be instantly applied to professional-level modeling.\u003c\/p\u003e","brand":"Wiley","offers":[{"title":"Default Title","offer_id":47989637906661,"sku":"NP9780470042908","price":94.0,"currency_code":"USD","in_stock":false}],"thumbnail_url":"\/\/cdn.shopify.com\/s\/files\/1\/1842\/7735\/files\/9780470042908.jpg?v=1761784909","url":"https:\/\/k12savings.com\/products\/modeling-structured-finance-cash-flows-with-microsoft-excel-isbn-9780470042908","provider":"K12savings","version":"1.0","type":"link"}