Advanced Modelling in Finance Using Excel and VBA

Preface

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 ...