Estimating Monthly Lower-Tail Return Quantiles

Summary

The document asks how to find the lower five-percent quantile of daily currency returns separately for each month. One response gives a direct spreadsheet method: calculate the fifth percentile of the month's return observations. Another suggests sorting observations and selecting the corresponding order statistic, with the selected rank depending on the number of trading days. A parametric estimate is also mentioned as an alternative, and a response points to a statistical software package without explaining its procedure.

The percentile calculation is the clearest general approach in the document. The sorting method illustrates how a quantile maps to an observation in a finite sample, but choosing an observation by rank can depend on sample size and quantile convention. The document does not compare interpolation conventions, discuss the uncertainty of monthly tail estimates, or explain parametric assumptions. Its examples are limited to monthly currency-return data and spreadsheet use.

Key ideas

• Estimate each month's lower-tail threshold from that month's return observations.
• A percentile function can directly calculate the requested empirical quantile.
• Sorting returns can identify an order statistic, though the rank depends on sample size and convention.
• A parametric estimate is mentioned, but its assumptions and implementation are not described.

Tags

# find the qth lower tail quantile


# find the qth lower tail quantile












I have daily currency returns. For each month, I have to find the return associated to the 5% lower tail quantile for each currency (the lowest return or the second lowest return).

Could you please tell me how to do it using Excel?

Thanks in advance.

## Answer by Alex C (score 0, accepted)

https://quant.stackexchange.com/a/24714

Use =PERCENTILE(range, 0.05)

where range refers to the returns in question

## Answer by Neeraj (score 0)

https://quant.stackexchange.com/a/24708

Just arrange your data in ascending or descending order using filter tab in Excel.

> Data $<-$ Filter $<-$ Sort Smallest to Largest

If there are 20 days in each month, then select second lowest return for each month.

Otherwise, You may use parametric approach to estimate 5% lower quantile for each and every month.

## Answer by Anonymous (score 0)

https://quant.stackexchange.com/a/24713

Use this R package: Performance Analytics Link: https://cran.r-project.org/web/packages/PerformanceAnalytics/index.html

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.