O'Reilly logo

FX Derivatives Trader School by Giles Jewitt

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Practical B

Building a Numerical Integration Option Pricer in Excel

When an option payoff depends only on the spot rate at the maturity of the contract (e.g., European vanilla options) the price of the option can be calculated using the terminal spot distribution and the option payoff.

Task A: Set Up the Terminal Spot Distribution

Step 1: Set Up the Future Spots

First, future spot levels must be generated using a log-normal distribution. The inputs to the function 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
  • Volatility (σ): the volatility of the spot log returns

Within log-normal world:

equation

For a given return of X standard deviations:

equation

This framework can be set up in an Excel sheet:

bappbuf001

Under a normal distribution, a range from –5 to +5 standard deviations covers almost all possible theoretical returns. Starting with 0.1 steps, go from –5 to +5 standard deviations and calculate the return level and corresponding spot level for each standard deviation value: ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required