Advanced Modelling In Finance Using Excel And VBA

3y ago
88 Views
10 Downloads
1.34 MB
262 Pages
Last View : 1m ago
Last Download : 2m ago
Upload by : River Barajas
Transcription

Advanced Modelling in Financeusing Excel and VBAMary JacksonandMike StauntonJOHN WILEY & SONS, LTDChichesteržNew YorkžWeinheimžBrisbanežSingaporežToronto

Copyright 2001 by John Wiley & Sons, Ltd,Baffins Lane, Chichester,West Sussex PO19 1UD, EnglandNational01243 779777International (C44) 1243 779777e-mail (for orders and customer service enquiries): cs-books@wiley.co.ukVisit our Home Page on http://www.wiley.co.ukor http://www.wiley.comAll Rights Reserved. No part of this publication may be reproduced, stored in a retrievalsystem, or transmitted, in any form or by any means, electronic, mechanical, photocopying,recording, scanning or otherwise, except under the terms of the Copyright, Designs and Patents Act1988 or under the terms of a licence issued by the Copyright Licensing Agency, 90 Tottenham CourtRoad, London W1P 9HE, UK, without the permission in writing of the publisher.Other Wiley Editorial OfficesJohn Wiley & Sons, Inc., 605 Third Avenue,New York, NY 10158-0012, USAWiley-VCH Verlag GmbH, Pappelallee 3,D-69469 Weinheim, GermanyJohn Wiley & Sons Australia Ltd, 42 McDougall Street, Milton,Queensland 4064, AustraliaJohn Wiley & Sons (Asia) Pte Ltd, 2 Clementi Loop #02-01,Jin Xing Distripark, Singapore 129809John Wiley & Sons Canada Ltd, 6045 Freemont Blvd,Mississauga, ONT, L5R 4J3, CanadaBritish Library Cataloguing in Publication DataA catalogue record for this book is available from the British LibraryISBN 0 471 49922 6Typeset in 10/12pt Times by Laserwords Private Limited, Chennai, IndiaPrinted and bound in Great Britain by Bookcraft (Bath) Ltd, Midsomer–NortonThis book is printed on acid-free paper responsibly manufactured from sustainable forestry,in which at least two trees are planted for each one used for paper production.

ContentsPrefacexiAcknowledgementsxii1 Introduction1.1 Finance insights1.2 Asset price assumptions1.3 Mathematical and statistical problems1.4 Numerical methods1.5 Excel solutions1.6 Topics covered1.7 Related Excel workbooks1.8 Comments and suggestions112223355Part One Advanced Modelling in Excel72 Advanced Excel functions and procedures2.1 Accessing functions in Excel2.2 Mathematical functions2.3 Statistical functions2.3.1 Using the frequency function2.3.2 Using the quartile function2.3.3 Using Excel’s normal functions2.4 Lookup functions2.5 Other functions2.6 Auditing tools2.7 Data Tables2.7.1 Setting up Data Tables with one input2.7.2 Setting up Data Tables with two inputs2.8 XY charts2.9 Access to Data Analysis and Solver2.10 Using range names2.11 Regression2.12 Goal Seek9910121214151618192020222326272831

viContents2.13 Matrix algebra and related functions2.13.1 Introduction to matrices2.13.2 Transposing a matrix2.13.3 Adding matrices2.13.4 Multiplying matrices2.13.5 Matrix inversion2.13.6 Solving systems of simultaneous linear equations2.13.7 Summary of Excel’s matrix functionsSummary3333333434353637373 Introduction to VBA3.1 Advantages of mastering VBA3.2 Object-oriented aspects of VBA3.3 Starting to write VBA macros3.3.1 Some simple examples of VBA subroutines3.3.2 MsgBox for interaction3.3.3 The writing environment3.3.4 Entering code and executing macros3.3.5 Recording keystrokes and editing code3.4 Elements of programming3.4.1 Variables and data types3.4.2 VBA array variables3.4.3 Control structures3.4.4 Control of repeating procedures3.4.5 Using Excel functions and VBA functions in code3.4.6 General points on programming3.5 Communicating between macros and the spreadsheet3.6 Subroutine examples3.6.1 Charts3.6.2 Normal probability plot3.6.3 Generating the efficient frontier with SolverSummaryReferencesAppendix 3A The Visual Basic EditorStepping through a macro and using otherdebug toolsAppendix 3B Recording keystrokes in ‘relative references’ 6568694 Writing VBA user-defined functions4.1 A simple sales commission function4.2 Creating Commission(Sales) in the spreadsheet4.3 Two functions with multiple inputs for valuing options4.4 Manipulating arrays in VBA4.5 Expected value and variance functions with array inputs4.6 Portfolio variance function with array inputs4.7 Functions with array output4.8 Using Excel and VBA functions in user-defined functions737374757879818485

