2

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 ? – AakashM Jul 17 '18 at 7:42
  • "How to lose money using Google Sheets" – Fattie Jul 17 '18 at 12:07
  • instead of criticize and down voting, beter think about an answer for this legit question @AakashM – Richard Aug 27 '18 at 3:56
  • 2
    @Richard 1) that's not a criticism; 2) I haven't downvoted this question – AakashM Aug 28 '18 at 7:42
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.

  • Thanks for pointing that out. Now I get 1.5415, which is fairly close to your value. – 4thSpace Jul 17 '18 at 14:13
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .