Advanced Modelling in Finance Using Excel and VBA

Chapter 1. Introduction

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