Advanced Modelling in Finance Using Excel and VBA

Advanced Modelling in Finance Using Excel and VBA

by Mary Jackson, Mike Staunton
Released June 2001
Publisher(s): Wiley
ISBN: 9780471499220

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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.

  • Specifically applies Excel and VBA to the financial markets

  • Packaged with a CD containing the software from the examples throughout the book

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Table of contents

  1. Copyright
  2. Preface
  3. Acknowledgements
  4. 1. Introduction
    1. 1.1. FINANCE INSIGHTS
    2. 1.2. ASSET PRICE ASSUMPTIONS
    3. 1.3. MATHEMATICAL AND STATISTICAL PROBLEMS
    4. 1.4. NUMERICAL METHODS
    5. 1.5. EXCEL SOLUTIONS
    6. 1.6. TOPICS COVERED
    7. 1.7. RELATED EXCEL WORKBOOKS
    8. 1.8. COMMENTS AND SUGGESTIONS
  5. 1. Advanced Modelling in Excel
    1. 2. Advanced Excel Functions and Procedures
      1. 2.1. ACCESSING FUNCTIONS IN EXCEL
      2. 2.2. MATHEMATICAL FUNCTIONS
      3. 2.3. STATISTICAL FUNCTIONS
        1. 2.3.1. Using the Frequency Function
        2. 2.3.2. Using the Quartile Function
        3. 2.3.3. Using Excel's Normal Functions
      4. 2.4. LOOKUP FUNCTIONS
      5. 2.5. OTHER FUNCTIONS
      6. 2.6. AUDITING TOOLS
      7. 2.7. DATA TABLES
        1. 2.7.1. Setting Up Data Tables with One Input
        2. 2.7.2. Setting Up Data Tables with Two Inputs
      8. 2.8. XY CHARTS
      9. 2.9. ACCESS TO DATA ANALYSIS AND SOLVER
      10. 2.10. USING RANGE NAMES
      11. 2.11. REGRESSION
      12. 2.12. GOAL SEEK
      13. 2.13. MATRIX ALGEBRA AND RELATED FUNCTIONS
        1. 2.13.1. Introduction to Matrices
        2. 2.13.2. Transposing a Matrix
        3. 2.13.3. Adding Matrices
        4. 2.13.4. Multiplying Matrices
        5. 2.13.5. Matrix Inversion
        6. 2.13.6. Solving Systems of Simultaneous Linear Equations
        7. 2.13.7. Summary of Excel's Matrix Functions
        8. 2.13.8. SUMMARY
    2. 3. Introduction to VBA
      1. 3.1. ADVANTAGES OF MASTERING VBA
      2. 3.2. OBJECT-ORIENTED ASPECTS OF VBA
      3. 3.3. STARTING TO WRITE VBA MACROS
        1. 3.3.1. Some Simple Examples of VBA Subroutines
        2. 3.3.2. MsgBox for Interaction
        3. 3.3.3. The Writing Environment
        4. 3.3.4. Entering Code and Executing Macros
        5. 3.3.5. Recording Keystrokes and Editing Code
      4. 3.4. ELEMENTS OF PROGRAMMING
        1. 3.4.1. Variables and Data Types
        2. 3.4.2. VBA Array Variables
        3. 3.4.3. Control Structures
        4. 3.4.4. Control of Repeating Procedures
        5. 3.4.5. Using Excel Functions and VBA Functions in Code
        6. 3.4.6. General Points on Programming
      5. 3.5. COMMUNICATING BETWEEN MACROS AND THE SPREADSHEET
      6. 3.6. SUBROUTINE EXAMPLES
        1. 3.6.1. Charts
        2. 3.6.2. Normal Probability Plot
        3. 3.6.3. Generating the Efficient Frontier with Solver
        4. 3.6.4. SUMMARY
        5. 3.6.5. REFERENCES
        6. 3.6.6. APPENDIX 3A THE VISUAL BASIC EDITOR
          1. 3.6.6.1. Stepping Through a Macro and Using Other Debug Tools
        7. 3.6.7. APPENDIX 3B RECORDING KEYSTROKES IN 'RELATIVE REFERENCES' MODE
    3. 4. Writing VBA User-defined Functions
      1. 4.1. A SIMPLE SALES COMMISSION FUNCTION
      2. 4.2. CREATING COMMISSION(SALES) IN THE SPREADSHEET
      3. 4.3. TWO FUNCTIONS WITH MULTIPLE INPUTS FOR VALUING OPTIONS
      4. 4.4. MANIPULATING ARRAYS IN VBA
      5. 4.5. EXPECTED VALUE AND VARIANCE FUNCTIONS WITH ARRAY INPUTS
      6. 4.6. PORTFOLIO VARIANCE FUNCTION WITH ARRAY INPUTS
      7. 4.7. FUNCTIONS WITH ARRAY OUTPUT
      8. 4.8. USING EXCEL AND VBA FUNCTIONS IN USER-DEFINED FUNCTIONS
        1. 4.8.1. Using VBA Functions in User-Defined Functions
        2. 4.8.2. Add-Ins
      9. 4.9. PROS AND CONS OF DEVELOPING VBA FUNCTIONS
        1. 4.9.1. SUMMARY
        2. 4.9.2. APPENDIX 4A FUNCTIONS ILLUSTRATING ARRAY HANDLING
        3. 4.9.3. APPENDIX 4B BINOMIAL TREE OPTION VALUATION FUNCTIONS
        4. 4.9.4. EXERCISES ON WRITING FUNCTIONS
        5. 4.9.5. SOLUTION NOTES FOR EXERCISES ON FUNCTIONS
  6. 2. Advanced Modelling in Equities
    1. 5. Introduction to Equities
    2. 6. Portfolio Optimisation
      1. 6.1. PORTFOLIO MEAN AND VARIANCE
      2. 6.2. RISK–RETURN REPRESENTATION OF PORTFOLIOS
      3. 6.3. USING SOLVER TO FIND EFFICIENT POINTS
      4. 6.4. GENERATING THE EFFICIENT FRONTIER (HUANG AND LITZENBERGER'S APPROACH)
      5. 6.5. CONSTRAINED FRONTIER PORTFOLIOS
      6. 6.6. COMBINING RISK-FREE AND RISKY ASSETS
      7. 6.7. PROBLEM ONE–COMBINING A RISK-FREE ASSET WITH A RISKY ASSET
      8. 6.8. PROBLEM TWO–COMBINING TWO RISKY ASSETS
      9. 6.9. PROBLEM THREE—COMBINING A RISK-FREE ASSET WITH A RISKY PORTFOLIO
      10. 6.10. USER-DEFINED FUNCTIONS IN Module1
      11. 6.11. FUNCTIONS FOR THE THREE GENERIC PORTFOLIO PROBLEMS IN Module1
      12. 6.12. MACROS IN ModuleM
        1. 6.12.1. SUMMARY
        2. 6.12.2. REFERENCES
    3. 7. Asset Pricing
      1. 7.1. THE SINGLE-INDEX MODEL
      2. 7.2. ESTIMATING BETA COEFFICIENTS
      3. 7.3. THE CAPITAL ASSET PRICING MODEL
      4. 7.4. VARIANCE–COVARIANCE MATRICES
      5. 7.5. VALUE-AT-RISK
      6. 7.6. HORIZON WEALTH
      7. 7.7. MOMENTS OF RELATED DISTRIBUTIONS SUCH AS NORMAL AND LOGNORMAL
      8. 7.8. USER-DEFINED FUNCTIONS IN Module1
        1. 7.8.1. SUMMARY
        2. 7.8.2. REFERENCES
    4. 8. Performance Measurement and Attribution
      1. 8.1. CONVENTIONAL PERFORMANCE MEASUREMENT
      2. 8.2. ACTIVE–PASSIVE MANAGEMENT
      3. 8.3. INTRODUCTION TO STYLE ANALYSIS
      4. 8.4. SIMPLE STYLE ANALYSIS
      5. 8.5. ROLLING-PERIOD STYLE ANALYSIS
      6. 8.6. CONFIDENCE INTERVALS FOR STYLE WEIGHTS
      7. 8.7. USER-DEFINED FUNCTIONS IN Module1
      8. 8.8. MACROS IN ModuleM
        1. 8.8.1. SUMMARY
        2. 8.8.2. REFERENCES
  7. 3. Options on Equities
    1. 9. Introduction to Options on Equities
      1. 9.1. THE GENESIS OF THE BLACK–SCHOLES FORMULA
      2. 9.2. THE BLACK–SCHOLES FORMULA
      3. 9.3. HEDGE PORTFOLIOS
      4. 9.4. RISK-NEUTRAL VALUATION
      5. 9.5. A SIMPLE ONE-STEP BINOMIAL TREE WITH RISK-NEUTRAL VALUATION
      6. 9.6. PUT–CALL PARITY
      7. 9.7. DIVIDENDS
      8. 9.8. AMERICAN FEATURES
      9. 9.9. NUMERICAL METHODS
      10. 9.10. VOLATILITY AND NON-NORMAL SHARE RETURNS
        1. 9.10.1. SUMMARY
        2. 9.10.2. REFERENCES
    2. 10. Binomial Trees
      1. 10.1. INTRODUCTION TO BINOMIAL TREES
      2. 10.2. A SIMPLIFIED BINOMIAL TREE
      3. 10.3. THE JR BINOMIAL TREE
      4. 10.4. THE CRR TREE
      5. 10.5. BINOMIAL APPROXIMATIONS AND BLACK–SCHOLES FORMULA
      6. 10.6. CONVERGENCE OF CRR BINOMIAL TREES
      7. 10.7. THE LR TREE
      8. 10.8. COMPARISON OF CRR AND LR TREES
      9. 10.9. AMERICAN OPTIONS AND THE CRR AMERICAN TREE
      10. 10.10. USER-DEFINED FUNCTIONS IN Module0 AND Module1
        1. 10.10.1. SUMMARY
        2. 10.10.2. REFERENCES
    3. 11. The Black-Scholes Formula
      1. 11.1. THE BLACK–SCHOLES FORMULA
      2. 11.2. BLACK–SCHOLES FORMULA IN THE SPREADSHEET
      3. 11.3. OPTIONS ON CURRENCIES AND COMMODITIES
      4. 11.4. CALCULATING THE OPTION'S 'GREEK' PARAMETERS
      5. 11.5. HEDGE PORTFOLIOS
      6. 11.6. FORMAL DERIVATION OF THE BLACK–SCHOLES FORMULA
      7. 11.7. USER-DEFINED FUNCTIONS IN MODULE1
        1. 11.7.1. SUMMARY
        2. 11.7.2. REFERENCES
    4. 12. Other Numerical Methods for European Options
      1. 12.1. INTRODUCTION TO MONTE CARLO SIMULATION
      2. 12.2. SIMULATION WITH ANTITHETIC VARIABLES
      3. 12.3. SIMULATION WITH QUASI-RANDOM SAMPLING
      4. 12.4. COMPARING SIMULATION METHODS
      5. 12.5. CALCULATING GREEKS IN MONTE CARLO SIMULATION
      6. 12.6. NUMERICAL INTEGRATION
      7. 12.7. USER-DEFINED FUNCTIONS IN Module1
        1. 12.7.1. SUMMARY
        2. 12.7.2. REFERENCES
    5. 13. Non-normal Distributions and Implied Volatility
      1. 13.1. BLACK–SCHOLES USING ALTERNATIVE DISTRIBUTIONAL ASSUMPTIONS
      2. 13.2. IMPLIED VOLATILITY
      3. 13.3. ADAPTING FOR SKEWNESS AND KURTOSIS
      4. 13.4. THE VOLATILITY SMILE
      5. 13.5. USER-DEFINED FUNCTIONS IN Module1
        1. 13.5.1. SUMMARY
        2. 13.5.2. REFERENCES
  8. 4. Options on Bonds
    1. 14. Introduction to Valuing Options on Bonds
      1. 14.1. THE TERM STRUCTURE OF INTEREST RATES
      2. 14.2. CASH FLOWS FOR COUPON BONDS AND YIELD TO MATURITY
      3. 14.3. BINOMIAL TREES
      4. 14.4. BLACK'S BOND OPTION VALUATION FORMULA
      5. 14.5. DURATION AND CONVEXITY
      6. 14.6. NOTATION
        1. 14.6.1. SUMMARY
        2. 14.6.2. REFERENCES
    2. 15. Interest Rate Models
      1. 15.1. VASICEK'S TERM STRUCTURE MODEL
      2. 15.2. VALUING EUROPEAN OPTIONS ON ZERO-COUPON BONDS, VASICEK'S MODEL
      3. 15.3. VALUING EUROPEAN OPTIONS ON COUPON BONDS, VASICEK'S MODEL
      4. 15.4. CIR TERM STRUCTURE MODEL
      5. 15.5. VALUING EUROPEAN OPTIONS ON ZERO-COUPON BONDS, CIR MODEL
      6. 15.6. VALUING EUROPEAN OPTIONS ON COUPON BONDS, CIR MODEL
      7. 15.7. USER-DEFINED FUNCTIONS IN Module1
        1. 15.7.1. SUMMARY
        2. 15.7.2. REFERENCES
    3. 16. Matching the Term Structure
      1. 16.1. TREES WITH LOGNORMALLY DISTRIBUTED INTEREST RATES
      2. 16.2. TREES WITH NORMAL INTEREST RATES
      3. 16.3. THE BDT TREE
      4. 16.4. VALUING BOND OPTIONS USING BDT TREES
      5. 16.5. USER-DEFINED FUNCTIONS IN Module1
        1. 16.5.1. SUMMARY
        2. 16.5.2. REFERENCES
    4. A. Other VBA Functions
      1. A.1.
        1. A.1.1. FORECASTING
        2. A.1.2. ARIMA MODELLING
        3. A.1.3. SPLINES
        4. A.1.4. EIGENVALUES AND EIGENVECTORS
        5. A.1.5. REFERENCES

Product information

  • Title: Advanced Modelling in Finance Using Excel and VBA
  • Author(s): Mary Jackson, Mike Staunton
  • Release date: June 2001
  • Publisher(s): Wiley
  • ISBN: 9780471499220