Our price: £45.50 (Convert currency) Normal price:£65.00, you save: £19.50 (30%)
£4.00 UK postage (for single orders)
In stock, usually dispatched within 24 hours
Or phone us on +44 (0)1730 233 870
Product code: 13742
ISBN: 0471499226,
ISBN13: 9780471499220,
270 pages, CD-Rom + hb, published by John Wiley & Sons, illustrated edition edition, 2001
Description of Advanced Modelling in Finance Using Excel and VBA
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. It specifically applies Excel and VBA to the financial markets and is packaged with a CD containing the software from the examples throughout the book.
Contents of Advanced Modelling in Finance Using Excel and VBA
Introduction
Finance insights
Asset price assumptions
Mathematical and statistical problems
Numerical Methods
Excel solutions
Topics covered
Related Excel workbooks
PART ONE: Advanced Modelling in Excel
Advanced Excel functions and procedures
Accessing functions in Excel
Mathematical functions
Statistical functions
Lookup functions
Other functions
Auditing tools
Data tables
XY charts
Access to Data Analysis and Solver
Using range names
Regression
Goal Seek
Matrix algebra and related functions
Introduction to VBA
Advantages of mastering VBA
Object-oriented aspects of VBA
Starting to write VBA macros
Elements of programming
Communicating between macros and the spreadsheet
Subroutine examples
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
Pros and cons of developing VBA functions
PART TWO: Equities
Introduction to 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 Module 1
Functions for the three generic portfolio problems in Module 1
Macros in Module M
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 Module 1
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 Module M
PART THREE: 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 Module 0 and Module 1
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 Module 1
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 Module 1
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 Module 1
PART FOUR: Options on Bonds
Introduction on 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
Notation
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 Module 1
Appendix Other VBA functions
Forecasting
ARIMA modelling
Splines
Eigenvalues and eigenvectors
References
Index
About Mary Jackson and Mike Staunton
MARY JACKSON and MIKE STAUNTON have worked together teaching spreadsheet modelling to both graduate students and practitioners since 1985. MARY JACKSON was Assistant Professor of Decision Sciences at London Business School. She is author of three previous books for John Wiley Sons: Understanding Expert Systems (1992), Advanced Spreadsheet Modelling (1988) and Creative Modelling (1985). MIKE STAUNTON is Visiting Lecturer in Numerical Methods at City University Business School and Director of the London Share Price Datbase at London Business School. He is co--author, with Elroy Dimson and Paul Marsh, of Millennium Book II: 101 Years of Investment Returns (2001) and Millennium Book: A Century of Investment Returns (2000).
