FRTB How is 99% VaR is approximatley equal to 97.5% ES on Excel

[rahul.goyl]

I come across numerous question about 99% VaR is equal to 97.5%, also some research claim & has also put up the below
where
99% Z Value of 2.34 using excel function (NORM.S.INV)
Equal
97.5% Z Value of 2.338 using excel function (NORM.S.DIST)


or


However when using excel functionality I am not able to produce the same 2.34 value for 97.5% ES.
The below are results which excel 2010 produce, please correct me why am not able to produce 2.34 as shown in the above 2 screen-shot.



David please help, your feedback will be highly appreciated.

 

[ShaktiRathore]

Hi,
Use formula for ES=NORM.S.DIST(CDF,0)/alpha=pdf/alpha
for 97.5%, ES=NORM.S.DIST(1.96,0)/0.025=2.338
thanks

 

[David Harper CFA FRM]

Hi @rahul.goyl Shakti is right, here is the XLS above at https://www.dropbox.com/s/iclf52q9dghzvn6/0106-normal-es.xlsx?dl=0 I hope that explains, thanks!

 

[rahul.goyl]

Hi,
Use formula for ES=NORM.S.DIST(CDF,0)/alpha=pdf/alpha
for 97.5%, ES=NORM.S.DIST(1.96,0)/0.025=2.338
thanks

Hi Shakti/David,

Thanks for replying to the post, I somehow not understanding the concept behind doing so. Why are we doing Standard Normal of CDF (Norm.S.Dist) to convert in PDF.
Could you please throw more light why is that required in ES multiplier. I don't see any such recommendation on bcbs documents than where this whole concept is driven from.

 

[ShaktiRathore]

As i can see, you are getting the average quantile from the formula,
Formula=Integral of qp dp from confidence level alpha(here in formula i think alpha is confidence level) to 1/(1-confidence level alpha)=weighted average of the quantiles qp to the right of Loss quantile=1.96 with each quantile qp weighted by the weight dp(area under quantile)/(1-confidence level alpha) with integral finally producing value of weighted average of the quantiles qp to the right of Loss quantile=1.96.

Then we get the value as same as average quantile
Thus,
average quantile*area to the right of Loss quantile 1.96=Integral of qp dp from confidence level alpha to 1
area to the right of Loss quantile 1.96 is nothing but 1-confidence level alpha
average quantile*(1-confidence level alpha)=Integral of qp dp from confidence level alpha to 1
average quantile=(1/(1-confidence level alpha))*Integral of qp dp from confidence level alpha to 1
This average quantile gives the magnitude of the ES that is the average loss quantile if the losses exceeds the Var quantile of 1.96.
thanks