Atnaujinkite slapukų nuostatas

Modeling Structured Finance Cash Flows with Microsoft Excel: A Step-by-Step Guide [Minkštas viršelis]

3.83/5 (78 ratings by Goodreads)
  • Formatas: Paperback / softback, 224 pages, aukštis x plotis x storis: 234x190x12 mm, weight: 416 g, Screen captures: 86 B&W, 0 Color
  • Serija: Wiley Finance
  • Išleidimo metai: 05-Apr-2007
  • Leidėjas: John Wiley & Sons Inc
  • ISBN-10: 0470042907
  • ISBN-13: 9780470042908
Kitos knygos pagal šią temą:
Modeling Structured Finance Cash Flows with Microsoft Excel: A Step-by-Step GuideDidesnis paveikslėlis
  • Formatas: Paperback / softback, 224 pages, aukštis x plotis x storis: 234x190x12 mm, weight: 416 g, Screen captures: 86 B&W, 0 Color
  • Serija: Wiley Finance
  • Išleidimo metai: 05-Apr-2007
  • Leidėjas: John Wiley & Sons Inc
  • ISBN-10: 0470042907
  • ISBN-13: 9780470042908
Kitos knygos pagal šią temą:
Pastovi nuoroda: https://www.kriso.lt/db/9780470042908.html
Raktažodžiai:
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.