Contents4.94.8.1 Using VBA functions in user-defined functions4.8.2 Add-insPros and cons of developing VBA functionsSummaryAppendix 4A Functions illustrating array handlingAppendix 4B Binomial tree option valuation functionsExercises on writing functionsSolution notes for exercises on functionsPart TwoEquitiesvii8586868788899495995 Introduction to equities1016 Portfolio optimisation6.1 Portfolio mean and variance6.2 Risk–return representation of portfolios6.3 Using Solver to find efficient points6.4 Generating the efficient frontier (Huang and Litzenberger’sapproach)6.5 Constrained frontier portfolios6.6 Combining risk-free and risky assets6.7 Problem One–combining a risk-free asset with a risky asset6.8 Problem Two–combining two risky assets6.9 Problem Three–combining a risk-free asset with a risky portfolio6.10 User-defined functions in Module16.11 Functions for the three generic portfolio problems in Module16.12 Macros in ModuleMSummaryReferences1031031051067 Asset7.17.27.37.47.57.67.77.8pricingThe single-index modelEstimating beta coefficientsThe capital asset pricing modelVariance–covariance matricesValue-at-RiskHorizon wealthMoments of related distributions such as normal and lognormalUser-defined functions in Module1SummaryReferences8 Performance measurement and attribution8.1 Conventional performance measurement8.2 Active–passive management8.3 Introduction to style 129130131134136136138138139140141144

viiiContents8.48.58.68.78.8Simple style analysisRolling-period style analysisConfidence intervals for style weightsUser-defined functions in Module1Macros in ModuleMSummaryReferences145146148151151152153Part Three Options on 6Introduction to options on equities9.1 The genesis of the Black–Scholes formula9.2 The Black–Scholes formula9.3 Hedge portfolios9.4 Risk-neutral valuation9.5 A simple one-step binomial tree with risk-neutral valuation9.6 Put–call parity9.7 Dividends9.8 American features9.9 Numerical methods9.10 Volatility and non-normal share returnsSummaryReferences10 Binomial trees10.1 Introduction to binomial trees10.2 A simplified binomial tree10.3 The Jarrow and Rudd binomial tree10.4 The Cox, Ross and Rubinstein tree10.5 Binomial approximations and Black–Scholes formula10.6 Convergence of CRR binomial trees10.7 The Leisen and Reimer tree10.8 Comparison of CRR and LR trees10.9 American options and the CRR American tree10.10 User-defined functions in Module0 and 818018218318411 The Black–Scholes formula11.1 The Black–Scholes formula11.2 Black–Scholes formula in the spreadsheet11.3 Options on currencies and commodities11.4 Calculating the option’s ‘greek’ parameters11.5 Hedge portfolios11.6 Formal derivation of the Black–Scholes formula185185186187189190192

Contentsix11.7 User-defined functions in Module1SummaryReferences19419519612Other numerical methods for European options12.1 Introduction to Monte Carlo simulation12.2 Simulation with antithetic variables12.3 Simulation with quasi-random sampling12.4 Comparing simulation methods12.5 Calculating greeks in Monte Carlo simulation12.6 Numerical integration12.7 User-defined functions in 720713Non-normal distributions and implied volatility13.1 Black–Scholes using alternative distributional assumptions13.2 Implied volatility13.3 Adapting for skewness and kurtosis13.4 The volatility smile13.5 User-defined functions in rt FourOptions on Bonds22114Introduction to valuing options on bonds14.1 The term structure of interest rates14.2 Cash flows for coupon bonds and yield to maturity14.3 Binomial trees14.4 Black’s bond option valuation formula14.5 Duration and convexity14.6 3015Interest rate models15.1 Vasicek’s term structure model15.2 Valuing European options on zero-coupon bonds, Vasicek’s model15.3 Valuing European options on coupon bonds, Vasicek’s model15.4 CIR term structure model15.5 Valuing European options on zero-coupon bonds, CIR model15.6 Valuing European options on coupon bonds, CIR model15.7 User-defined functions in 0241

