相關主題
商品描述
Description
A practical guide to building fully operational financial cash flow models for structured finance transactions
Structured 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. Modeling Structured Finance Cash Flows with Microsoft Excel 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 CD-ROM features all of the modeling exercises, as well as a final version of the model that is created in the text.
Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.
Table of Contents
Preface.Acknowledgments.
About the Author.
Introduction.
The Three Basic Elements of a Cash Flow Model.
Inputs.
Cash Flow Structure.
Outputs.
The Process of Building a Cash Flow Model.
Plan and Design.
Obtain All Necessary Information.
Construct Basic Framework.
Develop Advanced Structure.
Validate Assumptions.
Test Model.
How This Book Is Designed.
CHAPTER 1. Dates and Timing.
Time Progression.
Dates and Timing on the Inputs Sheet.
Day-Count Systems: 30/360 versus Actual/360 versus Actual/365.
Model Builder 1.1: Inputs Sheet—Dates and Timing.
Dates and Timing on the Cash Flow Sheet.
Model Builder 1.2: Cash Flow Sheet—Dates and Timing.
Toolbox.
Naming Cells and Ranges.
Data Validation Lists.
EDATE.
CHAPTER 2. Asset Cash Flow Generation.
Loan Level versus Representative Line Amortization.
How Asset Generation Is Demonstrated in Model Builder.
Asset Generation on the Inputs Sheet.
Fixed Rate Amortization Inputs.
Floating Rate Amortization Inputs.
Model Builder 2.1: Inputs Sheet Asset Assumptions and the Vectors Sheet.
Asset Generation on the Cash Flow Sheet.
Model Builder 2.2: Notional Asset Amortization on the Cash Flow Sheet.
Toolbox.
OFFSET.
MATCH.
MOD.
PMT.
CHAPTER 3. Prepayments.
How Prepayments Are Tracked.
SMM: Single Monthly Mortality.
CPR: Conditional Prepayment Rate.
PSA: Public Securities Association.
ABS: Absolute Prepayment Speed.
Historical Prepayment Data Formats.
Building Prepayment Curves.
Prepayment Curves in Project Model Builder.
The Effect of Prepayments on Structured Transactions.
Model Builder 3.1: Historical Prepayment Analysis and Creating a Projected Prepayment Curve.
Model Builder 3.2: Integrating Projected Prepayments in Asset Amortization.
Toolbox.
Weighted Averages Using SUMPRODUCT and SUM.
CHAPTER 4. Delinquency, Default, and Loss Analysis.
Delinquencies versus Defaults versus Loss.
The Importance of Analyzing Delinquency.
Model Builder 4.1: Building Historical Delinquency Curves.
Deriving Historical Loss Curves.
Model Builder 4.2: Building Historical and Projected Loss Curves.
Analyzing Historical Loss Curves.
Model Builder 4.2 Continued.
Projecting Loss Curves.
Model Builder 4.2 Continued.
Integrating Loss Projections.
The Effects of Seasoning and Default Timing.
Model Builder 4.3: Integrating Defaults in Asset Amortization.
CHAPTER 5. Recoveries.
Model Builder 5.1: Historical Recovery Analysis.
Projecting Recoveries in a Cash Flow Model.
Model Builder 5.2: Integrating Recoveries into Project Model Builder.
Final Points Regarding Recoveries.
CHAPTER 6. Liabilities and the Cash Flow Waterfall.
Priority of Payments and the Cash Flow Waterfall.
The Movement of Cash for an Individual Liability.
Types of Liabilities.
Fees.
Model Builder 6.1: Calculating Fees in the Waterfall.
Interest.
Model Builder 6.2: Calculating Interest in the Waterfall.
Principal.
Model Builder 6.3: Calculating Principal in the Waterfall.
Understanding Basic Asset and Liability Interactions.
CHAPTER 7. Advanced Liability Structures: Triggers, Interest Rate Swaps, and Reserve Accounts.
Triggers and Their Affect on the Liability Structure.
Model Builder 7.1: Incorporating Triggers.
Swaps.
Model Builder 7.2: Incorporating a Basic Interest Rate Swap.
Final Notes on Swaps.
Reserve Accounts.
Model Builder 7.3: Incorporating a Cash-Funded Reserve Account.
Conclusion of the Cash Flow Waterfall.
Toolbox.
AND and OR.
CHAPTER 8. Analytics and Output Reporting.
Internal Testing.
Cash In versus Cash Out.
Model Builder 8.1: Cash In versus Cash Out Test.
Balances at Maturity.
Model Builder 8.2: Balances at Maturity Tests.
Asset Principal Check.
Model Builder 8.3: Asset Principal Check Test.
Performance Analytics.
Monthly Yield.
Model Builder 8.4: Calculating Monthly Yield.
Calculating the Monthly Yield.
Bond-Equivalent Yield.
Model Builder 8.5: Calculating Bond-Equivalent Yield.
Modified Duration.
Model Builder 8.6: Calculating Modified Duration.
Output Reporting.
Model Builder 8.7: Creating the Output Report.
The Importance of Testing and Output.
Toolbox.
Conditional Formatting.
Goal Seek.
Array Formulas.
CHAPTER 9. Understanding the Model.
The Complete Model in Review.
Understanding the Effects of Increased Loss.
Varying Principal Allocation Methodologies.
Varying Prepayment Rates.
Varying Loss Timing.
Varying Recovery Rate and Lag.
The Value of a Swap.
Additional Testing.
CHAPTER 10. Automation Using Visual Basic Applications (VBA).
Conventions of This Chapter.
The Visual Basic Editor.
The Menu Bar.
The Project Explorer and the Properties Window.
VBA Code.
Simple Automation for Printing and Goal Seek.
Model Builder 10.1: Automating Print Procedures.
Model Builder 10.2: Automating Goal Seek to Optimize Advance Rates.
Understanding Looping to Automate the Analytics Sheet.
Model Builder 10.3: Automating Goal Seek to Perform Transaction Analytics.
Automated Scenario Generation.
Model Builder 10.4: Creating a Transaction Scenario Generator.
Working with Macros in Excel.
CHAPTER 11. Conclusion.
The Investment Banker’s Perspective.
The Investor’s Perspective.
The Issuer’s Perspective.
The Financial Guarantor’s Perspective.
The Big Picture Perspective.
Appendix: Using This Book with Excel 2007.
About the CD-ROM.
Index.
商品描述(中文翻譯)
描述
結構化金融和證券化交易在華爾街變得越來越普遍。然而，到目前為止，市場參與者必須自己創建模型來分析這些交易，而新進入者必須一邊學習一邊前進。《使用Microsoft Excel建立結構化金融現金流模型》為讀者提供了建立結構化金融和證券化交易現金流模型所需的信息。金融專業人士Keith Allman解釋了各個函數和公式，同時解釋了試算表背後的理論。每章開始時都會討論理論，然後是一個名為“模型構建者”的部分，在這部分中，Allman將理論轉化為函數和公式。此外，附帶的CD-ROM包含了所有的建模練習，以及在文本中創建的模型的最終版本。
注意：CD-ROM / DVD和其他附加材料不包含在電子書文件中。
目錄
前言
致謝
關於作者
引言
現金流模型的三個基本要素
輸入
現金流結構
輸出
