How to calculate Black-Scholes using Google Sheets?

Question

I'd like to calculate Black-Scholes using Google Sheets based on this formula: https://www.erieri.com/blackscholes.

Here are the parameters I'm using in the form:

Stock price = 60.89
Option strike price = 40
Maturity = .27
Risk-free rate = .02
Volatility = .59

European Put result is 0.5631.

The Black-Scholes formula can be found on the same page below the form in the definitions section.

Here's the formula I'm using in Google Sheets:

d1 = ((ln(60.89/40)+((0.02*0.5*0.59^2)*0.27))/(0.59*SQRT(0.27))) = 1.3766
d2 = d1-0.59*SQRT(0.27) = 1.06709
C = 60.89*NORMDIST(d1)-40*2.718^(0.02*0.27)*NORMDIST(d2) = error

Here's the error details:

Error
Wrong number of arguments to NORMDIST. Expected 4 arguments, but got 1 arguments.

How should NORMDIST be used?

Answer 1

I don't use Google sheets nor am I very good with Excel so I'm not sure how much help this will be.

I wrote the Black Scholes formula in a spreadsheet 25+ years ago, back in the days when BS software was in its infancy on the retail side. The call value from my calculation is $21.67 and the put value is $0.57 and they are almost identical to the output from your link. Where we differ is that my d1 value is 1.5387 versus your calculation of 1.3766

You have an error in your d1 formula. You multiplied instead of adding. It should be:

((ln (60.89/40) + ((0.02 + 0.5 * 0.59 ^ 2)* 0.27)) / (0.59 * SQRT(0.27)))

------------------------------ ^ here, above the caret

See if that makes a difference. I can't help you with the NORMDIST function since I am still using ancient spreadsheets (g) and my guess is that's a separate issue to be solved if it remains a problem.

Answer 2

I'm not familiar with this form of Black-Scholes (d1, d2, C), but the original q was regarding NORMDIST, is there a chance that NORMDIST and NORMSDIST are being confused?

NORMDIST - 4 arguments, the value of the norm distro. link

NORMSDIST - 1 arguments, the value of the standard norm distro cumulative function. link