IQR – Exclusive or Inclusive, that is the question

I have spoken a lot about the arm’s length range and when we should use a statistical tool such as the interquartile range (IQR) to derive an arm’s length range. But how do we calculate the IQR practically?

The easy answer is, Microsoft Excel or Numbers (for Mac) will do it for you, you just have to use the right formula. For Numbers this is a little easier as there is only one formula (=quartile) but for Excel users this can become a little more confusing as there are two formulas. Originally, Excel also only had one formula but now you have the option of either using or =quartile.exc. The previous formula within Excel was equivalent to, in case you were wondering.

So the questions are: Which formula should I use? Does it make a difference in the range? Will the tax authorities care?

Difference of Exclusive and Inclusive IQR

Let’t try and get some clarity on the above by firstly looking at what the differences are between Inclusive and Exclusive calculations. Exclusive and Inclusive refer to how quartiles are calculated when the data set isn’t divisible by 4. If we have five values, the median is the third value, because it’s in the middle. The first quartile is the median of the bottom half and the third quartile is the median of the top half. Exclusive calculations of our quartiles exclude the third value, and compute the medians of the first and second values and of the fourth and fifth values. Inclusive calculations of our quartiles include the third value, and compute the medians of the first through third values and of the third through fifth.

So in essence, Exclusive calculations result in a wider interquartile range and fewer outliers than Inclusive calculations. Another way the above can be explained is to refer to Exclusive as a greater than formula and Inclusive as a greater than or equal to formula.

It may also be worth mentioning that if you use certain box plots within Excel these may have a default to Exclusive or Inclusive IQRs and you should confirm what this default is to make sure the IQR used is calculated as required.

Which formula should I use?

I am of the view that the Inclusive range is the better suited formula to use. There are a few arguments for and against this but in my mind the purpose of the IQR is to provide an arm’s length range derived from a range that is not accurate enough to be an arm’s length range. The purpose of the IQR is to eliminated outliers in the full range and the Inclusive range calculates a narrower IQR (compared to the Exclusive IQR) which could be argued is a more accurate arm’s length range as we already elected to apply an IQR in any case. I would also argue that a tax authority would apply the narrower range as this is to the tax authority’s benefit, and as such this is a more conservative approach as a taxpayer.

The IQR works well to derive an arm’s length range where the local legislation allows for this. However, we must keep in mind that some tax jurisdictions may not accept this approach. For example, Tanzania requires a taxpayer to derive a range from the 35th to 60th percentile instead of the IQR, which is even narrower.

What are your thoughts on the above? I vaguely remember a discussion around the IRS and that it may actually allow for the Exclusive formula but I was not able to find anything on this. This could be beneficially for a taxpayer but keep in mind that the other tax jurisdiction may not allow this. Any thoughts or input are welcome.

I managed to write an additional blog post in relation to the above, please see here!

5 Replies to “IQR – Exclusive or Inclusive, that is the question”

  1. Hi Marcus: I cannot figure out how the IQR is calculated in excel. Neither the function “Quartile.Inc” nor “Quartile.Exc” seem to correctly compute the IQR. With six data points, the first quartile should be the middle of the bottom three data points. Excel, however, comes up with a different figure entirely. Am I correct?

    As of now, I have to calculate the IQR by hand. I sort the data in excel and then figure out the first and third quartiles.

    Is there a better way of doing this?

  2. This explanation above is not right.
    Consider the following 8 numbers: 1, 2, 4, 5, 7, 8, 10, 11.
    According to the rules above, the first quartile should be the median of 1, 2, 4, and 5, which is 3. However, QUARTILE.EXC and QUARTILE.INC yield 2.5 and 3.5, respectively.
    The previous commenter gives an even easier example.

What are your thoughts?

This site uses Akismet to reduce spam. Learn how your comment data is processed.