1st Edition

# Quantitative Finance A Simulation-Based Introduction Using Excel

532 Pages 71 B/W Illustrations
by Chapman & Hall

532 Pages
by Chapman & Hall

Also available as eBook on:

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.

Introduction

Introduction
Individual Attitudes toward Risk
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
Conclusions

Repaying Loans Over Time
Introduction
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
Introduction

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

Bond CDOs: More Than Two Bonds, Correlation, and Simulation
Introduction
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
Clean and Dirty Bond Prices
Conclusion and Bridge to the Next

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

Forward Rates
Introduction
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
Summary

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

Random Walks
Introduction
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
Summary

Black Scholes PDE for Pricing Options in Continuous Time
Introduction
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
Summary

Pricing Put Options Using Put Call Parity
Summary

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
Introduction
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
Summary

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

Index

Exercises and References appear at the end of each chapter.

### Biography

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