Credit risk modeling using Excel and VBA Gunter Löffler Peter N. Posch Credit risk modeling using Excel and VBA For other titles in the Wiley Finance series please see www.wiley.com/finance Credit risk modeling using Excel and VBA Gunter Löffler Peter N. Posch Copyright©2007 JohnWiley&SonsLtd,TheAtrium,SouthernGate,Chichester, WestSussexPO198SQ,England Telephone (cid:2)+44(cid:3)1243779777 Email(forordersandcustomerserviceenquiries):[email protected] VisitourHomePageonwww.wiley.com AllRightsReserved.Nopartofthispublicationmaybereproduced,storedinaretrievalsystemortransmittedin anyformorbyanymeans,electronic,mechanical,photocopying,recording,scanningorotherwise,exceptunder thetermsoftheCopyright,DesignsandPatentsAct1988orunderthetermsofalicenceissuedbytheCopyright LicensingAgencyLtd,90TottenhamCourtRoad,LondonW1T4LP,UK,withoutthepermissioninwritingof thePublisher.RequeststothePublishershouldbeaddressedtothePermissionsDepartment,JohnWiley&Sons Ltd,TheAtrium,SouthernGate,Chichester,WestSussexPO198SQ,England,oremailedto [email protected],orfaxedto(+44)1243770620. Designationsusedbycompaniestodistinguishtheirproductsareoftenclaimedastrademarks.Allbrandnames andproductnamesusedinthisbookaretradenames,servicemarks,trademarksorregisteredtrademarksoftheir respectiveowners.ThePublisherisnotassociatedwithanyproductorvendormentionedinthisbook. Thispublicationisdesignedtoprovideaccurateandauthoritativeinformationinregardtothesubjectmatter covered.ItissoldontheunderstandingthatthePublisherisnotengagedinrenderingprofessionalservices. Ifprofessionaladviceorotherexpertassistanceisrequired,theservicesofacompetentprofessionalshouldbe sought. OtherWileyEditorialOffices JohnWiley&SonsInc.,111RiverStreet,Hoboken,NJ07030,USA Jossey-Bass,989MarketStreet,SanFrancisco,CA94103-1741,USA Wiley-VCHVerlagGmbH,Boschstr.12,D-69469Weinheim,Germany JohnWiley&SonsAustraliaLtd,42McDougallStreet,Milton,Queensland4064,Australia JohnWiley&Sons(Asia)PteLtd,2ClementiLoop#02-01,JinXingDistripark,Singapore129809 JohnWiley&SonsCanadaLtd,6045FreemontBlvd,Mississauga,ONT,L5R4J3,Canada Wileyalsopublishesitsbooksinavarietyofelectronicformats.Somecontentthatappearsinprintmaynotbe availableinelectronicbooks. AnniversaryLogoDesign:RichardJ.Pacifico LibraryofCongressCataloginginPublicationData Löffler,Gunter. CreditriskmodelingusingExcelandVBA/GunterLöffler,PeterN.Posch. p. cm. Includesbibliographicalreferencesandindex. ISBN978-0-470-03157-5(cloth:alk.paper) 1. Credit—Management 2. RiskManagement 3. MicrosoftExcel(Computerfile) 4. MicrosoftVisualBasicforapplications. I. Posch,PeterN. II. Title. HG3751.L642007 332.70285(cid:2)554—dc22 2007002347 BritishLibraryCataloguinginPublicationData AcataloguerecordforthisbookisavailablefromtheBritishLibrary ISBN978-0-470-03157-5(HB) Typesetin10/12ptTimesbyIntegraSoftwareServicesPvt.Ltd,Pondicherry,India PrintedandboundinGreatBritainbyAntonyRoweLtd,Chippenham,Wiltshire Thisbookisprintedonacid-freepaperresponsiblymanufacturedfromsustainableforestry inwhichatleasttwotreesareplantedforeachoneusedforpaperproduction. Mundus est is qui constat ex caelo, et terra et mare cunctisque sideribus. Isidoro de Sevilla Contents 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 Choosingthefunctionalrelationshipbetweenthescoreandexplanatoryvariables 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 67