I know this topic has been on the table before, but I haven't seen a clear explanation with an example. I have successfully calculated the previous steps in


but I am now struggling with stress scenario calculation in pages 23-24. I have made a quick excel to show the calculation I have done. I don't know where I'm going wrong , but I'm quite close number wise (could be still a mile away from correct). A clear answer with used value dates and ranges would be much appreciated to point out where I'm wrong.

Here is a link to excel: https://www.dropbox.com/s/8htamga4mg92pf2/PRIIPS_EuroStoxx50_example.xlsx?dl=0

edit: Added more calculations in the excel above

  • $\begingroup$ I found a problem in your rolling window volatility calculations. In column L and M, you are taking forward looking windows for volatilities, whereas these should be historic volatilities. Please check, this could be the reason why your results are going off. $\endgroup$ Commented Oct 18, 2018 at 15:44
  • $\begingroup$ please can you give me the link to the file can't access the link to excel: dropbox.com/s/8htamga4mg92pf2/… $\endgroup$
    – Aya N
    Commented Nov 14, 2023 at 10:30

2 Answers 2


I think they messed up with the dataset. The dates are weird and the rolling volatilities do not match. They suddenly take 2 year history instead of 5.

May I please ask why did you not take full columns for 1Y and 3Y stress volatilities? (the percentile() starts somewhere in middle of the column)

Thank you.

EDIT: You should use full dataset to calculate rolling volatilities and thus only 1Y and more than 1Y stress volatilities shall differ.(ESA's workshop in Frankfurt 27.11.2017, ESA's workshop ).

  • 1
    $\begingroup$ Im seeing it as if you have 1 year holding period you would use the newest 1 year data available, in this case 2.5.2016 - 28.4.2017 (256 returns). Same for the 3 year holding period: newest 3 year data available, in this case 1.5.2014 - 28.4.2017 (768 returns). Dont know if this is the right way.. Hope my answer helps! $\endgroup$
    – Apnurm
    Commented Nov 22, 2017 at 9:36
  • $\begingroup$ Hello, today I received presentations form latest ESA workshop and they corrected the stress volatility of 3Y and 5Y RHP example (same as in flow diagram). the volatilities of 3y and 5y rhp are now equal, which means you should always use full dataset to estimate stressed volatility. $\endgroup$ Commented Dec 1, 2017 at 8:34
  • $\begingroup$ I checked the presentation on your post. I can match M1 with it but rest is a little bit off. Can't figure out why. Have you been able to match those numbers perfectly for category 2? $\endgroup$
    – Apnurm
    Commented Dec 4, 2017 at 13:08

Yes in the dataflows there were some mixup (similar question with the answer) with the data for the stress scenarious. But for the other scenarious you should be able to get to the results with no problem. Also I would like to address you to the this question where you can find how you should address the rolling window and how to chose dataset for the stress scenarious.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.