Advanced modelling in finance using excel and VBA.

• Main Author: Mary Jackson, Mike Staunton.
• Format: Book
• Language: English
• Published: John Wiley & Sons 2020
• Subjects: Finance.
• Online Access: http://dspace.uniten.edu.my/jspui/handle/123456789/17475

When asked why they tackled Mount Everest, climbers typically reply “Because it was there”. Our motivation for writing Advanced Modelling in Finance is for exactly the opposite reason. There were then, and still are now, almost no books that give due prominence to and explanation of the use of VBA functions within Excel. There is an almost similar lack of books that capture the true vibrant spirit of numerical methods in 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. With efficient code and VBA functions, calculations that were once the preserve of dedicated packages and languages can now be done on a modern PC in Excel within seconds, if not fractions of a second. By employing Excel and VBA, our purpose is to try to bring clarity to an area that was previously covered with black boxes. What started as an attempt to push back the boundaries of Excel through macros turned into a full-scale expedition into the VBA language within Excel and then developed from equities, through options and finally to cover bonds. Along the way we learned scores of new Excel skills and a much greater understanding of the numerical methods implemented across 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 on equities formed the basis for an executive course on ‘Equity Portfolio Management’ run annually by the International Centre for Money and Banking in Geneva. The parts on options 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 reach of 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-active stance when using the book–namely by taking advantage of the inherent ‘what-if’ quality of the spreadsheets and by looking at and using the code forming the VBA user-defined functions. Since we assume for the most part that asset returns are lognormal and therefore use binomial trees as a central numerical method, our explanations can be based on familiar results from probability and statistics. Comprehension is helped by the use of a common notation throughout, and transparency by the availability of complete solutions in both Excel and VBA forms.