Advanced modelling in finance using excel and VBA.

Abstract

We hope that our text, Advanced Modelling in Finance, is conclusive proof that a wide range of models can now be successfully implemented using spreadsheets. The models range across the complete spectrum of finance including equities, equity options and bond options spanning developments from the early fifties to the late nineties. The models are implemented in Excel spreadsheets, complemented with functions written using the VBA language within Excel. The resulting user-defined functions provide a portable library of programs 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 emphasis on 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 1970s brought with them analytic solutions that are now straightforward to calculate. The subsequent decades have seen a growing body of developments in numerical methods. With an intelligent choice of parameters, binomial trees have assumed a central role in the more numerically-intensive calculations now required to value equity and bond options. The centre of gravity in finance now concerns the search for more efficient ways of performing such calculations rather than the theories from yesteryear. The breadth of the coverage across finance and the sophistication needed for some of the more advanced models are testament to the ability of Excel, the built-in functions contained in Excel and the real programming environment that VBA provides. This allows us 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 throughout the 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 finance has proved to be both a challenge and an opportunity. The opportunity has provided us 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 we summarise a few of these unifying insights that apply to equities, options and bonds with regard to finance, mathematical topics, numerical methods and Excel features. This is followed by a more detailed summary of the main topics covered in each chapter of the book.