Table Of ContentHedge Fund Modelling and
Analysis Using Excel and VBA
ForothertitlesintheWileyFinanceseries
pleaseseewww.wiley.com/finance
Hedge Fund Modelling and
Analysis Using Excel and VBA
Paul Darbyshire and David Hampton
A John Wiley & Sons, Ltd., Publication
Thiseditionfirstpublished2011
©2011JohnWiley&SonsLtd
Registeredoffice
JohnWiley&SonsLtd,TheAtrium,SouthernGate,Chichester,WestSussex,PO198SQ,United
Kingdom
Fordetailsofourglobaleditorialoffices,forcustomerservicesandforinformationabouthowto
applyforpermissiontoreusethecopyrightmaterialinthisbookpleaseseeourwebsiteat
www.wiley.com.
Therightoftheauthorstobeidentifiedastheauthorsofthisworkhasbeenassertedin
accordancewiththeCopyright,DesignsandPatentsAct1988.
Allrightsreserved.Nopartofthispublicationmaybereproduced,storedinaretrievalsystem,or
transmitted,inanyformorbyanymeans,electronic,mechanical,photocopying,recordingor
otherwise,exceptaspermittedbytheUKCopyright,DesignsandPatentsAct1988,withoutthe
priorpermissionofthepublisher.
Wileypublishesinavarietyofprintandelectronicformatsandbyprint-on-demand.Some
materialincludedwithstandardprintversionsofthisbookmaynotbeincludedine-booksorin
print-on-demand.IfthisbookreferstomediasuchasaCDorDVDthatisnotincludedinthe
versionyoupurchased,youmaydownloadthismaterialathttp://booksupport.wiley.com.For
moreinformationaboutWileyproducts,visitwww.wiley.com.
Designationsusedbycompaniestodistinguishtheirproductsareoftenclaimedastrademarks.
Allbrandnamesandproductnamesusedinthisbookaretradenames,servicemarks,trademarks
orregisteredtrademarksoftheirrespectiveowners.Thepublisherisnotassociatedwithany
productorvendormentionedinthisbook.Thispublicationisdesignedtoprovideaccurateand
authoritativeinformationinregardtothesubjectmattercovered.Itissoldontheunderstanding
thatthepublisherisnotengagedinrenderingprofessionalservices.Ifprofessionaladviceorother
expertassistanceisrequired,theservicesofacompetentprofessionalshouldbesought.
LibraryofCongressCataloging-in-PublicationData
Darbyshire,Paul.
HedgefundmodellingandanalysisusingExcelandVBA/PaulDarbyshireandDavid
Hampton.
p.cm.
ISBN978-0-470-74719-3(hardback)
1.Hedgefunds–Mathematicalmodels. 2.MicrosoftExcel(Computerfile) 3.MicrosoftVisual
Basicforapplications. I.Hampton,David. II.Title.
HG4530.D372012
332.64(cid:2)5240285554–dc23
2011046750
AcataloguerecordforthisbookisavailablefromtheBritishLibrary.
ISBN978-0-470-74719-3(hbk)ISBN978-1-119-94563-5(ebk)
ISBN978-1-119-94565-9(ebk)ISBN978-1-119-94564-2(ebk)
Setin11/13ptTimesbyAptaraInc.,NewDelhi,India
PrintedinGreatBritainbyTJInternationalLtd,Padstow,Cornwall
MumandDad
TowhomIoweeverything.
P.D.andD.H.
Contents
Preface xi
1 TheHedgeFundIndustry 1
1.1 WhatAreHedgeFunds? 1
1.2 TheStructureofaHedgeFund 4
1.2.1 FundAdministrators 5
1.2.2 PrimeBrokers 5
1.2.3 Custodian,AuditorsandLegal 6
1.3 TheGlobalHedgeFundIndustry 7
1.3.1 NorthAmerica 8
1.3.2 Europe 10
1.3.3 Asia 11
1.4 SpecialistInvestmentTechniques 12
1.4.1 ShortSelling 12
1.4.2 Leverage 14
1.4.3 Liquidity 15
1.5 NewDevelopmentsforHedgeFunds 16
1.5.1 UCITSIIIHedgeFunds 16
1.5.2 TheEuropeanPassport 19
1.5.3 RestrictionsonShortSelling 20
2 MajorHedgeFundStrategies 23
2.1 Single-andMulti-StrategyHedgeFunds 23
2.2 FundofHedgeFunds 25
viii Contents
2.3 HedgeFundStrategies 27
2.3.1 TacticalStrategies 28
2.3.1.1 GlobalMacro 28
2.3.1.2 ManagedFutures 31
2.3.1.3 Long/ShortEquity 36
2.3.1.4 PairsTrading 38
2.3.2 Event-Driven 42
2.3.2.1 DistressedSecurities 42
2.3.2.2 MergerArbitrage 46
2.3.3 RelativeValue 49
2.3.3.1 EquityMarketNeutral 49
2.3.3.2 ConvertibleArbitrage 50
2.3.3.3 FixedIncomeArbitrage 54
2.3.3.3.1 CapitalStructureArbitrage 56
2.3.3.3.2 Swap-SpreadArbitrage 57
2.3.3.3.3 YieldCurveArbitrage 58
3 HedgeFundDataSources 61
3.1 HedgeFundDatabases 61
3.2 MajorHedgeFundIndices 65
3.2.1 Non-investableandInvestableIndices 66
3.2.2 DowJonesCreditSuisseHedgeFundIndexes 68
3.2.2.1 LiquidAlternativeBetas 70
3.2.3 HedgeFundResearch 73
3.2.4 HedgeFund.net 77
3.2.5 FTSEHedge 77
3.2.5.1 FTSEHedgeMomentumIndex 78
3.2.6 GreenwichAlternativeInvestments 79
3.2.6.1 GAIInvestableIndices 80
3.2.7 MorningstarAlternativeInvestmentCenter 83
3.2.7.1 MSCIHedgeFundClassification
Standard 83
3.2.7.2 MSCIInvestableIndices 85
3.2.8 EDHECRiskandAssetManagementResearch
Centre(www.edhec-risk.com) 86
3.3 DatabaseandIndexBiases 88
3.3.1 SurvivorshipBias 89
3.3.2 InstantHistoryBias 90
3.4 Benchmarking 91
3.4.1 TrackingError 92
AppendixA:WeightingSchemes 95
Contents ix
4 StatisticalAnalysis 99
4.1 BasicPerformancePlots 99
4.1.1 ValueAddedMonthlyIndex 99
4.1.2 Histograms 102
4.2 ProbabilityDistributions 105
4.2.1 PopulationsandSamples 106
4.3 ProbabilityDensityFunction 107
4.4 CumulativeDistributionFunction 108
4.5 TheNormalDistribution 109
4.5.1 StandardNormalDistribution 110
4.6 VisualTestsforNormality 111
4.6.1 Inspection 111
4.6.2 NormalQ-QPlot 112
4.7 MomentsofaDistribution 114
4.7.1 MeanandStandardDeviation 114
4.7.2 Skewness 117
4.7.3 ExcessKurtosis 119
4.7.4 DataAnalysisTool:Descriptive
Statistics 120
4.8 GeometricBrownianMotion 122
4.8.1 UniformRandomNumbers 125
4.9 CovarianceandCorrelation 126
4.10 RegressionAnalysis 131
4.10.1 OrdinaryLeastSquares 131
4.10.1.1 CoefficientofDetermination 133
4.10.1.2 ResidualPlots 134
4.10.1.3 Jarque–BeraNormalityTest 135
4.10.1.4 DataAnalysisTool:Regression 138
4.11 PortfolioTheory 142
4.11.1 Mean–VarianceAnalysis 142
4.11.2 Solver:PortfolioOptimisation 145
4.11.3 EfficientPortfolios 148
5 Risk-AdjustedReturnMetrics 151
5.1 TheIntuitionbehindRisk-AdjustedReturns 152
5.1.1 Risk-AdjustedReturns 154
5.2 CommonRisk-AdjustedPerformanceRatios 157
5.2.1 TheSharpeRatio 160
5.2.2 TheModifiedSharpeRatio 162
5.2.3 TheSortinoRatio 163
5.2.4 TheDrawdownRatio 167
x Contents
5.3 CommonPerformanceMeasuresinthePresenceofa
MarketBenchmark 170
5.3.1 TheInformationRatio 172
5.3.2 TheM-SquaredMetric 173
5.3.3 TheTreynorRatio 174
5.3.4 Jensen’sAlpha 178
5.4 TheOmegaRatio 181
6 AssetPricingModels 185
6.1 TheRisk-AdjustedTwo-MomentCapital
AssetPricingModel 185
6.1.1 InterpretingH 189
6.1.2 StaticAlphaAnalysis 191
6.1.3 DynamicRollingAlphaAnalysis 193
6.2 Multi-factorModels 195
6.3 TheChoiceofFactors 196
6.3.1 AMulti-FactorFrameworkfora
Risk-AdjustedHedgeFundAlpha
LeagueTable 202
6.3.2 AlphaandBetaSeparation 208
6.4 DynamicStyleBasedReturnAnalysis 210
6.5 TheMarkowitzRisk-AdjustedEvaluationMethod 214
7 HedgeFundMarketRiskManagement 223
7.1 Value-at-Risk 223
7.2 TraditionalMeasures 226
7.2.1 HistoricalSimulation 226
7.2.2 ParametricMethod 229
7.2.3 MonteCarloSimulation 230
7.3 ModifiedVaR 233
7.4 ExpectedShortfall 236
7.5 ExtremeValueTheory 239
7.5.1 BlockMaxima 240
7.5.2 PeaksoverThreshold 241
References 245
ImportantLegalInformation 249
Index 251
Preface
Thisbookisapracticalintroductiontomodellingandanalysinghedge
funds using the popular Excel spreadsheet tool and Visual Basic for
Applications(VBA)programminglanguage.Thestructureofthebook
isasfollows.Chapters1–3coverthenecessaryfoundationsrequiredin
ordertounderstandhedgefundsandthealternativeinvestmentindustry.
With this fundamental knowledge in place, Chapters 4–7 cover the
morequantitativeandtheoreticalmaterialneededtoeffectivelyanalysea
seriesofhedgefundreturnsandextracttherelevantinformationrequired
inordertomakecriticalinvestmentdecisions.
ThroughoutthebooktherearenumeroussnapshotsofExcelspread-
sheetsandVBAsourcecode.Thesearedescribedasfollows.
EXCEL SPREADSHEETS
ThebookassumesaworkingknowledgeofExcel,withanabilitytoim-
plement simple built-in functions, such as SUM(), AVERAGE() and
STDEV(), and build dynamic spreadsheets. The following example
schematic explains how to interpret an Excel spreadsheet snapshot
withinthebook:
