Black-Scholes option pricing in Google Sheets and GAS

In this module


0. Preliminary reading

If you are not up to speed on the Black-Scholes equations, then take a look at the Excel and VBA version link


1. The Black-Scholes model in Google Sheets

Example: parameters - the stock price at time 0, six months before expiration date of the option is $42.00, option exercise price is $40.00, the rate of interest on a government bond with 6 months to expiration is 5%, and the annual volatility of the underlying stock is 20%.


Calculation of the call price can be completed as a 5 step process. Step 1. d1; 2. d2; 3. N(d1); 4. N(d2); and step 5, C. The value for d1 and d2 are shown in rows 12 and 13 of figure 1. The probabilities for \(N(\cdot)\) are estimated with the Google Docs statistical function: NORMSDIST. The theoretical price of the call option is $4.08 (Figure 1 row 18), and the theoretical price of the put option is $1.09 (Figure 1 row 19).


Syntax - Google Docs function NORMSDIST(x), where x is the probability value.


The Google sheets example with built-in functions (figure 1 - down to row 20) is almost identical to its implementation in Excel.


xlf alert Loading... - the Custom functions section at row 22 may show a Loading... or #NAME? error (figure 1 Google Sheets). The GAS code in this module, is bound to the container (the Google Sheet). Therefore, scripts are only available to users who have permission to edit.