xContents16 Matching the term structure16.1 Trees with lognormally distributed interest rates16.2 Trees with normal interest rates16.3 The Black, Derman and Toy tree16.4 Valuing bond options using BDT trees16.5 User-defined functions in pendix Other VBA functionsForecastingARIMA modellingSplinesEigenvalues and eigenvectorsReferences253253254256257258Index259

PrefaceWhen asked why they tackled Mount Everest, climbers typically reply “Because it wasthere”. Our motivation for writing Advanced Modelling in Finance is for exactly theopposite reason. There were then, and still are now, almost no books that give dueprominence to and explanation of the use of VBA functions within Excel. There is analmost similar lack of books that capture the true vibrant spirit of numerical methodsin finance.It is no longer true that spreadsheets such as Excel are inadequate tools in highly technical and numerically demanding areas such as the valuation of financial derivatives. Withefficient code and VBA functions, calculations that were once the preserve of dedicatedpackages and languages can now be done on a modern PC in Excel within seconds, ifnot fractions of a second. By employing Excel and VBA, our purpose is to try to bringclarity to an area that was previously covered with black boxes.What started as an attempt to push back the boundaries of Excel through macros turnedinto a full-scale expedition into the VBA language within Excel and then developed fromequities, through options and finally to cover bonds. Along the way we learned scores ofnew Excel skills and a much greater understanding of the numerical methods implementedacross finance.The genesis of the book came from material developed for the ‘Computer-Based Financial Modelling’ elective on the MBA degree at London Business School. The part onequities formed the basis for an executive course on ‘Equity Portfolio Management’ runannually by the International Centre for Money and Banking in Geneva. The parts onoptions and bonds comprise a course in ‘Numerical Methods’ on the MSc in Mathematical Trading and Finance at City University Business School. The book is within the reachof both students at the postgraduate level and those in the latter undergraduate years.There are no prerequisites for readers apart from a willingness to adopt a pro-activestance when using the book–namely by taking advantage of the inherent ‘what-if’ qualityof the spreadsheets and by looking at and using the code forming the VBA user-definedfunctions. Since we assume for the most part that asset returns are lognormal and thereforeuse binomial trees as a central numerical method, our explanations can be based onfamiliar results from probability and statistics. Comprehension is helped by the use of acommon notation throughout, and transparency by the availability of complete solutionsin both Excel and VBA forms.

AcknowledgementsOur main debt is to the individuals from the academic and practitioner communities infinance who first developed the theory and then the numerical methods that form thematerial for this book. In the words of Sir Isaac Newton “If I have seen further it is bystanding on the shoulders of giants”.We would also like to thank our colleagues at both London Business School and CityUniversity Business School, in particular Elroy Dimson, John Hatgioannides, Paul Marshand Kiriakos Vlahos.We would like to thank Sam Whittaker at Wiley for her enthusiasm, encouragementand much needed patience, invaluable qualities for an editor.Last but not least, we are grateful for the patience of family and friends who haveoccasionally chivvied us about the book’s somewhat lengthy gestation period.

