Cumulative Normal Distribution function in Add-On Script in Google Sheets

Question

As part of an academic exercise (not university - for personal learning), I am creating a spreadsheet add-on that prices options using the Black Scholes model.

As part of the formula, I need to calculate the cumulative normal distribution function. I noticed that I can do this on the sheet itself - using the NORMDIST function. However, is there any way to get the values in the add-on itself?

Is there some way I can connect to some library (jstat) that would let me calculate the same?

Google searches have not led me to a solution as yet - will update the post in case I come across anything.

Thanks, Kedar

Answer 1

Here are a few of the existing questions about calling spreadsheet built-ins from Google Apps Script.

• Calling Google Spreadsheet functions from Google Apps Scripts
• Using built-in spreadsheet functions in a script
• https://stackoverflow.com/questions/30715753/google-apps-use-built-in-function-in-a-custom-function
• google apps script: built-in spreadsheet functions fail in script - wrong syntax?
• How to use built-in spreadsheet functions using GAS
• How to use native spreadsheet functions in google apps script?
• Possible to use Google Spreadsheet functions in Google App Script?
• Google Spreadsheet custom function made of built in spreadsheet functions
• Using built-in spreadsheet functions in a script
• Writing spreadsheet function via code
• Use the Spreadsheet standard functions in Scripts?
• How to evaluate a spreadsheet formula within a custom function?
• Can the google spreadsheet 'query' function be used in google apps script?

So, apparently StackOverflow is more effective for searching than Google is! ;^)

What you'll learn from that collection of answers...

• You cannot call spreadsheet functions from scripts.
• The feature has been requested multiple times, and Google says "no".
• You can use javascript library functions, providing that the library is compatible with Google Apps Script. (e.g. no reliance on DOM)

Answer 2

You'll find the formulas you need at the following links:

• https://excelatfinance.com/xlf17/xlf-black-scholes-google-sheets.php
• https://excelatfinance.com/xlf19/xlf-norm-dist-pdf.php

In case those links die, here's the Google Apps Script code:

/* ==================================================
  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)))) ;
}


/* ================================================== */
/* The Normal distribution probability density function (PDF)
   for the specified mean and specified standard deviation: */
function xlfNormalPDF1a (x, mu, sigma) {
    var num = Math.exp(-Math.pow((x - mu), 2) / (2 * Math.pow(sigma, 2)))
    var denom = sigma * Math.sqrt(2 * Math.PI)
    return num / denom
}


function xlfNormalPDF1b (x, mu, sigma) {
  var num = Math.exp(- 1 / 2 * Math.pow((x - mu) / sigma, 2))
  var denom = sigma * Math.sqrt(2 * Math.PI)
  return num / denom
}


/* ================================================== */
/* The Normal distribution probability density function (PDF)
   for the standard normal distribution: */
function xlfNormalSdistPDF (x) {
    var num = Math.exp(-1 / 2 * x * x )
    var denom = Math.sqrt(2 * Math.PI)
    return num / denom
}