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:
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 and the strike :
As described in Chapter 5