1st Edition

# Quantitative Finance A Simulation-Based Introduction Using Excel

**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 Intuition about Uncertainty and Risk**

Introduction

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

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

How Bonds Are Traded in Canada

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

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 Excelwill 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