Currently, one of my top blog posts is IQR – Exclusive or Inclusive, that is the question. During my hiatus I did not respond to the comments raised in the post (sorry) but because there were a few, I am sure many others also struggle with this concept. So I wanted to unpack this a bit further and also raise some more awareness around IQRs for transfer pricing purposes. What makes the IQR so interesting from a transfer pricing perspective is that not every tax authority applies the calculation of an IQR consistently.
This brings about some technical questions, for example, if one tax authority accepts an IQR that calculates a higher arm’s length range to another that does not, when using exactly the same data, what should I do? How will the tax authorities interpret this and what can a taxpayer do? I won’t go into further detail here, but let me know if this is something I should unpack in another post or touch base with me directly.
Let’s get back to the topic at hand and with that explain in more detail how to calculate the IQR Inclusive and IQR Exclusive ranges with an example. Thereafter I also managed to find some data from a secondary reference source on how other tax authorities determine the IQR in their jurisdiction. I am not certain if this is indeed correct as I was not able to find a primary reference source, so I won’t mention the jurisdictions but rather just provide the way the tax authority supposedly calculates the IQR. That is also why it is always important to double check your transfer pricing with a local specialist!
Ok here it goes.
How to calculate the IQR Inclusive range as per excel
My previous blog post tried to explain how to calculate the lower or upper quartile through words but nothing beats a visual example, so below is a snippet using excel and each step followed to get to the quartiles.
The left side reflects the IQR Inclusive range and the right the IQR Exclusive range. As you can see from the colour coding, the difference is that the inclusive range ‘includes’ the median to calculate the median between the the 0 and 50th percentile (i.e. lower quartile) whereas the exclusive range ‘excludes’ the median calculating the lower quartile (or upper quartile).
For those of you who think this just can’t be true, I thought I also share the actual calculation of the IQR Inclusive range. To do so, I have used the above observation and hopefully this makes it easier to follow:
Tax authority IQRs observed
I came across the following two examples on how a tax authority calculates the quartiles. Luckily, the median is still calculated the same.
Tax authority 1
The first tax authority uses this equation to calculate the upper and lower quartiles:
Upper quartile = number of observations multiplied by 0.75 and rounding to the nearest number. If we applied that to our example above, that would be observation 5 and therefore the upper quartile would be 55.00
Lower quartile = number of observations multiplied by 0.25 and rounding to the nearest number. If we applied that to our example above, that would be observation 2 and therefore the lower quartile would be 5.00
Tax authority 2
The second tax authority uses this equation to calculate the upper and lower quartiles:
Upper quartile = number of observations multiplied by 0.65 and rounding to the nearest number. If we applied that to our example above, that would be observation 5 and therefore the upper quartile would be 55.00
Lower quartile = number of observations multiplied by 0.35 and rounding to the nearest number. If we applied that to our example above, that would be observation 2 and therefore the lower quartile would be 5.00
Even though the results happen to be the same as per tax authority 1 this is only due to rounding and if there were a few more observation these would have differed.
Other ways to calculate an IQR
Lastly, I just wanted to touch on two more methods I have seen in practice to calculate an IQR. Where the observations for the 0 to 50th percentile, including the median, are even, the two observations in the middle are averaged to derive the lower quartile (or upper quartile where we consider 50 to 100th percentile).
The other method which I have seen, albeit not often, is using a standard deviation to determine the lower or upper quartile. In this method you would determine the median and then deduct the standard deviation to calculate the lower quartile or add the standard deviation to calculate the upper quartile.
Finally this is clear – as mud
I really hope the above shed some light on the different IQRs. I am sure there are even more ways to calculate an IQR or arm’s length range. If you came across something please share below.