Use of NORMSDIST and NORMSINV in Excel

[sudeepdoon]

Hi David,

I have been looking at my excels now which has calculation around the Normal Distribution and I am a bit confused around the use of NORMSDIST and NORMSINV functions in Excel.

in a few cases we have used the formule:

NORMSDIST(Z) and NORMSINV(P) I guess for a single tailed distribution
and sometimes NORMSDIST(Z)*2 + 1 and NORMSINV(P/2 +0.5) I guess for a two tailed distribution.

1. please validate this understanding.
2. Is VAR a single tail or a two tail distribution ?
3. How do we be sure for what distribution are we dealing with.

Thanks,
Sudeep Manchanda

 

[David Harper CFA FRM]

Hi Sudeep,

It's a great point...I realize you already know this but for others, just FYI, the excel functions themselves are not relevant to the exam. Candidates are not expected to know NORMSINV() beyond the two common ones: NORMSINV(95%) = 1.645 and NORMSINV(99%) = 2.33. Any other needed inverse CDFs must be provided since they cannot be gotten on the calculator...

1. Your understanding (of my use) is *exactly* correct. As the MS Excel function =NORMSDIST is inherently "one-tailed," the other manipulations I just used to convert into two-tailed. A CDF (i.e., what is the probability X is less than or equal to) is inherently one-tailed, so: NORMSDIST(), being a CDF, is one-tail without modification and NORMSINV is the inverse CDF, also one-tail unless modified.

2. VaR is *always* one-tailed because we only care about the downside loss, the "other tail" is the upside gain and it's not part of the formulation. A one-tail is betrayed by the use of a <= or a >=

if null is "=" then two tailed,
but VaR is: P[loss > VaR] <= 1 - c

...so please note: the null always has an equal sign. If only an "=" then two-tail, but VaR has a "<" or a ">" (depending on how you characterize), so the <= or the >= betrays a two-tailed

3. Specifically, you want to (as above) look at the null. If the null contains a < or an > you must have a one-tail test. But as a general rule (please don't hold me to this always), our tests of significance (e.g., test of significance of regression coefficient) are two-tailed because our null is typically "coefficient = 0" (because "significance" is to find in favor of the alternative; i.e. that the coefficient is nonzero). And, on the other hand, VaR is always one-tailed.
(so, that's a rule of thumb that will apply often: significance is two-tailed, VaR is one-tailed...but again, be careful b/c the significance test definitely can be one tailed, one of the sample exam questions is one-taild)

So the best *illustration* I have of this is Jorion's backtest. See
http://www.bionicturtle.com/premium/spreadsheet/5.c.1._jorion_backtest/
(which merely illustrates Jorion's table 6-2)

...because both are employed!

for example, take 95% confidence test of 90% VaR (answer is: 16 < N < 36)
The 90% VaR is one-tailed
but the backtest is a two-tailed 95% significance test
(hopefully a meditation on this is instructive)

hope that helps, David

 

[David Harper CFA FRM]

Sudeep - actually i misspoke: i wasn't thinking of the sample exams for a one-tailed test...I was thinking of a question from Gujarati:
http://forum.bionicturtle.com/viewthread/1314/

b/c the alternative hypo (i.e., the idea you hope to prove) is "independent var is positively related to dependent" ... so that's a good example of one-tailed; i.e.,

if alternative is "dependent variable is related to independent" (more typical), then coefficient could be +/- and that's a null of "slope coefficient = 0"
but if alternative is "dependent var is positively or negatively related to to independent," then we have a one-tailed test...

so for example, if we go to test hedge fund alpha, our alternative might be "alpha is positive" and this implies a one-tailed

David