Sheet 5-d-1 | Test for Var Subadditivity

[sudeepdoon]

Hi David,

I had a question and then a conern; first for the question:
In the sheet we have got the VAR for a portfolio of 3 bonds as 100. I seem to have a different value..

When trying to see all possible outcome of the portfolio which would be 0,-100,-100,-100,-200,-200,-200 and -300 . The number of outcomes are 2^3 = 8...

On using the PERCENTILE function in Excel and trying to get a 95% VAR for the portfolio I get a value of 265..

Where am I wrong?

And for the concern:

Frankly, I dont seem to get a good hang of the ES and EVT. I have gone through the video and the AIMS are all clear but when it comes to understand the Excels things get hazzy... I guess you have it clear that this has a low probabilty to be questioned by still I dont feel satified (with what I have able to understand..)

For sure it would be a wrong expectation to ask you to give special attention to the topics. I request would be to please provide a link to any of for tutorials that may be taking a deeper dive into the topic or even a link to some article would also do.

Thanks,
Sudeep Manchanda

 

[David Harper CFA FRM]

Hi Sudeep,

Your approach would be fine if all eight outcomes were equally likely, or if an historical simulation had produced those eight outcomes (where prob of each is 1/8).
I expanded the calculation in this XLS: http://sheet.zoho.com/public/btzoho/subadd-aug25

http://learn.bionicturtle.com/images/forum/subadd_aug25.png

under an assumption of 2% PD, note how all outcomes are not equally likely; e.g., because 98%^3 = 94.11%, the outcome of zero defaults is most likely. And, then 90% VaR will give zero! So, note the 95% (cumulative) loss must be -100 (no interpolation as the first default ranges from 94.119% to 96.040% and therefore squarely includes 95%)

Re: "I dont seem to get a good hang of the ES and EVT"
I have two thoughts.
First, these are among the hardest, esp. EVT...nobody gets a good hang of these on first pass...it sincerely took me three years of *teaching* EVT to sort of get a bit of a grasp. (and i am still constantly referencing the K. Dowd). The exam will not require excel-depth understanding!! No way...only conceptual....
Second, I will start doing some briefcast topics on stuff like this (e.g., today I did tracking error b/c I realize that is not simple), definitely EVT, so stay tuned and I will try to help with "mini boosts" for the hard stuff including ES and EVT.

Thanks, David

 

[sudeepdoon]

Thanks David,

I realily appreciate your efforts!

I would again take the opertunity to thank you for all the fast replies to the questions we post.
"www.bionicturtle.com" has become like an angle that works the entire night for you..(wrt to my time zone). you post the question in the night before you go to sleep and in the morning you have the replies... Even the thought of that makes my friends say "Cool!!!"

Thanks David

 

[David Harper CFA FRM]

Sudeep, Thank you for appreciating forum support efforts: I very much appreciate your appreciation

...please note yesterday I uploaded a new, brief recap of expected shortfall
here @ http://www.bionicturtle.com/learn/article/expected_shortfall_7_min_video_market/
(this is my cleanest attempt to summarize ES in brief)

David

 

[sudeepdoon]

Hi David,

Thanks a lot. This helped!

Just to verify my understanding the both ES and EVT concentrate on the tail on the distribution; ES tries to find the average of the losses that lie in the tail, where as EVT attempts to map to it some distribution.

Like in the example to took EVT would model the distribution as binomial....

 

[sudeepdoon]

Hey David, Is this spreadsheet uploaded somewhere? I was not able to find it on the member page.

FYI . I was not able to leave a comment on the link the you shared..

 

[David Harper CFA FRM]

Hi Sudeep,

Re: "Like in the example to took EVT would model the distribution as binomial…. " No EVT is used in the binomial example. Rather, we have a default characterized by a Bernoulli (default or no default) such that a portfolio of independent Bernouillis is characterized by a binomial. Given the binomial distribution, like any distribution, we can solve for an ES. No EVT is employed. EVT would be something like: we feel the tail is inaccurate under binomial, so we reach for an EVT distribution and "replace" that EVT into the tail.

Re: "both ES and EVT concentrate on the tail on the distribution; ES tries to find the average of the losses that lie in the tail, where as EVT attempts to map to it some distribution"

Yes, I think this is a very good characterization because ES does not require (or even imply) a different distribution; e.g., in the examples, we used the same normal distribution to find VaR and ES and then, for the bond portfolio example, we used the same binomial distribution to find VaR and ES. As you suggest, if we can safely assume a single distribution to characterize losses, then both VaR and ES can be derived from it...

whereas EVT implies the body of theory around a new distribution that characterizes only the tail...the way I think of this is: the EVT tail distribution is a "child" that is "grafted" onto the parent distribution...

but also please note, they are not mutually exclusive. Starting at cell H18:
http://www.bionicturtle.com/premium/spreadsheet/5.d.3._dowd_evt/

...is the example of calculating ES for the EVT GPD distribution. So in this case, EVT gives us a distribution for the extreme tail, that is "grafted" onto some parent distribution, then we ask of that EVT distribution "what is average loss in the tail?" So we can ask an ES question about an EVT distribution.

Re: the XLS i used is attached (it's not much different than member page version)

Thanks, David