Home » Source Code » Wiley Credit Risk Modeling using Excel and VBA Jun 2007

Wiley Credit Risk Modeling using Excel and VBA Jun 2007

2013-11-26 10:16:24
The author
Download(s): 2
Point (s): 2 
Category Category:
FileFile Visual BasicVB


Preface xi
Some Hints for Troubleshooting xiii
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 13
Treating outliers in input variables 15
Choosing the functional relationship between the score and explanatory variables 19
Concluding remarks 23
Notes and literature 24
Appendix 24
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 34
Implementing the Merton model with a T-year horizon 39
Credit spreads 44
Notes and literature 44
3 Transition Matrices 45
Cohort approach 46
Multi-period transitions 51
Hazard rate approach 53
Obtaining a generator matrix from a given transition matrix 58
Confidence intervals with the Binomial distribution 59
Bootstrapped confidence intervals for the hazard approach 63
Notes and literature 67
Appendix 67viii Contents
4 Prediction of Default and Transition Rates 73
Candidate variables for prediction 73
Predicting investment-grade default rates with linear regression 75
Predicting investment-grade default rates with Poisson regression 78
Backtesting the prediction models 83
Predicting transition matrices 87
Adjusting transition matrices 88
Representing transition matrices with a single parameter 89
Shifting the transition matrix 91
Backtesting the transition forecasts 96
Scope of application 98
Notes and literature 98
Appendix 99
5 Modeling and Estimating Default Correlations with the Asset Value
Approach 103
Default correlation, joint default probabilities and the asset value approach 103
Calibrating the asset value approach to default experience: the method of
moments 105
Estimating asset correlation with maximum likelihood 108
Exploring the reliability of estimators with a Monte Carlo study 114
Concluding remarks 117
Notes and literature 117
6 Measuring Credit Portfolio Risk with the Asset Value Approach 119
A default mode model implemented in the spreadsheet 119
VBA implementation of a default-mode model 122
Importance sampling 126
Quasi Monte Carlo 130
Assessing simulation error 132
Exploiting portfolio structure in the VBA program 135
Extensions 137
First extension: Multi-factor model 137
Second extension: t-distributed asset values 138
Third extension: Random LGDs 139
Fourth extension: Other risk measures 143
Fifth extension: Multi-state modeling 144
Notes and literature 146
7 Validation of Rating Systems 147
Cumulative accuracy profile and accuracy ratios 148
Receiver operating characteristic (ROC) 151
Bootstrapping confidence intervals for the accuracy ratio 153
Interpreting CAPs and ROCs 155
Brier Score 156
Testing the calibration of rating-specific default probabilities 157Validation strategies 161
Notes and literature 162
8 Validation of Credit Portfolio Models 163
Testing distributions with the Berkowitz test 163
Example implementation of the Berkowitz test 166
Representing the loss distribution 167
Simulating the critical chi-squared value 169
Testing modeling details: Berkowitz on subportfolios 171
Assessing power 175
Scope and limits of the test 176
Notes and literature 177
9 Risk-Neutral Default Probabilities and Credit Default Swaps 179
Describing the term structure of default: PDs cumulative, marginal, and seen
from today 180
From bond prices to risk-neutral default probabilities 181
Concepts and formulae 181
Implementation 184
Pricing a CDS 191
Refining the PD estimation 193
Notes and literature 196
10 Risk Analysis of Structured Credit: CDOs and First-to-Default Swaps 197
Estimating CDO risk with Monte Carlo simulation 197
The large homogeneous portfolio (LHP) approximation 201
Systematic risk of CDO tranches 203
Default times for first-to-default swaps 205
Notes and literature 209
Appendix 209
11 Basel II and Internal Ratings 211
Calculating capital requirements in the Internal Ratings-Based (IRB) approach 211
Assessing a given grading structure 214
Towards an optimal grading structure 220
Notes and literature 223
Appendix A1 Visual Basics for Applications (VBA) 225
Appendix A2 Solver 233
Appendix A3 Maximum Likelihood Estimation and Newton’s Method 239
Appendix A4 Testing and Goodness of Fit 245
Appendix A5 User-Defined Functions 251
Sponsored links

File list

Tips: You can preview the content of files by clicking file names^_^
Name Size Date
01logit.xls2.06 MB14-12-06 01:52
02merton.xls213.50 kB13-12-06 21:48
03transition.xls1.74 MB07-03-07 04:25
04prediction.xls457.00 kB10-12-06 22:28
05correlation.xls830.50 kB13-12-06 00:52
06creditportfolio.xls2.02 MB10-12-06 22:34
07rating_Validation.xls328.50 kB13-12-06 00:53
08validation_portfolio.xls3.48 MB10-12-06 22:38
09cds.xls7.70 MB10-12-06 22:41
10cdos.xls1,009.50 kB10-12-06 22:42
11Basel2.xls1.00 MB10-12-06 22:43
functionlist.pdf59.14 kB11-12-06 23:47
<Excel>0.00 B07-03-07 16:46
Sponsored links


(Add your comment, get 0.1 Point)
Minimum:15 words, Maximum:160 words
  • 1
  • Page 1
  • Total 1

Wiley Credit Risk Modeling using Excel and VBA Jun 2007 (2.94 MB)

Need 2 Point(s)
Your Point (s)

Your Point isn't enough.

Get 22 Point immediately by PayPal

Point will be added to your account automatically after the transaction.

More(Debit card / Credit card / PayPal Credit / Online Banking)

Submit your source codes. Get more Points


Don't have an account? Register now
Need any help?
Mail to: support@codeforge.com


CodeForge Chinese Version
CodeForge English Version

Where are you going?

^_^"Oops ...

Sorry!This guy is mysterious, its blog hasn't been opened, try another, please!

Warm tip!

CodeForge to FavoriteFavorite by Ctrl+D