• 1
• 2 ppauper
Posts: 70239
Joined: November 15th, 2001, 1:29 pm

Re: NORM.DIST methodology

Thanks but the problem is the ERF number. I'm using 0.842700793 which multiplied by 1/sqrt(2) = 0.59... and not the 0.682689 that you have.
NO
erf is a function.
you want erf( 1/sqrt(2) )
you have used ( erf( 1) ) /sqrt(2) Cuchulainn
Posts: 59896
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Re: NORM.DIST methodology

I agree with ppauper: start again. You are missing something in your analysis..

I already showed how I did it (and I checked it multiple times).

Let us recall it.
return 0.5*std::erfc(-x / std::sqrt(2.0));  Cuchulainn
Posts: 59896
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Re: NORM.DIST methodology

I've already replicated ERF via Abramowitz & Stegun 7.1.5 but ERF does not  equal NORM.DIST(x,mean,standard_dev,cumulative).

No one here ever claimed they were the same! (except possibly yourself)

BTW are you saying you coded up 7.1..5? If yes, how did you truncate the series? Cuchulainn
Posts: 59896
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Re: NORM.DIST methodology

I've already replicated ERF via Abramowitz & Stegun 7.1.5 but ERF does not equal NORM.DIST(x,mean,standard_dev,cumulative).

=NORM.DIST(1,0,1,TRUE) = 0.84134475
=ERF (0,1) = 0.84270079
Sure, but see how I do it with erfc. i.e. N() is computed in terms of ERF.
recall the last sentence. Cuchulainn
Posts: 59896
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Re: NORM.DIST methodology

Thanks but ERF or ERFC that doesn't replicate =NORM.DIST(1,0,1,TRUE) = 0.84134475 - unless I'm missing something here.
I checked the stuff
Excel
N(1) = 0.841344746
C++
erf(1) = Same as ppaupers' up to 16 digits (0.8427 0079 2949 7149)
N(x) = 0.8413447460685428 miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

Re: NORM.DIST methodology

Thanks guys. I was being a bit slow there but all clear now. I appreciate your help - you are stars !!! Cuchulainn
Posts: 59896
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Re: NORM.DIST methodology

You're welcome. Jordy
Posts: 20
Joined: October 1st, 2010, 12:00 am

Re: NORM.DIST methodology

Don't forget the paper "Better approximations to cumulative normal functions" by Graeme West. If I remember correctly, the double precision implementation matches Excel exactly.

Cheers,
Jordy Cuchulainn
Posts: 59896
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Re: NORM.DIST methodology

Don't forget the paper "Better approximations to cumulative normal functions" by Graeme West. If I remember correctly, the double precision implementation matches Excel exactly.

Cheers,
Jordy
Indeed. I spent some time on this

1. In C++11 N(x) is essentially a built-in function as already noted.
2. I did extensive (stress) tests (randomly generated parameters) on BVN using Graeeme's code and Quantlib. Same output.
3. As sanity check of 2, I posed BVN as a Goursat PDE that I solved using Box (2nd order) and extrapolated schemes
4. For TVN you can solve it as a Goursat-Fuchs PDE.
5. All tested with Chooser option from Collector's  Big Book.
6 So, Excel implements GENZ algorithm?

Tactic 2 (PDE posing) can be posed for any distribution (I examine Student in my 2018 C++ book) and it has a gear-box to tune to desired accuracy.
double N(double x)
{ // aka CdfN(x)

return 0.5*std::erfc(-x / std::sqrt(2.0));
} ikicker
Posts: 6
Joined: June 8th, 2011, 12:19 am

Re: NORM.DIST methodology

NORM.DIST(x,mean,standard_dev,cumulative). When cumulative is TRUE this function returns the cumulative density function which is the normal density function integrated from negative infinity to x.
I understand that there is no closed form solution for  integrating the NDF but does anyone know which methodology Excel uses? I've tried series approximations, continuous fractions and polynomials but get slightly different values .
There is a closed form approximation. Approximation isn't always bad. It's like when you deal with duration and convexity. We don't traditionally go beyond convexity, but it is possible to get a more accurate estimate. It's a taylor series. There is an ERF function. The closed form equation extends out until the amount is meaningless. That's probably what Excel does.
----
Undergraduate: accounting, finance, information systems; Graduate: MBA/finance; Graduate certificates: data science, applied statistics, advanced valuation; PhD: data science  