Preface xi
Acknowledgments xiii
About the Author xv
Introduction 1(2)
The Three Basic Elements of a Cash Flow Model
3(2)
Inputs
3(1)
Cash Flow Structure
4(1)
Outputs
5(1)
The Process of Building a Cash Flow Model
5(2)
Plan and Design
5(1)
Obtain All Necessary Information
6(1)
Construct Basic Framework
6(1)
Develop Advanced Structure
6(1)
Validate Assumptions
6(1)
Test Model
7(1)
How This Book Is Designed
7(2)
Dates and Timing
9(14)
Time Progression
9(1)
Dates and Timing on the Inputs Sheet
10(1)
Day-Count Systems: 30/360 versus Actual/360 versus Actual/365
11(1)
Model Builder 1.1: Inputs Sheet---Dates and Timing
12(2)
Dates and Timing on the Cash Flow Sheet
14(1)
Model Builder 1.2: Cash Flow Sheet---Dates and Timing
15(3)
Toolbox
18(5)
Naming Cells and Ranges
18(1)
Data Validation Lists
19(2)
EDATE
21(2)
Asset Cash Flow Generation
23(20)
Loan Level versus Representative Line Amortization
23(4)
How Asset Generation Is Demonstrated in Model Builder
27(1)
Asset Generation on the Inputs Sheet
27(2)
Fixed Rate Amortization Inputs
28(1)
Floating Rate Amortization Inputs
28(1)
Model Builder 2.1: Inputs Sheet Asset Assumptions and the Vectors Sheet
29(4)
Asset Generation on the Cash Flow Sheet
33(1)
Model Builder 2.2: Notional Asset Amortization on the Cash Flow Sheet
33(6)
Toolbox
39(4)
Offset
39(1)
Match
40(1)
MOD
41(1)
PMT
41(2)
Prepayments
43(16)
How Prepayments Are Tracked
43(3)
SMM: Single Monthly Mortality
44(1)
CPR: Conditional Prepayment Rate
44(1)
PSA: Public Securities Association
44(1)
ABS: Absolute Prepayment Speed
45(1)
Historical Prepayment Data Formats
46(1)
Building Prepayment Curves
46(1)
Prepayment Curves in Project Model Builder
47(1)
The Effect of Prepayments on Structured Transactions
48(1)
Model Builder 3.1: Historical Prepayment Analysis and Creating a Projected Prepayment Curve
48(5)
Model Builder 3.2: Integrating Projected Prepayments in Asset Amortization
53(3)
Toolbox
56(3)
Weighted Averages Using SUMPRODUCT and SUM
56(3)
Delinquency, Default, and Loss Analysis
59(24)
Delinquencies versus Defaults versus Loss
59(1)
The Importance of Analyzing Delinquency
60(2)
Model Builder 4.1: Building Historical Delinquency Curves
62(2)
Deriving Historical Loss Curves
64(3)
Model Builder 4.2: Building Historical and Projected Loss Curves
67(2)
Analyzing Historical Loss Curves
69(1)
Model Builder 4.2 Continued
69(1)
Projecting Loss Curves
70(1)
Model Builder 4.2 Continued
71(2)
Integrating Loss Projections
73(3)
The Effects of Seasoning and Default Timing
75(1)
Model Builder 4.3: Integrating Defaults in Asset Amortization
76(7)
Recoveries
83(6)
Model Builder 5.1: Historical Recovery Analysis
85(1)
Projecting Recoveries in a Cash Flow Model
86(1)
Model Builder 5.2: Integrating Recoveries into Project Model Builder
87(1)
Final Points Regarding Recoveries
88(1)
Liabilities and the Cash Flow Waterfall
89(18)
Priority of Payments and the Cash Flow Waterfall
89(2)
The Movement of Cash for an Individual Liability
90(1)
Types of Liabilities
91(1)
Fees
91(1)
Model Builder 6.1: Calculating Fees in the Waterfall
91(4)
Interest
94(1)
Model Builder 6.2: Calculating Interest in the Waterfall
95(5)
Principal
100(1)
Model Builder 6.3: Calculating Principal in the Waterfall
100(5)
Understanding Basic Asset and Liability Interactions
105(2)
Advanced Liability Structures: Triggers, Interest Rate Swaps, and Reserve Accounts
107(18)
Triggers and Their Affect on the Liability Structure
107(1)
Model Builder 7.1: Incorporating Triggers
108(5)
Swaps
113(1)
Model Builder 7.2: Incorporating a Basic Interest Rate Swap
114(3)
Final Notes on Swaps
117(1)
Reserve Accounts
117(1)
Model Builder 7.3: Incorporating a Cash-Funded Reserve Account
118(4)
Conclusion of the Cash Flow Waterfall
122(1)
Toolbox
123(2)
AND and OR
123(2)
Analytics and Output Reporting
125(20)
Internal Testing
125(1)
Cash In versus Cash Out
125(1)
Model Builder 8.1: Cash In versus Cash Out Test
126(2)
Balances at Maturity
128(1)
Model Builder 8.2: Balances at Maturity Tests
128(1)
Asset Principal Check
129(1)
Model Builder 8.3: Asset Principal Check Test
129(1)
Performance Analytics
130(1)
Monthly Yield
130(1)
Model Builder 8.4: Calculating Monthly Yield
130(3)
Calculating the Monthly Yield
132(1)
Bond-Equivalent Yield
133(1)
Model Builder 8.5: Calculating Bond-Equivalent Yield
133(1)
Modified Duration
133(1)
Model Builder 8.6: Calculating Modified Duration
134(1)
Output Reporting
135(1)
Model Builder 8.7: Creating the Output Report
136(4)
The Importance of Testing and Output
140(1)
Toolbox
140(5)
Conditional Formatting
140(1)
Goal Seek
141(1)
Array Formulas
142(3)
Understanding the Model
145(10)
The Complete Model in Review
145(2)
Understanding the Effects of Increased Loss
147(3)
Varying Principal Allocation Methodologies
150(2)
Varying Prepayment Rates
151(1)
Varying Loss Timing
152(1)
Varying Recovery Rate and Lag
152(1)
The Value of a Swap
153(1)
Additional Testing
153(2)
Automation Using Visual Basic Applications (VBA)
155(20)
Conventions of This
Chapter
155(1)
The Visual Basic Editor
156(1)
The Menu Bar
156(1)
The Project Explorer and the Properties Window
157(1)
VBA Code
157(1)
Simple Automation for Printing and Goal Seek
158(1)
Model Builder 10.1: Automating Print Procedures
158(3)
Model Builder 10.2: Automating Goal Seek to Optimize Advance Rates
161(3)
Understanding Looping to Automate the Analytics Sheet
164(1)
Model Builder 10.3: Automating Goal Seek to Perform Transaction Analytics
164(3)
Automated Scenario Generation
167(1)
Model Builder 10.4: Creating a Transaction Scenario Generator
167(6)
Working with Macros in Excel
173(2)
Conclusion
175(4)
The Investment Banker's Perspective
175(1)
The Investor's Perspective
176(1)
The Issuer's Perspective
176(1)
The Financial Guarantor's Perspective
177(1)
The Big Picture Perspective
177(2)
Appendix: Using This Book with Excel 2007 179(10)
About the CD-ROM 189(4)
Index 193


Keith Allman 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.