3

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?

4
  • What have you discovered so far about NORMDIST ?
    – AakashM
    Jul 17, 2018 at 7:42
  • "How to lose money using Google Sheets"
    – Fattie
    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
  • 2
    @Richard 1) that's not a criticism; 2) I haven't downvoted this question
    – AakashM
    Aug 28, 2018 at 7:42

2 Answers 2

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.

1
  • Thanks for pointing that out. Now I get 1.5415, which is fairly close to your value.
    – 4thSpace
    Jul 17, 2018 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.