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:
For a given return of X standard deviations:
This framework can be set up in an Excel sheet:
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: ...