1st Edition

Quantitative Finance A Simulation-Based Introduction Using Excel

By Matt Davison Copyright 2014
    532 Pages 71 B/W Illustrations
    by Chapman & Hall

    Teach Your Students How to Become Successful Working Quants

    Quantitative Finance: A Simulation-Based Introduction Using Excel provides an introduction to financial mathematics for students in applied mathematics, financial engineering, actuarial science, and business administration. The text not only enables students to practice with the basic techniques of financial mathematics, but it also helps them gain significant intuition about what the techniques mean, how they work, and what happens when they stop working.

    After introducing risk, return, decision making under uncertainty, and traditional discounted cash flow project analysis, the book covers mortgages, bonds, and annuities using a blend of Excel simulation and difference equation or algebraic formalism. It then looks at how interest rate markets work and how to model bond prices before addressing mean variance portfolio optimization, the capital asset pricing model, options, and value at risk (VaR). The author next focuses on binomial model tools for pricing options and the analysis of discrete random walks. He also introduces stochastic calculus in a nonrigorous way and explains how to simulate geometric Brownian motion. The text proceeds to thoroughly discuss options pricing, mostly in continuous time. It concludes with chapters on stochastic models of the yield curve and incomplete markets using simple discrete models.

    Accessible to students with a relatively modest level of mathematical background, this book will guide your students in becoming successful quants. It uses both hand calculations and Excel spreadsheets to analyze plenty of examples from simple bond portfolios. The spreadsheets are available on the book’s CRC Press web page.


    Intuition about Uncertainty and Risk

    Individual Attitudes toward Risk
    The St. Petersburg Paradox
    Looking Forward to Chapter 3

    The Classical Approach to Decision Making under Uncertainty
    Map to the Future

    Valuing Investment Opportunities: The Discounted Cash Flow Method
    Discounted Cash Flow Method for Evaluating Investment Opportunities

    Repaying Loans Over Time
    Repaying a Loan over Time: Excel
    Repaying a Loan over Time: Mathematics
    First-Order Difference Equations
    Solving the Loan Repayment Difference Equation
    More Examples of Using Difference Equations to Find Loan Payments
    Writing the Difference Equation in Forward versus Backward Forms
    Bridges to the Future

    Bond Pricing with Default: Using Simulations
    Modeling a Defaultable Bond or Loan
    Financial Insights
    Simulating Loan Portfolios
    What Happens if There Are a Large Number of Independent Loans?
    Bridge to the Future

    Bond Pricing with Default: Using Difference Equations
    Risky Bonds
    Using Difference Equations to Find C
    Exploring the Insights Arising from Equation 7.5
    Determining Recovery Rates
    Determining the Probability of Default
    A Bridge to the Future

    Difference Equations for Life Annuities

    Tranching and Collateralized Debt Obligations
    Collateralized Debt Obligations
    Tranched Portfolios
    The Detailed Calculation
    Correlation of Two Identical Bonds

    Bond CDOs: More Than Two Bonds, Correlation, and Simulation
    Using an Excel Simulation to Analyze CDOs with More Than Two Bonds
    Collateralized Debt Obligations: An Example of Financial Engineering
    The Binomial Simplification
    Correlated Defaults

    Fundamentals of Fixed Income Markets
    What Are Bonds?
    Getting Down to Quantitative Details
    Simplest Bond Pricing Equation
    How Bonds Are Traded in Canada
    Clean and Dirty Bond Prices
    Conclusion and Bridge to the Next

    Yield Curves and Bond Risk Measures
    Constructing Yield Curves from Bond Prices
    Bond Price Sensitivities to the Yield

    Forward Rates
    Relationships between Forward Rates and the Yield Curve
    Yield Curves, Discount Factors, and Forward Rates
    Interpreting Forward Curves

    Modeling Stock Prices
    What Are Stocks?
    Simple Statistical Analysis of Real Stock Data

    Mean Variance Portfolio Optimization
    Selecting Portfolios
    CAPM and Markowitz

    A Qualitative Introduction to Options
    Stock Option Definitions
    Uses for Put and Call Options
    Qualitative Behavior of Puts and Calls

    Value at Risk (VaR)
    Introduction to Value at Risk
    Pitfalls of VaR

    Pricing Options Using Binomial Trees
    Binomia l Model
    Single-Period Binomial Tree Model for Option Pricing
    Extending the Binomial Model to Multiple Time Steps
    Multiple-Step Binomial Trees

    Random Walks
    Deriving the Diffusion Partial Differential Equation

    Basic Stochastic Calculus
    Basics of Stochastic Calculus
    Stochastic Integration by Examples
    Conclusions and Bridge to Next Chapters

    Simulating Geometric Brownian Motion
    Simulating GBM Stock Prices at a Single Future Time
    Simulating a Time Sequence of GBM Stock Prices

    Black Scholes PDE for Pricing Options in Continuous Time
    Hedging Argument
    Call Price Solution of the Black Scholes Equation
    Why Short Selling Is So Dangerous
    Summary and Bridge to the Future

    Solving the Black Scholes PDE
    Solving the Black Scholes Partial PDE for a European Call
    General European Option Payoffs: Risk-Neutral Pricing

    Pricing Put Options Using Put Call Parity

    Some Approximate Values of the Black Scholes Call Formula
    Approximate Call Formulas at-the-Money
    Approximate Call Values Near-the-Money
    Approximate Call Values Far-from-the-Money

    Simulating Delta Hedging
    How Does Delta Hedging Really Work?
    Understanding the Results of the Delta Hedging Process
    The Impact of Transaction Costs
    A Hedgers Perspective on Option Gamma or, "Big Gamma" = "Big Money"
    Bridge to the Future

    Black Scholes with Dividends
    Modeling Dividends
    The Black Scholes PDE for the Continuously Paid Dividend Case
    Pricing the Prepaid Forward on a Continuous Dividend Paying Stock
    More Complicated Derivatives on Underlying Paying Continuous Dividends

    American Options
    Introduction and Binomial Pricing
    American Puts
    American Calls

    Pricing the Perpetual American Put and Call
    Perpetual Options: Underlying Pays No Dividends
    Basic Perpetual American Call
    Perpetual American Call/Put Model with Dividends
    The Perpetual American Call, Continuous Dividends

    Options on Multiple Underlying Assets
    Exchange Options

    Interest Rate Models
    Setting the Stage for Stochastic Interest Rate Models
    Pricing When You CANNOT Trade the Underlying Asset
    Hedging Bonds in Continuous Time
    Solving the Bond Pricing PDE
    Vasicek Model

    Incomplete Markets
    Introduction to Incomplete Markets
    Trying to Hedge Options on a Trinomial Tree
    Minimum Variance Hedging of a European Option with Default
    Binomial Tree Model with Default Risk

    Appendix 1: Probability Theory Basics—Experiments, Sample Outcomes, Events, and Sample Space
    Appendix 2: Proof of De Moivre–Laplace Theorem Using MGF
    Appendix 3: Naming Variables in Excel
    Appendix 4: Building VBA Macros from Excel


    Exercises and References appear at the end of each chapter.


    Matt Davison is an associate dean (administration) in the Faculty of Science and a professor of statistical and actuarial sciences at the University of Western Ontario. Dr. Davison holds the Canada Research Chair in Quantitative Finance. 

    "With this book, Matt Davison succeeds where many have failed: it provides a genuinely understandable introduction to quantitative finance that remains true to the excitement experienced by both practitioners and researchers in the field. The order to chapters is carefully selected to build up intuition from familiar financial products to more esoteric ones so that by the time stock prices are introduced in Chapter 14 they can be understood with confidence by the reader. Mathematical techniques also go hand-in-hand with the finance so concepts such as binomial trees and random walks don't look like out-of-place aberrations. Better still, simulations on easily constructed spreadsheets give students exposure to the `real deal’ of the subject. Definitely recommended—the chapters on American options alone are worth the cover price."
    —Matheus Grasselli, Deputy Director, The Fields Institute for Research in Mathematical Sciences

    "This financial mathematics textbook is exceptional in many ways. It is an ideal introduction to the essential mathematical models used in modern finance for those who are more interested in gaining intuitive understanding of such models and how to use them in practice than learning all the technical intricacies that underpin them. The author still explains in a rigorous way all the necessary mathematics, but he does this in a very accessible and often entertaining way.
    By combining financial theory and computational methods implemented in Excel, the book provides the reader with an integrated approach to important problems like portfolio selection, pricing and hedging of contingent claims in different asset classes, and risk management. Diverse, carefully selected, and meticulously explained examples form an integral part of the book. They not only illustrate well difficult abstract mathematical concepts but incrementally introduce new programming techniques and give hands-on experience.
    The author put a great deal of thought into the preparation of this textbook, but what is equally important is that his passion and enthusiasm for the subject matter is clearly visible throughout the book."
    —Adam Kolkiewicz, Associate Professor, Department of Statistics and Actuarial Science, University of Waterloo

    "For those starting out in the wide field of financial mathematics, this book will be an ideal introduction. It combines sound theoretical discussion of the most relevant topics with a guide to hands-on implementation. It provides detailed explanations of fundamental concepts, but always keeps the technical details at a minimum. The reader is gently lead through complicated ideas and is able to understand the underlying financial concepts. The construction of models is explained and all relevant calculations are presented in a carefully structured way. The beginner will learn a great deal on how to come up with such models and—what is even more important—is shown where the shortcomings of models are. The Excel implementations help the reader comprehend how to use the models. All implementations are explained in great detail and can also be used to better understand the possibilities (and shortcomings) of Excel as a pricing and simulation tool. The interplay of finance, insurance and mathematics (probability and statistics) is highlighted so that the reader can learn where these areas fruitfully interact.
    Matt Davison has given his book an excellent structure. Using simple examples in earlier chapters he provides intuition of the relevant concepts, which are put into a mathematically rigorous form in the later chapters. He displays very detailed derivations of the most important pricing formulas and immediately brings them to life with relevant examples. The reader is challenged by many exercises and is guided to independent study by suggestions for further reading from the literature. The appendices provide the relevant concepts from probability theory and a guide to Excel.
    This is one of the most thoughtful introductory books on financial mathematics I have seen. Every reader who uses it as a starting point of the subject will quickly gain access to some of the most important areas of financial mathematics. Experienced experts will appreciate the intuitive and mathematically correct treatment of many fundamental concepts."
    —Professor Rüdiger Kiesel, Chair for Energy Trading and Finance, University Duisburg-Essen

    "This is a very interesting book with a creative perspective on introductory financial mathematics. While including a thorough discussion on options and their hedging, this text also contains much interesting material on credit risk, risk management, and structured products. The mathematical level has been kept intentionally as basic as possible, making the book accessible to non-mathematical readers. But even mathematically oriented students will find interesting discussions here that will help them build their financial intuition and see how similar tools apply across various areas of financial mathematics as well as mathematical economics. The spreadsheets provided with the text are a very useful addition. I recommend this book to students and professionals."
    —Zengjing Chen, School of Mathematics, Shandong University

    "This wonderful book is a comprehensive learning-by-doing tool for pricing and hedging financial instruments. Readers are motivated to learn practical ways of valuing bonds, stocks, forwards and options, making the material of financial mathematics in corporate finance, investments and derivatives easily accessible. I strongly recommend this book to finance practitioners and quantitatively minded students. The book gives learning-friendly and clear instructions for using Excel spreadsheets to solve financial problems, ranging from basic net present value to highly sophisticated options pricing. The advanced material introduces the toolkit for quantitative analysis of finance, emphasizing intuition rather than mathematical rigor. Pricing and hedging concepts are made easier by Davison’s enlightening discussions on the fundamental ideas (and their limits) in the models, and readers benefit from the practical orientation throughout."
    —Stein-Erik Fleten, Norwegian University of Science and Technology

    "Most books on quantitative finance are only accessible to readers having a significant mathematical background and the focus is mainly on the mathematics of finance, rather than describing important financial concepts. In this book, Matt Davison chooses to target a larger audience, and the emphasis is mainly on financial intuition while mathematics is used to support the important concepts. It is interesting to note that almost all chapters are accessible to readers with a limited mathematical background.
    Starting from the fundamental concepts of risk and returns, readers are guided through a wide variety of quantitative finance topics: bonds and credit instruments like collateralized debt obligations (CDO), portfolio optimization, option pricing, and interest rates models. Some chapters are more technical but they are not essential for the overall comprehension. The book provides sufficient motivation and examples to enable readers to understand the core principles of financial mathematics. The main aim of the author is to develop intuition about financial markets through a series of questions, examples, and numerical computations. To learn the basics in an intuitive way, readers can also use spreadsheets to help visualize possible outcomes and show the limitations of the models by generating plausible scenarios through simulation. Again, readers do not have to be specialists in computer science; only standard knowledge of spreadsheets is needed. From an academic point of view, another interesting aspect of the book is that it can be used in a variety of courses in quantitative finance, from introductory to advanced, depending on the selection of chapters. Everybody can learn from this book; if not from the theory, it can be from finding other ways to present some important topics to a non-specialized audience."
    —Bruno Rémillard, Professor of Financial Engineering, HEC Montréal

    "Professor Davison is among the clearest thinkers I’ve ever met. His writing method makes it easy for the reader to enjoy the subject matter. As you turn the pages, the book becomes almost a conversation with the author. An important contribution, I found, is that it reminds readers to check, in a systematic manner, the soundness of the model (e.g., by working through limiting cases and by conducting simulations in Excel). The book provides well-balanced and relevant coverage of quantitative finance, accessible and insightful to quants and non-quants. For those building financial models for a living, I anticipate that Quantitative Finance: A Simulation-Based Introduction Using Excel will be one of those books that you do not read once but, instead, turn to often as a key reference document. I highly recommend this book for anyone looking for a direct and practical approach to applied work in this topic."
    —Alejandro Garcia Cañizares, Research Specialist, Office of the Superintendent of Financial Institutions Canada