Access to bound scripts: "Only users who have permission to edit a container can run its bound script. Collaborators who have only view access cannot open the script editor, although if they make a copy of the parent file, they become the owner of the copy and will be able to see and run a copy of the script." (Source: https://developers.google.com/apps-script/guides/bound)

Fig 1: Google Sheets (read only): - Google Sheets version of Black and Scholes' model for a European type option on a non dividend paying stock. Includes custom JavaScript functions (JS) in rows 29 to 32 - discussed in section 2.

View the worksheet in Google Sheets link

Google Sheets vs Excel Web App


Browser source code cf. code file.



HTML Code 1: rendering of Google Sheets displays the Sheet as a HTML table. 

xlf-black-scholes-google
HTML: lines 4 to 143 CSS style, lines 144 to 189 JavaScript, lines 197 to 319 table class="waffle", lines 321 to 368 JavaScript [x scroll yellow content (visible on scroll down), y scroll mauve content]

2. The Black-Scholes model in JavaScript


The coding language for Google sheets is named Google Apps Script (GAS), and is an implementation of the JavaScript language. In this example, separate JavaScript function procedures are developed for the call option (code 2) and put option (code 3) equations. Google Sheets functions are not available in the Google Apps environment. There is no equivalent to the Excel VBA Application.WorksheetFunction property. The NORMSDIST WS function, requires a custom function to be coded. It is named xlfNormSDist in this example (code 5).


2.1 xlfBSCall



JavaScript Code 2: Function xlfBSCall returns the call price for a European option on a non dividend paying stock 

/* ==================================================
   The Black and Scholes (1973) formula for an option on a non dividend stock
   OptType default "c" for Call */

function xlfBSCall(Stock, Exercise, Rate, Sigma, Time) {
    var d1;
    var d2;
    d1 = (Math.log(Stock / Exercise) + (Rate + (Math.pow(Sigma, 2)) / 2) * Time) / (Sigma * Math.sqrt(Time));
    d2 = (Math.log(Stock / Exercise) + (Rate - (Math.pow(Sigma, 2)) / 2) * Time) / (Sigma * Math.sqrt(Time));
    return Stock * xlfNormSDist(d1) - Exercise * Math.exp(-Rate * Time) * xlfNormSDist(d2);
}

About code 2

  1. Line 5 to line 11: Declares a JavaScript function procedure, in the form function name(arg1, agr2, ...). The function code is between the opening "{" bracket (line 5) and the closing "}" (line 11)
  2. Line 6 and line 7: Declare variables d1 and d2.
  3. Line 9: Call JavaScript Math functions. Math.log(x) returns the natural logarithm of x, and Math.sqrt(x) returns the square root of x. JavaScript has four arithmetic operators ( + - * /). JavaScript does not recognize the spreadsheet ^ symbol as exponentiation, so the function Math.pow(x,y) returns the value of x to the power y, that is, sigma2.
  4. Line 10: Is a custom function, xlfNormSDist (described in code 5) to return the cumulative normal density

2.2 xlfBSPut



JavaScript Code 3: Function xlfBSPut returns the put price for a European option on a non dividend paying stock 
/* ================================================== */
/* Black Scholes call option */

function xlfBSPut(Stock, Exercise, Rate, Sigma, Time) {
    var d1;
    var d2;
    d1 = (Math.log(Stock / Exercise) + (Rate + (Math.pow(Sigma, 2)) / 2) * Time) / (Sigma * Math.sqrt(Time));
    d2 = (Math.log(Stock / Exercise) + (Rate - (Math.pow(Sigma, 2)) / 2) * Time) / (Sigma * Math.sqrt(Time));
    return Exercise * Math.exp(-Rate * Time) * xlfNormSDist(-d2) - Stock * xlfNormSDist(-d1);
}

About code 3

  1. Line 15 to line 24: Declares a JavaScript function procedure, in the form function name(arg1, agr2, ...). The code 3 logic is identical to code 2 except for the Put value in line 23

2.3 xlfBSOption



JavaScript Code 4: Macro xlfBSOption with OptType argument. 
/* ==================================================
   The Black and Scholes (1973) formula for an option on a non dividend stock
   OptType default "c" for Call */

function xlfBSOption(Stock, Exercise, Rate, Sigma, Time, OptType) {

  var d1, d2;
  d1 = (Math.log(Stock / Exercise) + (Rate + Sigma * Sigma / 2.0) * Time) / (Sigma * Math.sqrt(Time));
  d2 = d1 - Sigma * Math.sqrt(Time);

  if (OptType == "c")
    return Stock * xlfNormSDist(d1) - Exercise * Math.exp(-Rate * Time) * xlfNormSDist(d2);
  else
    return Exercise * Math.exp(-Rate * Time) * xlfNormSDist(-d2) - Stock * xlfNormSDist(-d1);
}

2.4 xlfNormSDist


The Google Sheets NORMDIST cannot be called from the GAS module, so the approximation to the cumulative normal distribution function \(N(x)\) from Abramowitz (1972) equations 26.2.1 p931, 26.2.17 p932; and Bellalah (2008, Appendix 6, p126) is provided in code 5.


The \(N(x)\) approximation is:

$$ N(x) = \begin{cases} 1-n(x) \times k \times (a1 + k \times (a2 + k \times (a3 + k \times (a4 + k \times a5)))), & \text{when $x \ge 0$} \\ 1-n(-x) & \text{when $x \lt 0$} \end{cases}$$

where

$$n(x) = \frac{1} { \sqrt 2 \pi} e^{-x^2 / 2}, \quad \text{and} \quad k = \frac {1}{1 + ax} $$

and a1 to a5 are assigned values in code 5.



JavaScript Code 5: Function xlfNormSDist is equivalent to the WS NORMSDIST function
This approximation is based on Abramowitz & Stegun (1964) eq 26.2.17 p126 
/* ==================================================
  The cumulative Normal distribution function: */

function xlfNormSDist(x) {

// constants
  var a = 0.2316419;
  var a1 =  0.31938153;
  var a2 = -0.356563782;
  var a3 =  1.781477937;
  var a4 = -1.821255978;
  var a5 =  1.330274429;

if(x<0.0)
  return 1-xlfNormSDist(-x);
else
  var k = 1.0 / (1.0 + a * x);
  return 1.0 - Math.exp(-x * x / 2.0)/ Math.sqrt(2 * Math.PI) * k
  * (a1 + k * (a2 + k * (a3 + k * (a4 + k * a5)))) ;
}

Add a JavaScript module to Google sheets

  1. Open the Google Sheets workbook
  2. On the Menu Bar, select Tools > Script editor...
  3. Click File > New > Script File
  4. Then Add a name: xlfbscode in the dialog box, click OK
  5. This will add a tab named xlfbscode
  6. Delete the contents of the new javascript function MyFunction place holder
  7. Copy and paste JavaScript file available here with a .txt extension [3 KB]
  8. Click the Save icon

 

References

Abramowitz M, and I Stegun, (1972), Handbook of Mathematical Functions with Formulas, Graphs, and Mathematical Tables, National Bureau of Standards - Applied Mathematics Series #55.

Bellalah M, (2008), Exotic Derivatives and Risk: Theory, Extensions and Applications, World Scientific.

Mcpherson B, (2016), Going GAS, from VBA to Google Apps Script, O'Reilly.


Resources