Credit Risk Modeling using Excel and VBA

Credit risk modeling using Excel and VBA Second Edition Gunter Lö ffler and Peter N. Posch This book provides practitioners and students with a hands-on introduction to modern credit risk modeling. The authors begin each chapter with an accessible presentation of a given methodology, before providing a step-by-step guide to implementation methods in Excel and Visual Basic for Applications (VBA). The book covers default probability estimation (scoring, structural models, and transition matrices), correlation and portfolio analysis, validation, as well as credit default swaps and structured finance. Several appendices and videos increase ease of access. The second edition includes new coverage of the important issue of how parameter uncertainty can be dealt with in the estimation of portfolio risk, as well as comprehensive new sections on the pricing of CDSs and CDOs, and a chapter on predicting borrower-specific loss given default with regression models. In all, the authors present a host of applications ? many of which go beyond standard Excel or VBA usages, for example, how to estimate logit models with maximum likelihood, or how to quickly conduct large-scale Monte Carlo simulations. Clearly written with a multitude of practical examples, the new edition of Credit Risk Modeling using Excel and VBA will prove an indispensable resource for anyone working in, studying or researching this important field. Praise for the first edition "In one place, Lö ffler and Posch provide all that is needed to install a state-of-the-art risk management system, including a broad understanding of different risk management frameworks, detailed estimation techniques for deriving PD, LGD, and correlation parameters, and programming tools for putting these methods into practice." Richard Cantor, Chief Credit Officer, Moody's Investors Service

Preface to the 2nd edition xi
Preface to the 1st edition xiii
Some Hints for Troubleshooting xv
1 Estimating Credit Scores with Logit 1
Linking scores, default probabilities and observed default behavior 1
Estimating logit coefficients in Excel 4
Computing statistics after model estimation 8
Interpreting regression statistics 10
Prediction and scenario analysis 12
Treating outliers in input variables 16
Choosing the functional relationship between the score and explanatory variables 20
Concluding remarks 25
Appendix 25
Logit and probit 25
Marginal effects 25
Notes and literature 26
2 The Structural Approach to Default Prediction and Valuation 27
Default and valuation in a structural model 27
Implementing the Merton model with a one-year horizon 30
The iterative approach 30
A solution using equity values and equity volatilities 35
Implementing the Merton model with a T -year horizon 39
Credit spreads 43
CreditGrades 44
Appendix 50
Notes and literature 52
Assumptions 52
Literature 53
3 Transition Matrices 55
Cohort approach 56
Multi-period transitions 61
Hazard rate approach 63
Obtaining a generator matrix from a given transition matrix 69
Confidence intervals with the binomial distribution 71
Bootstrapped confidence intervals for the hazard approach 74
Notes and literature 78
Appendix 78
Matrix functions 78
4 Prediction of Default and Transition Rates 83
Candidate variables for prediction 83
Predicting investment-grade default rates with linear regression 85
Predicting investment-grade default rates with Poisson regression 88
Backtesting the prediction models 94
Predicting transition matrices 99
Adjusting transition matrices 100
Representing transition matrices with a single parameter 101
Shifting the transition matrix 103
Backtesting the transition forecasts 108
Scope of application 108
Notes and literature 110
Appendix 110
5 Prediction of Loss Given Default 115
Candidate variables for prediction 115
Instrument-related variables 116
Firm-specific variables 117
Macroeconomic variables 118
Industry variables 118
Creating a data set 119
Regression analysis of LGD 120
Backtesting predictions 123
Notes and literature 126
Appendix 126
6 Modeling and Estimating Default Correlations with the Asset Value Approach 131
Default correlation, joint default probabilities and the asset value approach 131
Calibrating the asset value approach to default experience: the method of moments 133
Estimating asset correlation with maximum likelihood 136
Exploring the reliability of estimators with a Monte Carlo study 144
Concluding remarks 147
Notes and literature 147
7 Measuring Credit Portfolio Risk with the Asset Value Approach 149
A default-mode model implemented in the spreadsheet 149
VBA implementation of a default-mode model 152
Importance sampling 156
Quasi Monte Carlo 160
Assessing Simulation Error 162
Exploiting portfolio structure in the VBA program 165
Dealing with parameter uncertainty 168
Extensions 170
First extension: Multi-factor model 170
Second extension: t-distributed asset values 171
Third extension: Random LGDs 173
Fourth extension: Other risk measures 175
Fifth extension: Multi-state modeling 177
Notes and literature 179
8 Validation of Rating Systems 181
Cumulative accuracy profile and accuracy ratios 182
Receiver operating characteristic (ROC) 185
Bootstrapping confidence intervals for the accuracy ratio 187
Interpreting caps and ROCs 190
Brier score 191
Testing the calibration of rating-specific default probabilities 192
Validation strategies 195
Testing for missing information 198
Notes and literature 201
9 Validation of Credit Portfolio Models 203
Testing distributions with the Berkowitz test 203
Example implementation of the Berkowitz test 206
Representing the loss distribution 207
Simulating the critical chi-square value 209
Testing modeling details: Berkowitz on subportfolios 211
Assessing power 214
Scope and limits of the test 216
Notes and literature 217
10 Credit Default Swaps and Risk-Neutral Default Probabilities 219
Describing the term structure of default: PDs cumulative, marginal and seen from today 220
From bond prices to risk-neutral default probabilities 221
Concepts and formulae 221
Implementation 225
Pricing a CDS 232
Refining the PD estimation 234
Market values for a CDS 237
Example 239
Estimating upfront CDS and the 'Big Bang' protocol 240
Pricing of a pro-rata basket 241
Forward CDS spreads 242
Example 243
Pricing of swaptions 243
Notes and literature 247
Appendix 247
Deriving the hazard rate for a CDS 247
11 Risk Analysis and Pricing of Structured Credit: CDOs and First-to-Default
Swaps 249
Estimating CDO risk with Monte Carlo simulation 249
The large homogeneous portfolio (LHP) approximation 253
Systemic risk of CDO tranches 256
Default times for first-to-default swaps 259
CDO pricing in the LHP framework 263
Simulation-based CDO pricing 272
Notes and literature 281
Appendix 282
Closed-form solution for the LHP model 282
Cholesky decomposition 283
Estimating PD structure from a CDS 284
12 Basel II and Internal Ratings 285
Calculating capital requirements in the Internal Ratings-Based (IRB) approach 285
Assessing a given grading structure 288
Towards an optimal grading structure 294
Notes and literature 297
Appendix A1 Visual Basics for Applications (VBA) 299
Appendix A2 Solver 307
Appendix A3 Maximum Likelihood Estimation and Newton's Method 313
Appendix A4 Testing and Goodness of Fit 319
Appendix A5 User-defined Functions 325
Index 333