# How to calculate Black-Scholes using Google Sheets?

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?

• What have you discovered so far about `NORMDIST` ? Jul 17, 2018 at 7:42
• "How to lose money using Google Sheets" Jul 17, 2018 at 12:07
• instead of criticize and down voting, beter think about an answer for this legit question @AakashM Aug 27, 2018 at 3:56
• @Richard 1) that's not a criticism; 2) I haven't downvoted this question Aug 28, 2018 at 7:42

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

((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.

• Thanks for pointing that out. Now I get 1.5415, which is fairly close to your value. Jul 17, 2018 at 14:13

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