1IntroductionWe hope that our text, Advanced Modelling in Finance, is conclusive proof that a widerange of models can now be successfully implemented using spreadsheets. The modelsrange across the complete spectrum of finance including equities, equity options and bondoptions spanning developments from the early fifties to the late nineties. The models areimplemented in Excel spreadsheets, complemented with functions written using the VBAlanguage within Excel. The resulting user-defined functions provide a portable library ofprograms with more than sufficient speed and accuracy.Advanced Modelling in Finance should be viewed as a complement (or dare we say,an antidote) to traditional textbooks in the area. It contains relatively few derivations,allowing us to cover a broader range of models and methods, with particular emphasison more recent advances.The major theoretical developments in finance such as portfolio theory in the 1950s,the capital asset pricing model in the 1960s and the Black–Scholes formula in the 1970sbrought with them analytic solutions that are now straightforward to calculate. The subsequent decades have seen a growing body of developments in numerical methods. With anintelligent choice of parameters, binomial trees have assumed a central role in the morenumerically-intensive calculations now required to value equity and bond options. Thecentre of gravity in finance now concerns the search for more efficient ways of performingsuch calculations rather than the theories from yesteryear.The breadth of the coverage across finance and the sophistication needed for someof the more advanced models are testament to the ability of Excel, the built-in functionscontained in Excel and the real programming environment that VBA provides. This allowsus to highlight the commonality of assumptions (lognormality), mathematical problems(expectation) and numerical methods (binomial trees) throughout finance as a whole.Without exception, we have tried to ensure a consistent and simple notation throughoutthe book to reinforce this commonality and to improve clarity of exposition.Our objective in writing a book that covers the broad range of subjects in financehas proved to be both a challenge and an opportunity. The opportunity has providedus with the chance to overview finance as a whole and, in so doing, to make important connections and bring out commonalities in asset price assumptions, mathematical problems, numerical methods and Excel solutions. In the following sections wesummarise a few of these unifying insights that apply to equities, options and bondswith regard to finance, mathematical topics, numerical methods and Excel features. Thisis followed by a more detailed summary of the main topics covered in each chapter ofthe book.1.1 FINANCE INSIGHTSThe genesis of modern finance as a subject separate from economics started withMarkowitz’s development of portfolio theory in 1952. Markowitz used utility theory tomodel the preferences of individual investors and to develop a mean–variance approach

2Advanced Modelling in Financeto examining the trade-off between return (as measured by an asset’s mean return) andrisk (measured by an asset’s variance of return). This subsequently led to the developmentby Sharpe, Lintner and Treynor of the capital asset pricing model (CAPM), an equilibriummodel describing expected returns on equities. The CAPM introduced beta as a measureof diversifiable risk, arguing that the creation of portfolios served to minimise the specificrisk element of total risk (variance).The next great theoretical development was the equity option pricing formula of Blackand Scholes, which rested on the ability to create a (riskless) hedge portfolio. Contemporaneously, Merton extended the Black–Scholes formula to allow for continuous dividendsand thus also options on commodities and currencies. The derivation of the originalformula required the solving of the diffusion (or heat) equation familiar from physics, butwas subsequently encompassed by the broader risk-neutral approach to the valuation ofderivatives.1.2 ASSET PRICE ASSUMPTIONSAlthough portfolio theory was derived through individual preferences, it could also havebeen obtained by making assumptions about the distribution of asset price returns. Thestandard assumption is that equity returns follow a lognormal distribution–equivalently wecan say that equity log returns follow a normal distribution. More recently, practitionershave examined the effect of departures from strict normality (as measured by skewnessand kurtosis) and have also proposed different distributions (for example, the reciprocalgamma distribution).Although bonds have characteristics that are different from equities, the starting pointfor bond option valuation is the short interest rate. This is frequently assumed to followthe lognormal or normal distribution. The result is that familiar results grounded in theseprobability distributions can be applied throughout finance.1.3 MATHEMATICAL AND STATISTICAL PROBLEMSWithin the equities part, the mathematical problems concern optimisation. The optimisation can also include additional constraints, exemplified by Sharpe’s development ofreturns-based style analysis. Beta is estimated as the slope coefficient in a linear regression.Options are valued in the risk-neutral framework as statistical expectations. The normaldistribution of log equity prices can be approximated by an equivalent discrete binomial distribution. This binomial distribution provides the framework for calculating theexpected option value.1.4 NUMERICAL METHODSIn the context of portfolio optimisation, the optimisation involves portfolio variance, andthe numerical method needed for optimisation is quadratic programming. Style analysisalso uses quadratic programming, the quantity to be minimised being the error variance.Although not usually thought of as optimisation, linear regression chooses slope coefficients to minimise residual error. Here optimisation is of a different kind, regressionanalysis, which provides analytical formulas to calculate the beta coefficients.Turning to option valuation, the binomial tree provides the structure within whichthe risk-neutral expectation can be calculated. We highlight the importance of parameter

