NORM.DIST methodology

miltenpoint · June 21st, 2018, 6:17 pm

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 · June 21st, 2018, 8:04 pm

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));
}

Cuchulainn · June 21st, 2018, 8:09 pm

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

erfc = 1 - erf

miltenpoint · June 21st, 2018, 9:13 pm

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 · June 21st, 2018, 9:57 pm

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.

miltenpoint · June 22nd, 2018, 3:22 pm

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

Alan · June 22nd, 2018, 4:50 pm

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 · June 22nd, 2018, 9:04 pm

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

miltenpoint · June 23rd, 2018, 12:34 pm

Thanks but ERF or ERFC that doesn't replicate =NORM.DIST(1,0,1,TRUE) = 0.84134475 - unless I'm missing something here.

Cuchulainn · June 23rd, 2018, 12:59 pm

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

Cuchulainn · June 23rd, 2018, 1:23 pm

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

ppauper · June 23rd, 2018, 2:38 pm

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 · June 23rd, 2018, 3:24 pm

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 · June 23rd, 2018, 3:46 pm

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 · June 23rd, 2018, 4:00 pm

Thanks. Got it now

NORM.DIST methodology

NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology

Re: NORM.DIST methodology