Reverse Engineering Deals on Wall Street with Microsoft Excel: A Step-by-Step Guide

225 Pages·2008·4.39 MB·English

by  Keith Allman

Economics / Investments & Securities A
l
lm Loop through each loan
Praise for a For k = 1 To TotalLoans1
nefault scenarios where defaults can exceed the beginning
Reverse Engineering Deals on Wall Street with Microsoft Excel
equal the beginning balance.
R Clear the notional amort arrays
e0) = min (gp 1 DefaultRange (k, (i + gp 1Season Array (k,
v For q = 0 To TotalPeriods
e * gp1OrgBalArray (k, 0) , ActualBegBal (i, 0)
r NotionalBegBal (q, 0) = 0
s0) = ActualNewDef (i, 0) + AggNewDef (i, 0)
“Allman has written an excellent sequel to his fi rst book Modeling Structured Finance Cash Flows with e NotionalPMT (q, 0) = 0
E NotionalInt (q, 0)
Microsoft Excel, providing a simple guide on how to deconstruct cash fl ows through modeling and n (cid:73)(cid:60)(cid:77)(cid:60)(cid:73)(cid:74)(cid:60) NotionalPrin (q, 0) = 0
understanding the collateral by analyzing the prospectus. Whether you are new to the structured fi nance industry g
i NotionalEndBal (q, 0)
n
or steeped in structured fi nancing—this book is a must-read.”
e NotionalAmortFact (q, 0) = 0
e
r CurRateArray (q, 0) = 0
—Ralph Armenta, PMI Mortgage Insurance/Structured Transactions i
n Next q
g

D
“The credit crisis has shown that more fi nancial institutions and analysts need to be able to reverse-engineer
e
structured fi nance deals to assess their values and risks for themselves. Keith Allman has produced a thorough a
l
s
guide for creating reverse-engineered models that both beginners and experienced analysts will fi nd invaluable.” (cid:60)(cid:69)(cid:62)(cid:64)(cid:69)(cid:60)(cid:60)(cid:73)(cid:64)(cid:69)(cid:62)(cid:23)(cid:59)(cid:60)(cid:56)(cid:67)(cid:74)
o
n
—Chandan Sengupta, author of Financial Modeling Using Excel and VBA
W
a
l
l

S
A serious source of information for those looking to reverse-engineer business deals t
r
e (cid:102)(cid:101)(cid:23)(cid:78)(cid:88)(cid:99)(cid:99)(cid:23)(cid:74)(cid:107)(cid:105)(cid:92)(cid:92)(cid:107)
e
t
How does Wall Street keep track of the competition? They reverse-engineer deals by taking a prospectus or
w
term sheet and recreating a model from it. The skills involved in this task are sophisticated, but are important to i
t
understand—especially in today’s dynamic business environment. h

M
i
In this practical resource, fi nancial professional Keith Allman demystifi es the process by interpreting compli- c
r
cated legal terminology and clearly showing how it can be organized into a dynamic model. Step by step, Allman o (cid:110)(cid:96)(cid:107)(cid:95)(cid:23)(cid:68)(cid:96)(cid:90)(cid:105)(cid:102)(cid:106)(cid:102)(cid:93)(cid:107)(cid:23)(cid:60)(cid:111)(cid:90)(cid:92)(cid:99)(cid:23)(cid:23)(cid:34)(cid:58)(cid:59)
s
walks you through this endeavor with textual excerpts from the prospectus and details how it directly transfers o
f
to a model. Each chapter begins with a discussion of concepts with exact references to an example prospectus, t

E
followed by a section called “Model Builder,” in which Allman translates the theory into a fully functioning model x
c A Step-by-Step Guide
for the example deal. In addition, the companion CD-ROM features all of the modeling exercises, as well as a fi nal e
l
version of the model that is outlined in the text. A

S
KEITH A. ALLMAN is a capital markets professional with a specialization in analytics and modeling. He is te
p
currently the principal trainer and founder of Enstruct, a quantitative fi nance training company, as well as a -
b CD-ROM includes modeling exercises and a
y
Managing Director with NSM Capital Management. Prior to this, Allman was a vice president at Citigroup’s Global -
S
Corporate and Investment Bank. He has also worked for MBIA Corporation in their Quantitative Analytics division. te final version of the model discussed in the text
p
Allman is the author of Modeling Structured Finance Cash Flows with Microsoft Excel, which is published by Wiley.
G
u
i
d
$95.00 USA/$105.00 CAN e
(cid:66)(cid:92)(cid:96)(cid:107)(cid:95)(cid:23)(cid:56)(cid:37)(cid:23)(cid:56)(cid:99)(cid:99)(cid:100)(cid:88)(cid:101)
Includes
CD-ROM
F(Balances!$F 15,liab12_FxdRatteeSStteeppDDwwnn**OOFFFFSSEETT(($$BB1100,,00,,MMAATTCCHH((lliiaabb
2_DayCtSys, mezz_DayCountSys,))))**BBallance!!BB001144, lliabb1122_FFxddRRate**OOFF
ET($B10,0,MATCH(liab12_DayCtSys, mezz_DayCountSys,0))*Balance
014
fm JWBT003-Allman October18,2008 14:38 Printer:Malloy
ii
fm JWBT003-Allman October18,2008 14:38 Printer:Malloy
Reverse Engineering
Deals on Wall Street
with Microsoft Excel
i
fm JWBT003-Allman October18,2008 14:38 Printer:Malloy
Founded in 1807, John Wiley & Sons is the oldest independent publishing com-
pany in the United States. With offices in North America, Europe, Australia, and
Asia,Wileyisgloballycommittedtodevelopingandmarketingprintandelectronic
products and services for our customers’ professional and personal knowledge and
understanding.
TheWileyTradingseriesfeaturesbooksbytraderswhohavesurvivedthemar-
ket’sever-changingtemperamentandhaveprospered—somebyreinventingsystems,
othersbygettingbacktobasics.Whetheranovicetrader,professional,orsomewhere
in-between,thesebookswillprovidetheadviceandstrategiesneededtoprosperto-
dayandwellintothefuture.
Foralistofavailabletitles,visitourwebsiteatwww.WileyFinance.com.
ii
fm JWBT003-Allman October18,2008 14:38 Printer:Malloy
Reverse Engineering
Deals on Wall Street
with Microsoft Excel
A Step-by-Step Guide
KEITH A. ALLMAN
John Wiley & Sons, Inc.
iii
fm JWBT003-Allman October18,2008 14:38 Printer:Malloy
Copyright(cid:1)C 2009byKeithA.Allman.Allrightsreserved.
PublishedbyJohnWiley&Sons,Inc.,Hoboken,NewJersey.
PublishedsimultaneouslyinCanada.
Nopartofthispublicationmaybereproduced,storedinaretrievalsystem,ortransmittedinanyform
orbyanymeans,electronic,mechanical,photocopying,recording,scanning,orotherwise,exceptas
permittedunderSection107or108ofthe1976UnitedStatesCopyrightAct,withouteithertheprior
writtenpermissionofthePublisher,orauthorizationthroughpaymentoftheappropriateper-copyfeeto
theCopyrightClearanceCenter,Inc.,222RosewoodDrive,Danvers,MA01923,[phone],fax
[phone],orontheWebatwww.copyright.com.RequeststothePublisherforpermissionshould
beaddressedtothePermissionsDepartment,JohnWiley&Sons,Inc.,111RiverStreet,Hoboken,NJ
07030,[phone],fax[phone],oronlineathttp://www.wiley.com/go/permissions.
LimitofLiability/DisclaimerofWarranty:Whilethepublisherandauthorhaveusedtheirbesteffortsin
preparingthisbook,theymakenorepresentationsorwarrantieswithrespecttotheaccuracyor
completenessofthecontentsofthisbookandspecificallydisclaimanyimpliedwarrantiesof
merchantabilityorfitnessforaparticularpurpose.Nowarrantymaybecreatedorextendedbysales
representativesorwrittensalesmaterials.Theadviceandstrategiescontainedhereinmaynotbesuitable
foryoursituation.Youshouldconsultwithaprofessionalwhereappropriate.Neitherthepublishernor
authorshallbeliableforanylossofprofitoranyothercommercialdamages,includingbutnotlimited
tospecial,incidental,consequential,orotherdamages.
Designationsusedbycompaniestodistinguishtheirproductsareoftenclaimedastrademarks.Inall
instanceswhereJohnWiley&Sons,Inc.isawareofaclaim,theproductnamesappearininitialcapital
orallcapitalletters.Readers,however,shouldcontacttheappropriatecompaniesformorecomplete
informationregardingtrademarksandregistration.
MicrosoftandExcelareregisteredtrademarksofMicrosoftCorporation.
MicrosoftExcelscreenshotsreprintedwithpermissionfromMicrosoftCorporation.
Forgeneralinformationonourotherproductsandservicesorfortechnicalsupport,pleasecontactour
CustomerCareDepartmentwithintheUnitedStatesat(800)762-2974,outsidetheUnitedStatesat
[phone]orfax(317)572-4002.
Wileyalsopublishesitsbooksinavarietyofelectronicformats.Somecontentthatappearsinprintmay
notbeavailableinelectronicbooks.FormoreinformationaboutWileyproducts,visitourwebsiteat
www.wiley.com.
LibraryofCongressCataloging-in-PublicationData:
Allman,KeithA.,1977–
ReverseengineeringdealsonWallStreetwithMicrosoftExcel:astep-by-stepguide/KeithA.Allman.
p.cm.—(Wileyfinanceseries)
Includesindex.
ISBN978-0-470-24205-6(paper/cd-rom)
1.Financialengineering—Mathematicalmodels. 2.Investments—Mathematicalmodels.
3.Deals—Mathematicalmodels. 4.MicrosoftExcel(Computerfile) I.Title.
HG176.7.A452009
338.8(cid:2)30285554–dc22
2008025012
PrintedintheUnitedStatesofAmerica.
10 9 8 7 6 5 4 3 2 1
iv
fm JWBT003-Allman October18,2008 14:38 Printer:Malloy
Contents
Preface ix
Acknowledgments xiii
AbouttheAuthor xv
CHAPTER1
Introduction 1
TheTransaction 2
TheDocuments 2
TheProcess 6
HowThisBookWorks 12
CHAPTER2
DeterminingDatesandSettingUpTiming 15
DifferencesinTimingApproaches 15
AFirstLookattheProspectus 16
ImportantDates 17
TransformingDatesandTimingfromWordstoaModel 21
ModelBuilder2.1:ReversingDatesandTiming 21
ConclusionofDatesandTiming 27
CHAPTER3
CreatingAssetCashFlowfromProspectusData 29
It’sAllintheProspectusSupplement 29
TheBasicsofAmortization 32
PerformanceandtheProspectusSupplement 33
Delinquency 34
Loss 35
Prepayment 36
Recovery 38
CreatingCashFlow 38
v
fm JWBT003-Allman October18,2008 14:38 Printer:Malloy
vi CONTENTS
AComplexImplementation 39
ModelBuilder3.1:EnteringintheRawAssetInformation 40
ModelBuilder3.2:EnteringintheDefaultandPrepaymentAssumptions 43
ModelBuilder3.3:InterestRatesandAdditionalAssetAmortizationInputs 47
ModelBuilder3.4:IntroducingVBAandMovingDataInand
OutoftheModel 50
ModelBuilder3.5:LoadingLoanPerformanceAssumptionsintoVBA 57
ModelBuilder3.6:GlobalFunctions 60
ModelBuilder3.7:Loan-LevelAssetAmortization 61
CHAPTER4
SettingUpLiabilityAssumptions,PayingFees,andDistributingInterest 81
IdentifyingtheOfferedSecurities 81
ModelBuilder4.1:TransferringtheLiabilityInformationtoa
ConsolidatedSheet 84
TheLiabilityWaterfall:ASystemofPriority 87
ModelBuilder4.2:StartingtheWaterfallwithFees 89
Interest:NoFinancingIsFree 91
ModelBuilder4.3:ContinuingtheWaterfallwithInterestPaidtothe
CertificateHolders 93
MoreonWaterfallsandWallStreet’sRiskParsing 106
ModelBuilder4.4:MezzanineInterest 106
ContinuingtheWaterfall:ItOnlyGetsMoreComplicated 114
CHAPTER5
PrincipalRepaymentandtheShiftingNatureofaWallStreetDeal 115
ModelBuilder5.1:TheDealStateandSeniorPrincipal 116
MezzaninePrincipalReturns 142
ModelBuilder5.2:TheMezzanineCertificates’PriorityofPayments 142
NumberGamesorRiskParsing? 147
CHAPTER6
CreditEnhancementMechanismstoMitigateLoss 149
ModelBuilder6.1:ExcessSpread,Overcollateralization,and
CreditEnhancement 151
CHAPTER7
AuditingtheModel 173
ModelBuilder7.1 174
fm JWBT003-Allman October18,2008 14:38 Printer:Malloy
Contents vii
CHAPTER8
ConclusionofExampleTransactionandFinalThoughtsonReverseEngineering 187
MortgageInsuranceandServicerAdvances 187
ReverseEngineeringintheCurrentandFutureMarket 188
Appendix 189
AutomaticRangeNaming 189
AbouttheCD-ROM 193
Index 197
fm JWBT003-Allman October18,2008 14:38 Printer:Malloy
viii