Introduction3choice by examining the convergence properties of three different binomial trees. Suchtrees also allow the valuation of American options, where the option can be exercised atany date prior to maturity.With European options, techniques such as Monte Carlo simulation and numericalintegration are also used. Numerical search methods, in particular the Newton–Raphsonapproach, ensure that volatilities implied by option prices in the market can be estimated.1.5 EXCEL SOLUTIONSThe spreadsheets demonstrate how Excel can be used as a prototype for building models.Within the individual spreadsheets, all the formulas in the cells can easily be examinedand we have endeavoured to incorporate all intermediate calculations in cells of theirown. The spreadsheets also allow the hallmark ability to ‘what-if’ by changing parametervalues in cells.The implementation of all the models and methods occurs twice: once in the spreadsheets and once in the VBA functions. This dual approach serves as an important checkon the accuracy of the numerical calculations.Some of the VBA procedures are macros, normally seen by others as the main purposeof VBA in Excel. However, the majority of the procedures we implement are user-definedfunctions. We demonstrate how easily these functions can be written in VBA and howthey can incorporate Excel functions, including the powerful matrix functions.The Goal Seek and Solver comma

1.5 Excel solutions 3 1.6 Topics covered 3 1.7 Related Excel workbooks 5 1.8 Comments and suggestions 5 Part One Advanced Modelling in Excel 7 2 Advanced Excel functions and procedures 9 2.1 Accessing functions in Excel 9 2.2 Mathematical functions 10 2.3 Statistical functions 12 2.3.1 Using the frequency function 12 2.3.2 Using the quartile .

Related Documents:

Modelling for Project Finance course (or individuals who have practical experience and a good understanding of Best Practice Financial Modelling) and would like to build on these skills to tackle more complex elements of Project Finance modelling. The content is well suited to Project Finance analysts and associates working for project

DWPF can provide you with tailored financial modelling and project finance training solutions. The suggested course outlines can form the basis to develop and structure tailored training courses to meet your needs. Courses Advanced Modelling for Project Finance Foundation in Project Finance Modelling Add On: P3/PPP and Infrastructure Projects .

of Managerial Finance page 2 Introduction to Managerial Finance 1 Starbucks—A Taste for Growth page 3 1.1 Finance and Business What Is Finance? 4 Major Areas and Opportunities in Finance 4 Legal Forms of Business Organization 5 Why Study Managerial Finance? Review Questions 9 1.2 The Managerial Finance Function 9 Organization of the Finance

The roles of the finance function in organisations 4. The role of ethics in the role of the finance function Ethics is the system of moral principles that examines the concept of right and wrong. Ethics underpins an organisation’s sustained value creation. The roles that the finance function performs should be carried out in an .File Size: 888KBPage Count: 10Explore furtherRole of the Finance Function in the Financial Management .www.managementstudyguide.c Roles and Responsibilities of a Finance Department in a .www.pharmapproach.comRoles and Responsibilities of a Finance Department .www.smythecpa.comTop 10 – Functions of Business Finance in an Organizationwikifinancepedia.com23 Functions and Duties of Accounting and Finance .accountantnextdoor.comRecommended to you b

5. Who can grow the largest crystal from solution? Modelling crystals 15 . 1. Modelling a salt crystal using marshmallows 2. Modelling crystals using cardboard shapes 3. Modelling diamond and graphite 4. Modelling crystal growth using people. More about crystals 21 . 1. Crystalline or plastic? 2. Make a crystal garden. Putting crystals to use .

Descriptif des cours Course Outlines 10 Catalogue des cours/ Course Catalog 2017-2018 FIN: Finance/Finance A : Actuariat/Actuarial, Insurance E : Finance d’entreprise/Corporate Finance The course liste tables and the course outlines G : Finance générale/General Finance M : Finance de marché/Market Finance S : Synthèse/Synthesis IDS: Systèmes d’Information, Sciences de la Décision et .

Introduction to Behavioral Finance CHAPTER1 What Is Behavioral Finance? Behavioral Finance: The Big Picture Standard Finance versus Behavioral Finance The Role of Behavioral Finance with Private Clients How Practical Application of Behavioral Finance Can Create a Successful Advisory Rel

10 Catalogue des cours/Course Catalog 2021-2022 FIN: Finance/Finance E : Finance d'entreprise/Corporate Finance G : Finance générale/General Finance M : Finance de marché/Market Finance S : Synthèse/Synthesis IDS: Systèmes d'Information, Sciences de la Décision et Statistiques/ Information Systems, Decision Sciences and Statistics