Published April 17, 2025Updated November 5, 202610 min read
Interquartile Range in Transfer Pricing: Calculation & ExamplesInterquartile Range in Transfer Pricing: Calculation & Examples
Calculate the interquartile range in transfer pricing with a worked example and Excel formulas. Compare the U.S. rank method, Excel INC/EXC and local rules.
BorysCEO of ArmsLength AI
10min read
Contents↓
Contents
TL;DR key takeaways
The quartile interval runs from Q1 to Q3. Its statistical width is Q3 minus Q1; these are different outputs.
The U.S. regulatory rank method is not the same as Excel PERCENTILE.INC or PERCENTILE.EXC.
For comparable prices of 42, 44, 45 and 47.5, the U.S. quartile interval is 43 to 46.25 and its width is 3.25.
First establish comparability and the applicable local rule. Statistical trimming does not make unsuitable comparables reliable.
Record the dataset, percentile algorithm and tax year so another reviewer can reproduce the result.
We tested three AI configurations on 10 transfer pricing questions. ChatGPT with web search scored 83%, but models without web access fabricated most quotes. Here's what we learned.
The Berry Ratio (gross profit ÷ operating expenses) is a specialized PLI for low-risk distributors and agents. Learn when it's appropriate, when to avoid it, and how to defend your analysis—with calculation examples and jurisdictional guidance.
On November 10, 2025, HMRC updated INTM485120 with critical details on searching for comparables. The key takeaway? The Interquartile Range is not a default right - and relying on it blindly carries a median-sized risk.
More resources for your next workflow stepMore resources for your next workflow step
Browse the full resource library or contact us if you want recommendations for your specific use case.
What does the interquartile range mean in transfer pricing?
In a transfer pricing benchmark, the interquartile interval runs from the 25th percentile (Q1) to the 75th percentile (Q3). Practitioners often call that interval the interquartile range, or IQR. In statistics, IQR also means its width: Q3 − Q1.
For example, an interval of 4% to 7% has a width of 3 percentage points. A report should show both endpoints when testing a margin; the width alone cannot tell you whether the margin is inside the interval.
The calculation has three separate decisions: which observations are comparable, which range the applicable rules permit, and how the endpoints are computed. Start with the benchmarking analysis, including the profit level indicator and tested party, before calculating percentiles.
U.S. quartiles: rank, then average when required
Treasury Regulation §1.482-1(e)(2)(iii) distinguishes a full range of sufficiently reliable comparable results from a range narrowed to improve reliability. IQR is ordinarily acceptable in the latter case; it is not compulsory for every U.S. benchmark.
Calculation method
For ordered observations and a percentile expressed as a decimal, such as 0.25:
Multiply the number of observations, n, by the percentile, p.
If n × p is a whole number, average the observation at that rank and the next observation.
Otherwise, round the rank up and use the observation at that position.
Use p = 0.25 for Q1 and p = 0.75 for Q3. This rank rule should not be called Excel's “exclusive” method: the algorithms differ.
Q1: 4 × 0.25 = 1, so average observations 1 and 2: (42 + 44) / 2 = 43.
Q3: 4 × 0.75 = 3, so average observations 3 and 4: (45 + 47.5) / 2 = 46.25.
Median: (44 + 45) / 2 = 44.5.
Interval:43 to 46.25. IQR width: 46.25 − 43 = 3.25.
The figures below compare three calculations on the same four observations. They do not represent interchangeable tax rules.
Calculation
Q1
Q3
IQR width
U.S. regulatory rank method
43.000
46.250
3.250
Excel PERCENTILE.INC
43.500
45.625
2.125
Excel PERCENTILE.EXC
42.500
46.875
4.375
A tested price of 43.25 falls inside the U.S. interval but below the Excel INC interval. That is why the formula matters even when the comparable set has not changed.
Excel formula for the U.S. rank method
Put the four prices in A2:A5. The formulas below use integer percentage numerators to identify whole-number ranks. For another dataset, replace every A2:A5 reference with the same numeric range.
Use a non-empty numeric dataset with consistent units. COUNT ignores text and blank cells; check that the count matches the observations you intended to include. A formula does not establish whether a small sample is sufficiently reliable for tax purposes.
How Excel INC and EXC differ
Microsoft's INC function uses rank 1 + (n − 1) × p. EXC uses (n + 1) × p. Both interpolate between adjacent observations when needed. EXC returns an error when the requested percentile cannot be interpolated within the sample.
excel
Excel INC:
Q1: =PERCENTILE.INC(A2:A5,0.25)
Q3: =PERCENTILE.INC(A2:A5,0.75)
Excel EXC:
Q1: =PERCENTILE.EXC(A2:A5,0.25)
Q3: =PERCENTILE.EXC(A2:A5,0.75)
IQR width, if Q1 is in B2 and Q3 is in C2:
=C2-B2
Adjustment policy
Under §1.482-1(e)(3), an adjustment outside an IQR-based range will ordinarily be to the median of all comparable results. The regulation also allows adjustment to another point within the arm's length range; “ordinarily” should not be rewritten as “always.”
OECD guidance: when narrowing a range helps
OECD paragraphs 3.55–3.63 allow a range of equally reliable results. Where unquantified comparability defects remain and there are enough observations, statistical narrowing may improve reliability.
The OECD does not prescribe Excel INC, EXC or one universal percentile formula. Nor does it require an automatic median adjustment: the appropriate point depends on reliability and the facts. An extreme result alone is not a reason to reject a comparable.
Read the comparability guidance together with the applicable domestic rules. Record the algorithm rather than describing a spreadsheet default as an “OECD method.”
India: a 35th–65th percentile range
Rule 81 of the Income-tax Rules 2026, effective from 1 April 2026, provides a 35th–65th percentile range for CUP, resale price, cost plus and TNMM where the prescribed dataset has at least six entries. Follow its dataset and weighted-average requirements, not just the observation count.
Within that range, the transaction price is accepted; outside it, the median of the dataset determines the arm's length price. Where the range provisions do not apply, the arithmetic-mean rule and any applicable notified tolerance must be considered. That tolerance is not an extra margin band added to the percentile range.
The percentile calculation uses the rank-and-average procedure above, with 35 or 65 replacing every 25 or 75 in the respective formula. Expand the cell range to include the complete qualifying dataset. Earlier periods governed by the 1961 Act require the relevant Rule 10CA analysis; identify the tax year before choosing the citation.
Ukraine: prescribed quartiles and self-adjustments
The State Tax Service's explanation of Resolution 381 sets out a rank-based 25th–75th percentile calculation. Retain duplicate observations and exclude the controlled transaction's own result from the comparable dataset.
The Q1 and Q3 rank formulas above implement this arithmetic. Calculate the median from the complete ordered dataset, not from a subset left after trimming.
Adjustment mechanics
Article 39 distinguishes an adjustment using the median from a taxpayer self-adjustment to the relevant range boundary. Self-adjustment must not reduce the tax payable. Check the applicable filing and penalty rules separately; the percentile calculation does not establish penalty relief.
Australia: apply the relevant OECD-consistency rules
Australia's October 2025 OECD country profile, question 10, links range analysis to section 815-125 and consistency with the referenced OECD guidance. This does not establish an automatic preference for Excel INC. Explain why the range and calculation are suitable for the facts.
European Union: check the member state's rules
Do not treat an Excel default as an EU-wide rule. For example, Germany's AStG §1(3a) provides for narrowing where comparability differences remain, with removal of the lowest and highest quarters where no better narrowing is indicated. It also sets a median adjustment rule, unless another point is shown to better satisfy the arm's length principle. Country-specific requirements matter even when countries refer to the same OECD guidance.
Canada: distinguish published guidance from current legislation
CRA memorandum TPM-16 emphasizes qualitative comparability and rejects excluding observations solely because their numerical outcomes are outliers. It also distinguishes useful multi-year context from averaging outcomes to substantiate a particular year's price.
For an outside-range result, paragraph 28 discusses a suitable measure of central tendency and usually the average where comparability gives no further distinction. It does not prescribe an automatic median adjustment.
TPM-16 warns that it has not been updated for subsequent legislative changes. Section 247, as amended in 2026, expressly references the OECD Guidelines in its method-selection framework. Check the legislation applicable to the tax year alongside CRA guidance; avoid an unconditional claim that Canada always requires the full range or prohibits IQR in every context.
Which calculation should a report use?
Context
Calculation or range decision
U.S. regulatory IQR
Apply the prescribed rank-and-average method; establish first whether narrowing is appropriate.
OECD-guided analysis
Justify the range and algorithm under the applicable domestic framework.
India
Use the prescribed 35th–65th percentile range only when its conditions are met.
Ukraine
Apply the prescribed quartile ranks and distinguish self-adjustment from other adjustments.
Australia and EU member states
Check the relevant domestic law; do not assume universal acceptance of a spreadsheet default.
Canada
Read tax-year legislation together with CRA guidance and its stated limitations.
Keep the legal choice of range separate from the mathematical calculation. A narrower interval is not automatically more reliable, and a wider interval is not automatically permissible.
The dataset: selected comparables, exclusions, periods, units and any adjustments or weighting.
The rule: jurisdiction, tax year and legal or administrative basis for the chosen range.
The algorithm: percentile ranks, interpolation or averaging, spreadsheet function and the unrounded outputs.
The conclusion: tested result, both endpoints, whether it falls inside the range, and the separate basis for any adjustment.
When a result is close to an endpoint, verify the underlying data and formula before changing the pricing conclusion. Preserve the calculation precision and show rounding separately. For the broader workflow, see the transfer pricing benchmarking guide.
Frequently Asked Questions
What is the interquartile range (IQR) in transfer pricing?
It commonly means the interval from Q1 to Q3 used in a benchmark. The statistical IQR width is Q3 minus Q1. Report the endpoints when testing a price or margin.
Why do different countries calculate the IQR differently?
Local rules may prescribe different percentile algorithms or different acceptable ranges. “Interquartile” identifies the 25th and 75th percentiles; it does not identify a single calculation algorithm.
How significant are the differences between IQR calculation methods?
They can change a borderline conclusion. In the four-price example above, 43.25 is inside the U.S. regulatory interval and below the Excel INC interval, despite identical inputs.
Should I use the same IQR calculation method for all countries?
Only if the method is appropriate under each relevant framework. Keep the underlying data consistent where appropriate, and document any jurisdiction-specific differences in the range or calculation.
What happens if my tested transaction falls outside the arm's length range?
Review the applicable adjustment rule separately. A median adjustment is not universal, and the midpoint between Q1 and Q3 is not necessarily the median of the dataset.
Should I indicate which IQR calculation method I've used in my documentation?
Yes. Include the dataset, tax year, formula and source for the local rule so a reviewer can reproduce both endpoints and the conclusion.
Can I use Excel's built-in functions for calculating quartiles in all jurisdictions?
No. INC and EXC use different interpolation rules, and neither generally reproduces the U.S. regulatory rank method. Use the function or formula appropriate to the applicable rules.
How does India's narrower range impact transfer pricing compliance?
Where the statutory conditions apply, calculate the 35th and 65th percentiles of the prescribed dataset. A 25th–75th interval is not a substitute for that test.
How does Canada's approach differ from other jurisdictions?
CRA's published guidance emphasizes qualitative comparability. Read it alongside the legislation applicable to the tax year, including the 2026 amendments, rather than assuming an automatic IQR or full-range rule.
Are there any other countries besides India that use non-standard percentile ranges?
Yes. Malaysia's 2024 transfer pricing guidelines describe a 37.5th–62.5th percentile arm's length range. Its conditions and adjustment rules must be checked separately; it is not the conventional 25th–75th interval.