Advanced Modelling in Finance using Excel and VBA

1 Advanced Modelling in Financeusing Excel and VBAMary JacksonandMike StauntonJOHN WILEY & SONS, LTDC hichester New York Weinheim Brisbane Singapore TorontoCopyright 2001 by John Wiley & Sons, Ltd,Baffins Lane, Chichester,West Sussex PO19 1UD, EnglandNational01243 779777 International (C44) 1243 779777e-mail (for orders and customerservice enquiries): our Home Page on 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 theterms 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 Wiley Editorial OfficesJohn Wiley & Sons, Inc.

2 , 605 Third Avenue,New York, NY 10158-0012, USAW iley-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 129809 John 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 6 Typeset in 10/12pt Times by Laserwords Private Limited, Chennai, IndiaPrinted and bound in Great Britain by Bookcraft (Bath)

3 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 price and statistical Excel and suggestions5 Part One Advanced Modelling in Excel72 Advanced Excel functions and functions in using the frequency using the quartile using Excel s normal Setting up Data Tables with one Setting up Data Tables with two to Data Analysis and using range Goal Matrix algebra and related Introduction to

4 Transposing a Adding Multiplying Matrix Solving systems of simultaneous linear Summary of Excel s matrix functions37 Summary373 Introduction to of mastering aspects of to write VBA simple examples of VBA for writing code and executing keystrokes and editing of and data array of repeating Excel functions and VBA functions in points on between macros and the probability the efficient frontier with Solver61 Summary65 References65 Appendix 3A The Visual Basic Editor65 Stepping through a macro and using otherdebug tools68 Appendix 3 BRecording keystrokes in relative references mode694 Writing VBA user-defined simple sales commission Commission(Sales)

5 In the functions with multiple inputs for valuing arrays in value and variance functions with array variance function with array with array Excel and VBA functions in user-defined using VBA functions in user-defined and cons of developing VBA functions86 Summary87 Appendix 4A Functions illustrating array handling88 Appendix 4B Binomial tree option valuation functions89 Exercises on writing functions94 Solution notes for exercises on functions95 Part Two Equities995 Introduction to equities1016 Portfolio mean and return representation of Solver to find efficient the efficient frontier (Huang and Litzenberger sapproach)

6 Frontier risk-free and risky One combining a risk-free asset with a risky Two combining two risky Three combining a risk-free asset with a risky User-defined functions in Functions for the three generic portfolio problems in Macros in ModuleM121 Summary123 References1237 Asset single-index beta capital asset pricing covariance of related distributions such as normal and functions in Module1136 Summary138 References1388 Performance measurement and performance passive to style style style intervals for style functions in in ModuleM151 Summary152 References153 Part Three Options on Equities1559 Introduction to options on genesis of the Black Scholes Black Scholes simple one-step binomial tree with risk-neutral call Volatility and non-normal share returns165 Summary165 References16610 Binomial Introduction to binomial A simplified binomial The Jarrow and Rudd binomial The Cox.

7 Ross and Rubinstein Binomial approximations and Black Scholes Convergence of CRR binomial The Leisen and Reimer Comparison of CRR and LR American options and the CRR American User-defined functions in Module0 and Module1182 Summary183 References18411 The Black Scholes The Black Scholes Black Scholes formula in the Options on currencies and Calculating the option s greek Hedge Formal derivation of the Black Scholes User-defined functions in Module1194 Summary195 References19612 Other numerical methods for European Introduction to Monte Carlo Simulation with antithetic Simulation with quasi-random Comparing simulation Calculating greeks in Monte Carlo Numerical User-defined functions in Module1205 Summary207 References20713 Non-normal distributions and implied Black Scholes using alternative distributional Implied Adapting for skewness and The volatility User-defined

8 Functions in Module1217 Summary219 References220 Part Four Options on Bonds22114 Introduction to valuing options on The term structure of interest Cash flows for coupon bonds and yield to Binomial Black s bond option valuation Duration and Notation230 Summary230 References23015 Interest rate Vasicek s term structure Valuing European options on zero-coupon bonds, Vasicek s Valuing European options on coupon bonds, Vasicek s CIR term structure Valuing European options on zero-coupon bonds, CIR Valuing European options on coupon bonds, CIR User-defined functions in Module1239 Summary240 References241xContents16 Matching the term Trees with lognormally distributed interest Trees with normal interest The Black.

9 Derman and Toy Valuing bond options using BDT User-defined functions in Module1250 Summary252 References252 Appendix Other VBA functions253 Forecasting253 ARIMA modelling254 Splines256 Eigenvalues and eigenvectors257 References258 Index259 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 .

10 There is analmost similar lack of books that capture the true vibrant spirit of numerical methodsin is no longer true that spreadsheets such as Excel are inadequate tools in highly tech-nical 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 started as an attempt to push back the boundaries of Excel through mac