I got some historical daily cds spreads over 10 yr period and I want to find the 95th, 99th percentile largest move over a 2 week period… In column A i have the date and column B i have the spread going down c1600 rows.
I’m prepared for much smarter people to tell me this is 100% incorrect, but this is what I’d do:
I’d try something like (((Day14-Day1)/Day1)) and plop that into Column C and drag it all the way down to get a list of nearly 1600 14 day spreads, by %. Since i’m assuming you don’t care about the direction of movement, you could throw an =abs in front of that so that your output list has just the largest % moves For your 95/99th percentiles, you could do lots of things. You could sort and count, you could do z-scores I suppose. You could just make something up and hope nobody notices. Lots of options.
Yeah easiest way is historical simulation approach where you do as described above, then sort them by magnitude, and then take the (1600 - 14) - ((1600 -14) x 0.95) = 79th value for the 95th percentile and the 16th value for the 99th percentile. (Personally I wouldn’t use the absolute values unless you are of course only interested in sheer magnitude of move with no care for direction)
Either that…or you could go the parametric approach by calculating the standard deviation and mean of the values and then using the old 1.645 and 2.33 standard deviations away from the mean for a 1 tailed 95th and 99th percentile value.
And when I say “values” I mean the % changes you calculated, not the spread values themselves.