SERVING THE QUANTITATIVE FINANCE COMMUNITY

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

NORM.DIST methodology

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 .
 
User avatar
Cuchulainn
Posts: 60470
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Re: NORM.DIST methodology

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

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
 
User avatar
Cuchulainn
Posts: 60470
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Re: NORM.DIST methodology

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
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
 
User avatar
miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

Re: NORM.DIST methodology

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
 
User avatar
Cuchulainn
Posts: 60470
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Re: NORM.DIST methodology

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.
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
 
User avatar
miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

Re: NORM.DIST methodology

June 22nd, 2018, 3:22 pm

Abramowitz & Stegun 26.2.17 gives the closest value to NORM.DIST but not identical. 
 
User avatar
Alan
Posts: 9865
Joined: December 19th, 2001, 4:01 am
Location: California
Contact:

Re: NORM.DIST methodology

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).  
 
User avatar
ppauper
Posts: 70239
Joined: November 15th, 2001, 1:29 pm

Re: NORM.DIST methodology

June 22nd, 2018, 9:04 pm

there's super-accurate code discussed here
maple with 30 digits gives erf(1)=0.842700792949714869341220635083
 
User avatar
miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

Re: NORM.DIST methodology

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.
 
User avatar
Cuchulainn
Posts: 60470
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Re: NORM.DIST methodology

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
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
 
User avatar
Cuchulainn
Posts: 60470
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Re: NORM.DIST methodology

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
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
 
User avatar
ppauper
Posts: 70239
Joined: November 15th, 2001, 1:29 pm

Re: NORM.DIST methodology

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[$]
 
User avatar
miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

Re: NORM.DIST methodology

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? 
 
User avatar
ppauper
Posts: 70239
Joined: November 15th, 2001, 1:29 pm

Re: NORM.DIST methodology

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)
 
User avatar
miltenpoint
Topic Author
Posts: 215
Joined: February 23rd, 2006, 1:40 pm

Re: NORM.DIST methodology

June 23rd, 2018, 4:00 pm

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

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On