Rights Contact Login For More Details
- Wiley
More About This Title Credit Risk Modeling using Excel and VBA 2e
English
This book provides practitioners and students with an intuitive, hands-on introduction to modern credit risk modelling. Every chapter starts with an explanation of the methodology and then the authors take the reader step by step through the implementation of the methods in Excel and VBA. They focus specifically on risk management issues and cover default probability estimation (scoring, structural models, and transition matrices), correlation and portfolio analysis, validation, as well as credit default swaps and structured finance.
The book has an accompanying website, http://loeffler-posch.com/, which has been specially updated for this Second Edition and contains slides and exercises for lecturers.
English
About the authors
GUNTER LÖFFLER is Professor of Finance at the University of Ulm in Germany. His current research interests are on credit risk and empirical finance. Previously, Gunter was Assistant Professor at Goethe University Frankfurt, and served as an internal consultant in the asset management division of Commerzbank. His Ph.D. in finance is from the University of Mannheim. Gunter has studied at Heidelberg and Cambridge Universities.
PETER N. POSCH is Assistant Professor of Finance at the University of Ulm in Germany. Previously, Peter was co-head of credit treasury at a large bank, where he also traded credit derivatives and other fixed income products for the bank's proprietary books. His Ph.D. in finance on the dynamics of credit risk is from the University of Ulm. Peter has studied economics, philosophy and law at the University of Bonn.
English
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
