**Preface ****Acknowledgements**

**1 Introduction**

1.1 Finance insights

1.2 Asset price assumptions

1.3 Mathematical and statistical problems

1.4 Numerical methods

1.5 Excel solutions

1.6 Topics covered

1.7 Related Excel workbooks

1.8 Comments and suggestions

**Part One Advanced Modelling in Excel**

**2 Advanced Excel functions and procedures**

2.1 Accessing functions in Excel

2.2 Mathematical functions

2.3 Statistical functions

2.3.1 Using the frequency function

2.3.2 Using the quartile function

2.3.3 Using Excel's normal functions

2.4 Lookup functions

2.5 Other functions

2.6 Auditing tools

2.7 Data Tables

2.7.1 Setting up Data Tables with one input

2.7.2 Setting up Data Tables with two inputs

2.8 XY charts

2.9 Access to Data Analysis and Solver

2.10 Using range names

2.11 Regression

2.12 Goal Seek

2.13 Matrix algebra and related functions

2.13.1 Introduction to matrices

2.13.2 Transposing a matrix

2.13.3 Adding matrices

2.13.4 Multiplying matrices

2.13.5 Matrix inversion

2.13.6 Solving systems of simultaneous linear equations

2.13.7 Summary of Excel's matrix functions Summary

**3 Introduction to VBA**

3.1 Advantages of mastering VBA

3.2 Object-oriented aspects of VBA

3.3 Starting to write VBA macros

3.3.1 Some simple examples of VBA subroutines

3.3.2 MsgBox for interaction

3.3.3 The writing environment

3.3.4 Entering code and executing macros

3.3.5 Recording keystrokes and editing code

3.4 Elements of programming

3.4.1 Variables and data types

3.4.2 VBA array variables

3.4.3 Control structures

3.4.4 Control of repeating procedures

3.4.5 Using Excel functions and VBA functions in code

3.4.6 General points on programming

3.5 Communicating between macros and the spreadsheet

3.6 Subroutine examples

3.6.1 Charts

3.6.2 Normal probability plot

3.6.3 Generating the efficient frontier with Solver

Summary

References

Appendix ЗА The Visual Basic Editor

Stepping through a macro and using other

debug tools Appendix 3B Recording keystrokes in 'relative references' mode

**4 Writing VBA user-defined functions**

4.1 A simple sales commission function

4.2 Creating Commission(Sales) in the spreadsheet

4.3 Two functions with multiple inputs for valuing options

4.4 Manipulating arrays in VBA

4.5 Expected value and variance functions with array inputs

4.6 Portfolio variance function with array inputs

4.7 Functions with array output

4.8 Using Excel and VBA functions in user-defined functions

4.8.1 Using VBA functions in user-defined functions

4.8.2 Add-ins

4.9 Pros and cons of developing VBA functions Summary

Appendix 4A Functions illustrating array handling

Appendix 4B Binomial tree option valuation functions Exercises on writing functions Solution notes for exercises on functions

**Part Two Equities**

**5 Introduction to equities**

**6 Portfolio optimisation**

6.1 Portfolio mean and variance

6.2 Risk-return representation of portfolios

6.3 Using Solver to find efficient points

6.4 Generating the efficient frontier (Huang and Litzenberger's approach)

6.5 Constrained frontier portfolios

6.6 Combining risk-free and risky assets

6.7 Problem One-combining a risk-free asset with a risky asset

6.8 Problem Two-combining two risky assets

6.9 Problem Three-combining a risk-free asset with a risky portfolio

6.10 User-defined functions in Modulel

6.11 Functions for the three generic portfolio problems in Modulel

6.12 Macros in ModuleM Summary References

**7 Asset pricing**

7.1 The single-index model

7.2 Estimating beta coefficients

7.3 The capital asset pricing model

7.4 Variance-covariance matrices

7.5 Value-at-Risk

7.6 Horizon wealth

7.7 Moments of related distributions such as normal and lognormal

7.8 User-defined functions in Modulel Summary References

**8 Performance measurement and attribution**

8.1 Conventional performance measurement

8.2 Active-passive management

8.3 Introduction to style analysis

8.4 Simple style analysis

8.5 Rolling-period style analysis

8.6 Confidence intervals for style weights

8.7 User-defined functions in Modulel

8.8 Macros in ModuleM Summary References

**Part Three Options on Equities**

**9 Introduction to options on equities**

9.1 The genesis of the Black-Scholes formula

9.2 The Black-Scholes formula

9.3 Hedge portfolios

9.4 Risk-neutral valuation

9.5 A simple one-step binomial tree with risk-neutral valuation

9.6 Put-call parity

9.7 Dividends

9.8 American features

9.9 Numerical methods

9.10 Volatility and non-normal share returns

Summary

References

**10 Binomial trees**

10.1 Introduction to binomial trees

10.2 A simplified binomial tree

10.3 The Jarrow and Rudd binomial tree

10.4 The Cox, Ross and Rubinstein tree

10.5 Binomial approximations and Black-Scholes formula

10.6 Convergence of CRR binomial trees

10.7 The Leisen and Reimer tree

10.8 Comparison of CRR and LR trees

10.9 American options and the CRR American tree

10.10 User-defined functions in ModuleO and Modulel

Summary

References

**11 The Black-Scholes formula**

11.1 The Black-Scholes formula

11.2 Black-Scholes formula in the spreadsheet

11.3 Options on currencies and commodities

11.4 Calculating the option's 'greek' parameters

11.5 Hedge portfolios

11.6 Formal derivation of the Black-Scholes formula

11.7 User-defined functions in Module 1 Summary References

**12 Other numerical methods for European options**

12.1 Introduction to Monte Carlo simulation

12.2 Simulation with antithetic variables

12.3 Simulation with quasi-random sampling

12.4 Comparing simulation methods

12.5 Calculating greeks in Monte Carlo simulation

12.6 Numerical integration

12.7 User-defined functions in Module 1 Summary References

**13 Non-normal distributions and implied volatility**

13.1 Black-Scholes using alternative distributional assumptions

13.2 Implied volatility

13.3 Adapting for skewness and kurtosis

13.4 The volatility smile

13.5 User-defined functions in Module 1 Summary References

**Part Four Options on Bonds**

**14 Introduction to valuing options on bonds**

14.1 The term structure of interest rates

14.2 Cash flows for coupon bonds and yield to maturity

14.3 Binomial trees

14.4 Black's bond option valuation formula

14.5 Duration and convexity

14.6 Notation Summary References

**15 Interest rate models**

15.1 Vasicek's term structure model

15.2 Valuing European options on zero-coupon bonds, Vasicek's model

15.3 Valuing European options on coupon bonds, Vasicek's model

15.4 CIR term structure model

15.5 Valuing European options on zero-coupon bonds, CIR model

15.6 Valuing European options on coupon bonds, CIR model

15.7 User-defined functions in Module 1 Summary References

**16 Matching the term structure**

16.1 Trees with lognormally distributed interest rates

16.2 Trees with normal interest rates

16.3 The Black, Derman and Toye tree

16.4 Valuing bond options using BDT trees

16.5 User-defined functions in Module 1 Summary References

**Appendix Other VBA functions**

Forecasting

ARIMA modelling

Splines

Eigenvalues and eigenvectors

References

**Index**