Disc contains spreadsheets, VBA functions, and macros used throughout the text.

Includes bibliographical references and index.

Finance insights -- Asset price assumptions -- Mathematical and statistical problems -- Numerical methods -- Excel solutions -- Topics covered -- Advanced Modelling in Excel -- Advanced Excel functions and procedures -- Accessing functions in Excel -- Mathematical functions -- Statistical functions -- Using the frequency function -- Using the quartile function -- Using Excel's normal functions -- Lookup functions -- Other functions -- Auditing tools -- Data Tables -- Setting up Data Tables with one input -- Setting up Data Tables with two inputs -- XY charts -- Access to Data Analysis and Solver -- Using range names -- Regression -- Goal Seek -- Matrix algebra and related functions -- Introduction to matrices -- Transposing a matrix -- Adding matrices -- Multiplying matrices -- Matrix inversion -- Solving systems of simultaneous linear equations -- Summary of Excel's matrix functions -- Introduction to VBA -- Advantages of mastering VBA -- Object-oriented aspects of VBA -- Starting to write VBA macros -- Some simple examples of VBA subroutines -- MsgBox for interaction -- The writing environment -- Entering code and executing macros -- Recording keystrokes and editing code -- Elements of programming -- Variables and data types -- VBA array variables -- Control structures -- Control of repeating procedures -- Using Excel functions and VBA functions in code -- General points on programming -- Communicating between macros and the spreadsheet -- Subroutine examples -- Charts -- Normal probability plot -- Generating the efficient frontier with Solver -- The Visual Basic Editor -- Stepping through a macro and using other debug tools -- Recording keystrokes in 'relative references' mode -- Writing VBA user-defined functions -- A simple sales commission function -- Creating Commission(Sales) in the spreadsheet -- Two functions with multiple inputs for valuing options -- Manipulating arrays in VBA -- Expected value and variance functions with array inputs -- Portfolio variance function with array inputs -- Functions with array output -- Using Excel and VBA functions in user-defined functions -- Using VBA functions in user-defined functions -- Add-ins -- Pros and cons of developing VBA functions -- Functions illustrating array handling -- Binomial tree option valuation functions -- Exercises on writing functions -- Solution notes for exercises on functions -- Equities -- Portfolio optimisation -- Portfolio mean and variance -- Risk--return representation of portfolios -- Using Solver to find efficient points -- Generating the efficient frontier (Huang and Litzenberger's approach) -- Constrained frontier portfolios -- Combining risk-free and risky assets -- Problem One--combining a risk-free asset with a risky asset -- Problem Two--combining two risky assets -- Problem Three--combining a risk-free asset with a risky portfolio -- User-defined functions in Module1 -- Functions for the three generic portfolio problems in Module1 -- Macros in ModuleM -- Asset pricing -- The single-index model -- Estimating beta coefficients -- The capital asset pricing model -- Variance--covariance matrices -- Value-at-Risk -- Horizon wealth -- Moments of related distributions such as normal and lognormal -- User-defined functions in Module1 -- Performance measurement and attribution -- Conventional performance measurement -- Active--passive management -- Introduction to style analysis -- Simple style analysis -- Rolling-period style analysis -- Confidence intervals for style weights -- User-defined functions in Module1 -- Macros in ModuleM -- Options on Equities -- Introduction to options on equities -- The genesis of the Black--Scholes formula -- The Black--Scholes formula -- Hedge portfolios -- Risk-neutral valuation -- A simple one-step binomial tree with risk-neutral valuation -- Put--call parity -- Dividends -- American features -- Numerical methods -- Volatility and non-normal share returns -- Binomial trees -- Introduction to binomial trees -- A simplified binomial tree -- The Jarrow and Rudd binomial tree -- The Cox, Ross and Rubinstein tree -- Binomial approximations and Black--Scholes formula -- Convergence of CRR binomial trees -- The Leisen and Reimer tree -- Comparison of CRR and LR trees -- American options and the CRR American tree -- User-defined functions in Module0 and Module1 -- The Black--Scholes formula -- The Black--Scholes formula -- Black--Scholes formula in the spreadsheet -- Options on currencies and commodities -- Calculating the option's 'greek' parameters -- Hedge portfolios -- Formal derivation of the Black--Scholes formula -- User-defined functions in Module1 -- Other numerical methods for European options -- Introduction to Monte Carlo simulation -- Simulation with antithetic variables -- Simulation with quasi-random sampling -- Comparing simulation methods -- Calculating greeks in Monte Carlo simulation -- Numerical integration -- User-defined functions in Module1 -- Non-normal distributions and implied volatility -- Black--Scholes using alternative distributional assumptions -- Implied volatility -- Adapting for skewness and kurtosis -- The volatility smile -- User-defined functions in Module1 -- Options on Bonds -- Introduction to valuing options on bonds -- The term structure of interest rates -- Cash flows for coupon bonds and yield to maturity -- Binomial trees -- Black's bond option valuation formula -- Duration and convexity -- Interest rate models -- Vasicek's term structure model -- Valuing European options on zero-coupon bonds, Vasicek's model -- Valuing European options on coupon bonds, Vasicek's model -- CIR term structure model -- Valuing European options on zero-coupon bonds, CIR model -- Valuing European options on coupon bonds, CIR model -- User-defined functions in Module1 -- Matching the term structure -- Trees with lognormally distributed interest rates -- Trees with normal interest rates -- The Black, Derman and Toye tree -- Valuing bond options using BDT trees -- User-defined functions in Module1 -- Other VBA functions -- Forecasting -- ARIMA modelling -- Splines -- Eigenvalues and eigenvectors.

This new and unique book demonstrates that Excel and VBA can play an important role in the explanation and implementation of numerical methods across finance. Advanced Modelling in Finance provides a comprehensive look at equities, options on equities and options on bonds from the early 1950s to the late 1990s. The book adopts a step-by-step approach to understanding the more sophisticated aspects of Excel macros and VBA programming, showing how these programming techniques can be used to model and manipulate financial data, as applied to equities, bonds and options. The book is essential for financial practitioners who need to develop their financial modelling skill sets as there is an increase in the need to analyse and develop ever more complex 'what if' scenarios. l Specifically applies Excel and VBA to the financial markets l Packaged with a CD containing the software from the examples throughout the book