Credit Risk Modeling using Excel and VBA PDF
Preview Credit Risk Modeling using Excel and VBA
P1:TIX fm JWBK493-Lo¨ffler November15,2010 17:8 Printer:Yettocome Credit Risk Modeling Using Excel and VBA with DVD i P1:TIX fm JWBK493-Lo¨ffler November15,2010 17:8 Printer:Yettocome ForothertitlesintheWileyFinanceseries pleaseseewww.wiley.com/finance ii P1:TIX fm JWBK493-Lo¨ffler November15,2010 17:8 Printer:Yettocome Credit Risk Modeling Using Excel and VBA with DVD Gunter Lo¨ffler Peter N. Posch A John Wiley and Sons, Ltd., Publication iii P1:TIX fm JWBK493-Lo¨ffler November15,2010 17:8 Printer:Yettocome Thiseditionfirstpublished2011 C 2011JohnWiley&Sons,Ltd Registeredoffice JohnWiley&SonsLtd,TheAtrium,SouthernGate,Chichester,WestSussex,PO198SQ,UnitedKingdom Fordetailsofourglobaleditorialoffices,forcustomerservicesandforinformationabouthowtoapplyfor permissiontoreusethecopyrightmaterialinthisbookpleaseseeourwebsiteatwww.wiley.com. Therightoftheauthortobeidentifiedastheauthorofthisworkhasbeenassertedinaccordancewiththe Copyright,DesignsandPatentsAct1988. Allrightsreserved.Nopartofthispublicationmaybereproduced,storedinaretrievalsystem,ortransmitted,in anyformorbyanymeans,electronic,mechanical,photocopying,recordingorotherwise,exceptaspermittedbythe UKCopyright,DesignsandPatentsAct1988,withoutthepriorpermissionofthepublisher. Wileyalsopublishesitsbooksinavarietyofelectronicformats.Somecontentthatappearsinprintmaynotbe availableinelectronicbooks. Designationsusedbycompaniestodistinguishtheirproductsareoftenclaimedastrademarks.Allbrandnamesand productnamesusedinthisbookaretradenames,servicemarks,trademarksorregisteredtrademarksoftheir respectiveowners.Thepublisherisnotassociatedwithanyproductorvendormentionedinthisbook.This publicationisdesignedtoprovideaccurateandauthoritativeinformationinregardtothesubjectmattercovered.It issoldontheunderstandingthatthepublisherisnotengagedinrenderingprofessionalservices.Ifprofessional adviceorotherexpertassistanceisrequired,theservicesofacompetentprofessionalshouldbesought. ISBN 978-0-470-66092-8 AcataloguerecordforthisbookisavailablefromtheBritishLibrary. Typesetin10/12ptTimesbyAptaraInc.,NewDelhi,India PrintedinGreatBritainbyCPIAntonyRowe,Chippenham,Wiltshire iv P1:TIX fm JWBK493-Lo¨ffler November15,2010 17:8 Printer:Yettocome Mundusestisquiconstatexcaelo,etterra etmare cunctisquesideribus. IsidorodeSevilla v P1:TIX fm JWBK493-Lo¨ffler November15,2010 17:8 Printer:Yettocome Contents Prefacetothe2ndedition xi Prefacetothe1stedition xiii SomeHintsforTroubleshooting xv 1 EstimatingCreditScoreswithLogit 1 Linkingscores,defaultprobabilitiesandobserveddefaultbehavior 1 EstimatinglogitcoefficientsinExcel 4 Computingstatisticsaftermodelestimation 8 Interpretingregressionstatistics 10 Predictionandscenarioanalysis 12 Treatingoutliersininputvariables 16 Choosingthefunctionalrelationshipbetweenthescoreandexplanatoryvariables 20 Concludingremarks 25 Appendix 25 Logitandprobit 25 Marginaleffects 25 Notesandliterature 26 2 TheStructuralApproachtoDefaultPredictionandValuation 27 Defaultandvaluationinastructuralmodel 27 ImplementingtheMertonmodelwithaone-yearhorizon 30 Theiterativeapproach 30 Asolutionusingequityvaluesandequityvolatilities 35 ImplementingtheMertonmodelwithaT-yearhorizon 39 Creditspreads 43 CreditGrades 44 Appendix 50 Notesandliterature 52 Assumptions 52 Literature 53 vii P1:TIX fm JWBK493-Lo¨ffler November15,2010 17:8 Printer:Yettocome viii Contents 3 TransitionMatrices 55 Cohortapproach 56 Multi-periodtransitions 61 Hazardrateapproach 63 Obtainingageneratormatrixfromagiventransitionmatrix 69 Confidenceintervalswiththebinomialdistribution 71 Bootstrappedconfidenceintervalsforthehazardapproach 74 Notesandliterature 78 Appendix 78 Matrixfunctions 78 4 PredictionofDefaultandTransitionRates 83 Candidatevariablesforprediction 83 Predictinginvestment-gradedefaultrateswithlinearregression 85 Predictinginvestment-gradedefaultrateswithPoissonregression 88 Backtestingthepredictionmodels 94 Predictingtransitionmatrices 99 Adjustingtransitionmatrices 100 Representingtransitionmatriceswithasingleparameter 101 Shiftingthetransitionmatrix 103 Backtestingthetransitionforecasts 108 Scopeofapplication 108 Notesandliterature 110 Appendix 110 5 PredictionofLossGivenDefault 115 Candidatevariablesforprediction 115 Instrument-relatedvariables 116 Firm-specificvariables 117 Macroeconomicvariables 118 Industryvariables 118 Creatingadataset 119 RegressionanalysisofLGD 120 Backtestingpredictions 123 Notesandliterature 126 Appendix 126 6 ModelingandEstimatingDefaultCorrelationswiththeAsset ValueApproach 131 Defaultcorrelation,jointdefaultprobabilitiesandtheassetvalueapproach 131 Calibratingtheassetvalueapproachtodefaultexperience:themethodof moments 133 Estimatingassetcorrelationwithmaximumlikelihood 136 ExploringthereliabilityofestimatorswithaMonteCarlostudy 144 Concludingremarks 147 Notesandliterature 147 P1:TIX fm JWBK493-Lo¨ffler November15,2010 17:8 Printer:Yettocome Contents ix 7 MeasuringCreditPortfolioRiskwiththeAssetValueApproach 149 Adefault-modemodelimplementedinthespreadsheet 149 VBAimplementationofadefault-modemodel 152 Importancesampling 156 QuasiMonteCarlo 160 AssessingSimulationError 162 ExploitingportfoliostructureintheVBAprogram 165 Dealingwithparameteruncertainty 168 Extensions 170 Firstextension:Multi-factormodel 170 Secondextension:t-distributedassetvalues 171 Thirdextension:RandomLGDs 173 Fourthextension:Otherriskmeasures 175 Fifthextension:Multi-statemodeling 177 Notesandliterature 179 8 ValidationofRatingSystems 181 Cumulativeaccuracyprofileandaccuracyratios 182 Receiveroperatingcharacteristic(ROC) 185 Bootstrappingconfidenceintervalsfortheaccuracyratio 187 InterpretingcapsandROCs 190 Brierscore 191 Testingthecalibrationofrating-specificdefaultprobabilities 192 Validationstrategies 195 Testingformissinginformation 198 Notesandliterature 201 9 ValidationofCreditPortfolioModels 203 TestingdistributionswiththeBerkowitztest 203 ExampleimplementationoftheBerkowitztest 206 Representingthelossdistribution 207 Simulatingthecriticalchi-squarevalue 209 Testingmodelingdetails:Berkowitzonsubportfolios 211 Assessingpower 214 Scopeandlimitsofthetest 216 Notesandliterature 217 10 CreditDefaultSwapsandRisk-NeutralDefaultProbabilities 219 Describing the term structure of default: PDs cumulative, marginal and seen fromtoday 220 Frombondpricestorisk-neutraldefaultprobabilities 221 Conceptsandformulae 221 Implementation 225 PricingaCDS 232 RefiningthePDestimation 234 P1:TIX fm JWBK493-Lo¨ffler November15,2010 17:8 Printer:Yettocome x Contents MarketvaluesforaCDS 237 Example 239 EstimatingupfrontCDSandthe‘BigBang’protocol 240 Pricingofapro-ratabasket 241 ForwardCDSspreads 242 Example 243 Pricingofswaptions 243 Notesandliterature 247 Appendix 247 DerivingthehazardrateforaCDS 247 11 RiskAnalysisandPricingofStructuredCredit:CDOsandFirst-to-Default Swaps 249 EstimatingCDOriskwithMonteCarlosimulation 249 Thelargehomogeneousportfolio(LHP)approximation 253 SystemicriskofCDOtranches 256 Defaulttimesforfirst-to-defaultswaps 259 CDOpricingintheLHPframework 263 Simulation-basedCDOpricing 272 Notesandliterature 281 Appendix 282 Closed-formsolutionfortheLHPmodel 282 Choleskydecomposition 283 EstimatingPDstructurefromaCDS 284 12 BaselIIandInternalRatings 285 CalculatingcapitalrequirementsintheInternalRatings-Based(IRB)approach 285 Assessingagivengradingstructure 288 Towardsanoptimalgradingstructure 294 Notesandliterature 297 AppendixA1 VisualBasicsforApplications(VBA) 299 AppendixA2 Solver 307 AppendixA3 MaximumLikelihoodEstimationandNewton’sMethod 313 AppendixA4 TestingandGoodnessofFit 319 AppendixA5 User-definedFunctions 325 Index 333
The list of books you might like
