SERVING THE QUANTITATIVE FINANCE COMMUNITY

• 1
• 2

miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

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

Cuchulainn
Posts: 60470
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Re: NORM.DIST methodology

Probably one of Abramowitz & Stegun 26.2.16 to 26.2.19 (polynomials accuracy [1.0e-4, 1.0e-8]). Continued fraction not much better I sus[pect

I have found erfc to be  better (e.g. by comparing above with exact BS price).
double N(double x)
{ // aka CdfN(x)

// C++11 supports the error functions erf and erfc
return 0.5*std::erfc(-x / std::sqrt(2.0));
}


Last edited by Cuchulainn on June 21st, 2018, 8:31 pm, edited 1 time in total.
http://www.datasimfinancial.com
http://www.datasim.nl

Approach your problem from the right end and begin with the answers. Then one day, perhaps you will find the final question..
R. van Gulik

Cuchulainn
Posts: 60470
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Re: NORM.DIST methodology

I see Excel has a ERF function, might be worth a try
https://support.office.com/en-us/articl ... df3c9af349

erfc = 1 - erf
http://www.datasimfinancial.com
http://www.datasim.nl

Approach your problem from the right end and begin with the answers. Then one day, perhaps you will find the final question..
R. van Gulik

miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

### Re: NORM.DIST methodology

Thanks. I'll take a look at those. I think one of them ( Abramowitz & Stegun 26.2.16-19)  might be the polynomial approximation I've used from Quantitative Methods for Finance by Parramore and Watsham but  that does not fully match NORM.DIST. Maybe one of the others does.

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

Cuchulainn
Posts: 60470
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.
http://www.datasimfinancial.com
http://www.datasim.nl

Approach your problem from the right end and begin with the answers. Then one day, perhaps you will find the final question..
R. van Gulik

miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

### Re: NORM.DIST methodology

Abramowitz & Stegun 26.2.17 gives the closest value to NORM.DIST but not identical.

Alan
Posts: 9865
Joined: December 19th, 2001, 4:01 am
Location: California
Contact:

### Re: NORM.DIST methodology

Here is a study of the accuracy. If I am reading it correctly, looks like 12-13 good digits after the 2010 statistical function revisions. I doubt you'll find the definitive algorithm anywhere. I think I saw somewhere where MS says they switch to a different algorithm for $|z| > 5$ (probably asymptotics).  But, googling should turn up some series or other short expressions good to 13 or more digits (I don't think the A&S series are that good).

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

### Re: NORM.DIST methodology

there's super-accurate code discussed here
maple with 30 digits gives erf(1)=0.842700792949714869341220635083

miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

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

Cuchulainn
Posts: 60470
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.
Then NORMDIST is wrong? (i don't use VBA maths functions but I would wonder if they are brilliant).
Have you used this function? in C++11
https://stats.stackexchange.com/questio ... on-related

And Boost

https://www.boost.org/doc/libs/1_53_0/l ... _dist.html
http://www.datasimfinancial.com
http://www.datasim.nl

Approach your problem from the right end and begin with the answers. Then one day, perhaps you will find the final question..
R. van Gulik

Cuchulainn
Posts: 60470
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Re: NORM.DIST methodology

A&S is from another era. (22.2.1=7 is O(1.0e-8) accurate. In those days error analysis of floating-point arithmetic was part of each numerical analysis course. Single precision was the safe standard. Now we have IEEE 754.
http://www.dsm.fordham.edu/~agw/arch-gr ... 374493.pdf
http://www.datasimfinancial.com
http://www.datasim.nl

Approach your problem from the right end and begin with the answers. Then one day, perhaps you will find the final question..
R. van Gulik

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

### 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.
you are missing something.
The error function and cumulative normal distribution differ by several factors in their definitions and have different limits of integration
(1+erf(1/sqrt(2.)))/2;=0.8413447462 replicates your result

${\rm erf}(x)=\frac{2}{\sqrt{\pi}}\int_{0}^{x}e^{-x^{2}}dx$
the cumulative normal distribution is
$\frac{1}{\sqrt{2\pi}}\int_{-\infty}^{x}e^{-x^{2}/2}dx$

miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

### Re: NORM.DIST methodology

I'm seeing the light here but can't replicate your numbers

(1+erf(1/sqrt(2)))/2  where erf(0,1)=0.8427008  = 0.79793

Matlab (whose normcdf function returns the same values as excel NORMDIST) use
normcdf(x)= 0.5*ERFC*(-x/SQRT(2)). (This is what Culhulainn said earlier).

see: https://uk.mathworks.com/help/stats/normcdf.html

But agian I can't floor the numbers.

for P(x) where x=1 = 0.5*0.1572*(-1/sqrt(2)) = -0.05561

What am I missing or doing wrong?

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

### Re: NORM.DIST methodology

I'm seeing the light here but can't replicate your numbers

(1+erf(1/sqrt(2)))/2  where erf(0,1)=0.8427008  = 0.79793
I've got no idea what you've done
Go through it step by step and see where the disagreement is
1/sqrt(2)=0.7071067814
erf(1/sqrt(2))=erf(0.7071067814)=0.6826894923
1+erf(1/sqrt(2))=1.6826894923
(1+erf(1/sqrt(2))/2=0.8413447462

In case you don't know,
erfc(z)=1-erf(z)
erfc(-z)=1+erf(z)

miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

### Re: NORM.DIST methodology

Thanks. Got it now
Last edited by miltenpoint on June 23rd, 2018, 9:33 pm, edited 2 times in total.