Serving the Quantitative Finance Community

 
User avatar
ppauper
Posts: 11729
Joined: November 15th, 2001, 1:29 pm

Re: NORM.DIST methodology

June 23rd, 2018, 4:06 pm

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)
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Re: NORM.DIST methodology

June 23rd, 2018, 8:06 pm

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));
Image
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Re: NORM.DIST methodology

June 23rd, 2018, 8:20 pm

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?
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Re: NORM.DIST methodology

June 23rd, 2018, 8:23 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.
recall the last sentence.
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Re: NORM.DIST methodology

June 24th, 2018, 3:42 pm

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

Re: NORM.DIST methodology

June 24th, 2018, 5:53 pm

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

Re: NORM.DIST methodology

June 24th, 2018, 6:41 pm

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

Re: NORM.DIST methodology

March 4th, 2019, 10:36 pm

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
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Re: NORM.DIST methodology

March 5th, 2019, 10:08 am

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));
}
 
User avatar
ikicker
Posts: 30
Joined: June 8th, 2011, 12:19 am

Re: NORM.DIST methodology

June 20th, 2019, 2:13 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 .
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 candidate - data science

Blog: Www.ThinkerTinkerSolutions.com