Professional Financial Computing Using Excel and VBA

352 Pages·2010·6.113 MB·English

by  Humphrey K. K. Tung, Donny C. F. Lai, Michael C. S. Wong, Stephen NG(auth.)

Professional
Financial
Computing Using
Excel and VBA
Professional
Financial
Computing Using
Excel and VBA
HUMPHREY K. K. TUNG,
DONNY C. F. LAI, and
MICHAEL C. S. WONG
with STEPHEN NG
John Wiley & Sons (Asia) Pte. Ltd.
Copyright#2010JohnWiley&Sons(Asia)Pte.Ltd.
Publishedin2010byJohnWiley&Sons(Asia)Pte.Ltd.
2ClementiLoop,#02–01,Singapore129809
Allrightsreserved.
Nopartofthispublicationmaybereproduced,storedinaretrievalsystem,ortransmittedin
anyformorbyanymeans,electronic,mechanical,photocopying,recording,scanning,or
otherwise,exceptasexpresslypermittedbylaw,withouteitherthepriorwrittenpermissionof
thePublisher,orauthorizationthroughpaymentoftheappropriatephotocopyfeetothe
CopyrightClearanceCenter.RequestsforpermissionshouldbeaddressedtothePublisher,
JohnWiley&Sons(Asia)Pte.Ltd.,2ClementiLoop,#02–01,Singapore129809,
tel:[phone],fax:[phone],e-mail:[email protected].
Thispublicationisdesignedtoprovideaccurateandauthoritativeinformationinregardtothe
subjectmattercovered.Itissoldwiththeunderstandingthatthepublisherisnotengagedin
renderingprofessionalservices.Ifprofessionaladviceorotherexpertassistanceisrequired,the
servicesofacompetentprofessionalpersonshouldbesought.
Neithertheauthorsnorthepublisherareliableforanyactionspromptedorcausedbythe
informationpresentedinthisbook.Anyviewsexpressedhereinarethoseoftheauthorsanddo
notrepresenttheviewsoftheorganizationstheyworkfor.
OtherWileyEditorialOffices
JohnWiley&Sons,111RiverStreet,Hoboken,NJ07030,USA
JohnWiley&Sons,TheAtrium,SouthernGate,Chichester,WestSussex,P0198SQ,
UnitedKingdom
JohnWiley&Sons(Canada)Ltd.,5353DundasStreetWest,Suite400,Toronto,Ontario,
M9B6HB,Canada
JohnWiley&SonsAustraliaLtd.,42McDougallStreet,Milton,Queensland4064,Australia
Wiley-VCH,Boschstrasse12,D-69469Weinheim,Germany
LibraryofCongressCataloging-in-PublicationData
ISBN978–0–470–82439–9
Typesetin10.5/13ptSabon-RomanbyThomsonDigital,India
PrintedinSingaporebyToppanSecurityPrinting Pte.Ltd.
10 9 8 7 6 5 4 3 2 1
Contents
Preface ix
CHAPTER1
FinancialEngineeringandComputing 1
1.1 FinancialEngineeringandSpreadsheetModeling 1
1.2 LehmanBrothers’ProductsforRetailInvestors 3
1.3 RiskManagementandBaselII 4
1.4 AbouttheBook 4
1.5. ChapterHighlights 6
1.6 OtherRemarks 7
CHAPTER2
TheGARCH(1,1)Model 9
2.1. TheModel 9
2.2. ExcelImplementation 10
2.3. ExcelPlusVBAImplementation 15
CHAPTER3
FiniteDifferenceMethods 21
3.1. DifferenceEquations 21
3.2. ExcelImplementation 24
3.3. VBAImplementation 28
3.4. Crank–NicholsonScheme 33
CHAPTER4
PortfolioMean-VarianceOptimization 37
4.1. PortfolioSelection 37
4.2. ExcelImplementation 42
4.3. ExcelPlusVBAImplementation 48
v
vi CONTENTS
CHAPTER5
Newton–RaphsonMethod 59
5.1. Newton–RaphsonMethodforSystemsofEquations 59
5.2. VBARoutine 61
CHAPTER6
YieldCurveConstructionUsingCubicSpline 67
6.1. CubicSplineInterpolation 67
6.2. YieldCurveConstruction 75
6.3. ExcelPlusVBAImplementation 77
CHAPTER7
BinomialOptionPricingModel 85
7.1. Risk-NeutralOptionPricing
andtheBinomialTree 85
7.2. VBAImplementation 89
CHAPTER8
TheBlack–Derman–ToyModel 95
8.1. TheTermStructureModeland
theBlack–Derman–ToyTree 95
8.2. ExcelPlusVBAImplementation 98
CHAPTER9
MonteCarloOptionPricing 109
9.1. TheMonteCarloMethod 109
9.2. Risk-NeutralValuation 112
9.3. VBAImplementation 114
9.4. ExoticOptions 124
9.5. AmericanOptions 132
CHAPTER10
PortfolioValue-at-Risk 143
10.1. PortfolioRiskSimulation 143
10.2. MonteCarloSimulationforMultiple-AssetPortfolios 152
10.3. HistoricalSimulationforMultiple-AssetPortfolios 160
10.4. VBAImplementationofPortfolioRiskSimulation 164
10.5. DrillDownofPortfolioRisk 180
vii
Contents
CHAPTER11
TheHull–WhiteModel 189
11.1. Hull–WhiteTrinomialTree 189
11.2. ExcelPlusVBAImplementation 196
11.3. TheGeneralHull–WhiteModel 203
11.4. ImplementationoftheGeneral
Hull–WhiteModel 210
CHAPTER12
CreditMetricsModel 221
12.1. TheCreditMetricsModel 221
12.2. Individual(Segregate)AssetValuationFramework 221
12.3 MonteCarloSimulationinDetail 225
12.4. ExcelandVBAImplementation 227
CHAPTER13
KMV–MertonModel 243
13.1. KMV–MertonModelofCreditRisk 243
13.2. ExcelandVBAImplementation 248
APPENDIXA
VBAProgramming 255
A.1 Introduction 255
A.2 ABriefHistoryofVBA 255
A.3 EssentialExcelElementsforVBA 256
A.3.1 ExcelCellReference 257
A.3.2 ExcelDefinedNames 261
A.3.3 ExcelWorksheetFunctions 264
A.4 TheVBADevelopmentEnvironment(VBE) 266
A.4.1 TheDeveloperTabintheRibbon 266
A.4.2 TheWindowsofVBE 268
A.4.3 TheProjectExplorer 272
A.4.4 TheVBAProjectStructure 273
A.4.5 TheProceduretoCreateaVBASubroutine 275
A.4.6 TheProceduretoCreateaVBAFunction 278
A.5 BasicVBAProgrammingConcepts 280
A.5.1 VariablesandDataTypes 285
A.5.2 DeclarationandAssignmentStatements 287
A.5.3 FlowControlStatements 293
A.6 VBAArrays 300
viii CONTENTS
A.7 UsingWorksheetMatrixFunctionsinVBA 304
A.8 Summary 311
APPENDIXB
TheExcelObjectModel 315
APPENDIXC
VBADebuggingTools 321
APPENDIXD
SummaryofVBAOperators 327
APPENDIXE
SummaryofVBAFunctions 331
APPENDIXF
SummaryofVBAStatements 333
APPENDIXG
ExcelArrayFormula 341
Index 349
Preface
T
his book is a good company to Master degree programs in Financial
Engineering, Financial Risk Management, Quantitative Investment,
Computational Finance, or Mathematical Finance. Also, risk managers,
traders,ITanalysts,quantitativeanalystsworkingininvestmentbanksand
hedgefundwillfindittobeagoodreference.
ThebookprovidesVBAexamplesonsomewidely-usedfinanceandrisk
models.Weexpectthatreadershavepriortrainingonthesemodelsbecause
some of them require strong mathematical foundation. Through the
examples, readers can easily build their implementable analytics and apply
similarskillstoothercomplexmodels.
Feedbacks from professors, students, analysts, and risk professionals
arewarmlywelcome.
HumphreyTung
DonnyLai
MichaelWong
StephenNg
Email:[email protected]
ix
Professional Financial Computing Using Excel and VBA
by Humphrey K. K. Tung, Donny C. F. Lai, Michael C. S. Wong and Stephen NG
Copyright © 2010 John Wiley & Sons (Asia) Pte. Ltd.
1
CHAPTER
Financial Engineering
and Computing
1.1 FINANCIAL ENGINEERING AND SPREADSHEET
MODELING
‘‘SpreadsheetModelingforFinance’’haslongbeenapopularcourseinthe
MSc Financial Engineering program at the university we served in Hong
Kong. The course is different from introductory Excel courses in financial
management.Itisanadvancedcourseofferedmainlytostudentswithsolid
training in mathematical finance, option pricing, and risk modeling. Most
of the students in the course have been designated a chartered financial
analyst (CFA) or certified as a financial risk manager (FRM). The financial
engineeringprogram mainlyrecruitspart-timestudentsworkinginvarious
financialinstitutions.Therearearound40to60newrecruitseachyear.Many
ofthemarederivativestraders,bankriskmanagers,bankITspecialists,fund
managers, product structurers, bank regulators, and product auditors. In
1997–2008, the program trained more than 500 graduates. Most of them
successfullyappliedtheknowledgegainedtotheirdailywork.
Some may ask why no ‘‘quantitative analysts’’ are mentioned. Loosely
speaking, these financial engineering graduates are quantitative analysts in
nature. Strictly speaking, none of them carries the job title ‘‘quantitative
analyst.’’ A global investment bank may have one or two quantitative
analysts and/or financial engineers in Hong Kong. Given the presence of
15 global institutions, there are a maximum of 10 quantitative analyst
job vacancies a year. This number cannot satisfy the continuous supply of
financialengineering graduates every year. Although our graduates are not
called quantitative analysts, their training in financial engineering did help
their fast career development. Also, their quantitative skills have enabled
HongKongtoexcelinfinancialservices.
1
2 PROFESSIONALFINANCIALCOMPUTINGUSINGEXCELANDVBA
Whenweplannedthisbookin2007,thefinancialmarketinHongKong
was very bullish. Many China initial public offering (IPO) deals were
completedinHongKong.TheHangSengIndexreachedover30,000points.
Structured products and hedge funds were prevalent in corporate banking
and private banking. Equity-linked notes, minibonds, and currency-linked
productswerecommoninretailbanking.
In addition to sizable financial institutions, Hong Kong is a hub of
boutique hedge funds. It is believed that there are 600 or more. These hedge
funds employ few people, but their asset under management (AUM) can be
over US$100 million each.In these hedgefunds, financial andriskanalysis is
mostlybasedonExcelandVisualBasicforApplications(VBA)programming.
Thisisareasonwhythecourse‘‘SpreadsheetModeling’’isverypopular.
Our progress in writing this book was hindered by the financial tsunami
in2008.Highmarketvolatility,depreciationofwealth,andmassivelayoffsin
the banking sector brought a lot of frustration to financial practitioners and
financial educators. When we completed this book in June 2009, the market
remained very weak. Many wealthy individuals suffered huge losses in the
past 12 months; financial institutions cut their manpower seriously; selling
complexproductsbecamedifficult;andnewregulationswereenactedrelating
to structured products. In 2009, students in the course ‘‘Spreadsheet Model-
ing’’stillenjoyedtheclassbutwereslightlyworriedoutsideoftheclass.This
is because the next round, which would be the fourth or fifth round, of
massive layoffs would affect them. Investment banking follows obvious
businesscycles.Thisappliestostudyprogramsinfinancialengineeringaswell.
Mature students are always pragmatic in acquiring knowledge.
Complexmathematicsisveryfancy,butour maturestudentstend totakeit
for granted and focus mostly on the applications of the mathematics. The
course ‘‘Spreadsheet Modeling’’ makes those fancy mathematical concepts
more easily applicable. From the perspective of educators, this mindset
of the students is not harmful. After using Excel and VBA to build their
models, some students become more interested in complex mathematics.
What we would like them to know is not simply building models for
financial analysis. We wish that they could understand model risks and
estimate when these risks are likely to occur. The increased curiosity of our
studentsafterthecoursemadeusfeelsatisfiedaboutoureducationalefforts.
Many new financial products have no mathematical models. Due
to the advancement of technology, an analyst can easily apply Monte
Carlo simulation on related variables and find out an average value. Our
students especially like this analytical approach because there is less of a
mathematicalfoundationrequired.Infact,ExcelandVBAcaneasilyhandle
MonteCarlosimulation.