# Calculating VaR on the Jorion GBM Excel Sheet

Discussion in 'P1.T2. Quantitative Methods (20%)' started by wrongsaidfred, Aug 24, 2011.

1. ### wrongsaidfredMember

Hi David,

When computing VaR on the Jorion-GBM Excel sheet, you used an Excel function to find the percentiles. There seem to be some discrepancies as to how this should be done if we do not have access to Excel. In this case, for instance, how exactly would you use these 40 final prices to find the 90th, 95th and 99th percentile VaR? Should we use some sort of interpolation or just use percentiles?

If there are 10 paths, I believe the 95th and 99th percentiles would both be the lowest price. Is this correct? Would the 90th percentile also be the lowest price or would it be the 9th out of the 10 ranked prices?

Any advice on this topic would be greatly appreciated.

Thanks,
Mike

2. ### David Harper CFA FRMDavid Harper CFA FRM (test)

Hi Mike,

Again, you've hit on a topic with a history (um, congrats?). As I understand, there is no single superior method when the quantile/percentile draws from a discrete distribution. Dowd says this, among others. In the case of loss n = 40, then technically valid answers to the question, what is the 90th percentile VaR? (assuming simple unweighted historical simulation), include:
* The 5th worst loss (36th best; Dowd's method), or
* The 4th worst loss (37th best, which I believe Jorion is still using), or
* Interpolation between the 4th and 5th (between 36th and 37th)

I like Dowd for this simple intuition: here the 90% VaR implies a 10% tail such that 40 * 10% = 4 losses are "in the tail." (i.e., worst, 2nd worst, 3rd worst, 4th worst = 4% of the total). Then, the 5th worst allows us to emphasize the "worse than" aspect of VaR phrasing with "10% of the time we expect the loss to EXCEED the VaR."
So, my pref is to follow Dowd: [(significance% * n) + 1]th worst lost; e.g.,
if n = 40, the 90% VaR = 10%*4 + 1 = 5th worst
(i.e., 36th best)

In the case of n=10 and 95% and 99% VaR, in my opinion, that is not ambiguous: any VaR with confidence ABOVE 90% would return the lowest price (ie., I totally agree with you); e.g., with n = 10, even 91% VaR returns the lowest! Because unlike the above where the 10% quantile is "falling exactly in the crack between" two loss points, under n = 10, 91% or 95% or 99% all fall "on top of" or "within" the lowest DISCRETE point.

What about the exam? GARP is very well aware of the inexactitude of 36th or 37th or interpolation between in the case of 90% and n = 40 (you may note they issued a revision to the practice question per our input on this issue). So if they ask about the first case, they will allow a 36th or 37th answer. (I've requested they "settle" on Dowd's methodology and synchronize Jorion to Dowd but not yet). Bottom line on the ambiguous case: GARP knows to recognize either 36th or 37th as valid so they won't force your choice.

I hope that helps, David

3. ### wrongsaidfredMember

That makes perfect sense.

Thanks,
Mike

4. ### wrongsaidfredMember

Hi David,

Again, this is from Schwesser, but apparently it is a question and answer from an old exam and I was hoping you could explain the answer.

They state that there are 300 returns and want the 99% VaR. The chose the 3rd from the bottom of the list instead of the 4th from the bottom of the list. From our discussion above, it sounds like we should have taken the 4th.

I have emailed GARP about this previously and they do not want to answer my questions. They simply say that the question will provide all of the information needed.

For one of the largest topics in the exam this is a pretty crappy answer.

Can you think of any way they they could subtly tell us which one of these answers would be considered correct?

Thanks,
Mike

5. ### David Harper CFA FRMDavid Harper CFA FRM (test)

Hi Mike,

Right, I have repeatedly asked GARP to show some leadership on this methodological point (just use Dowd's method). Per Dowd, I prefer 4th from bottom if n=300 and 99% VaR.

GARP is well aware of the discrepancy; as i understand, they are going to craft questions to allow for either answer. So, going forward, under this type of question, you would not see both 3rd and 4th. Hopefully, you would just see 4th from bottom.
(as i've argued, Dowd is the assigned reading and Dowd would give 4th in this case).

Thanks, David