Practical C

Building a Black-Scholes Option Pricer in Excel

Building a Black-Scholes vanilla option pricing tool is one of the best ways to develop an understanding of derivatives pricing. Manipulating inputs and observing the impact on vanilla option prices is far more productive than looking at formulas in a book. This practical links closely to the material developed in Chapter 5.

Task A: Set Up a Simple Black-Scholes Options Pricer

Step 1: Set Up Spot/Rates/Time to Expiry

The first inputs to the pricer are:

  • Spot (S): the current exchange rate in a given currency pair
  • Interest rates (rCCY1 and rCCY2): continuously compounded risk free interest rates in CCY1 and CCY2 of the currency pair
  • Time to expiry (T): the time between the horizon date and expiry date measured in years

The first output is the forward to maturity T:

equation
bappcuf001

Test to see how changing the inputs impacts the forward and note what happens when rCCY1 = rCCY2.

Step 2: Set Up Vanilla Option Pricing

European vanilla option payoffs are calculated using spot at maturity practc-math-0002 and the strike practc-math-0003:

As described in Chapter 5

Get FX Derivatives Trader School now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.