Credit Risk Modeling using Excel and VBA (The Wiley Finance Series)

278 Pages·2007·18.54 MB·English

by  Gunter Loeffler

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([phone].
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