List the comparables your study accepted, and the file computes each company's weighted average margin, the interquartile range and the median by the counting rule of the US regulation, places your tested party, and gives the true-up to the median or to the nearest edge. Four sheets, no macros.
The example is the book's fictional case: 14 accepted distributors with three years of data, an interquartile range of 2.44 to 4.18 percent and a median of 3.04 percent. Harnwick Europe, a limited-risk distributor, earned $1,074,631 on net sales of $118,400,000, an operating margin of 0.91 percent. It falls below the range; bringing it to the median takes $2,521,920, and to the 25th percentile $1,811,520.
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other free models.
Harnwick_Transfer_Pricing_Benchmark_Template.xlsx (16 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
This file is a simplified copy of one of the five workbooks of The Transfer Pricing Professional. The book follows the same group for two years: the functional analysis, the choice of method and tested party, the study and its screens, the policy and the year-end true-up, services, loans and royalties, the documentation that protects against penalties, an audit abroad and a decision priced over three years.
DRM-free PDF, $19.99 → · Paperback, $32.99, shipped worldwide
Four sheets. Blue cells on a light blue fill are inputs; black cells are formulas.
| Measure | Value |
|---|---|
| Accepted comparables | 14 |
| 25th percentile (result 4 of 14) | 2.44% |
| Median (average of results 7 and 8) | 3.04% |
| 75th percentile (result 11 of 14) | 4.18% |
| Tested party's operating margin | 0.91% |
| True-up to the median | $2,521,920 |
| True-up to the 25th percentile | $1,811,520 |
With 14 results, 25 percent of 14 is 3.5, so the 25th percentile is the 4th result; 50 percent is exactly 7, so the median is the average of the 7th and 8th results, 3.00 and 3.07 percent; 75 percent is 10.5, so the 75th percentile is the 11th result. Remove one company and the positions move: with 13 results the median is the 7th result alone. The book's 2025 roll-forward does exactly that, and its range becomes 2.61 to 4.27 percent.
Yes. It is a companion file of a book. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Excel's quartile functions interpolate between results. The US regulation counts instead: the 25th percentile is the lowest result with at least 25 percent of the results at or below it, and when exactly 25 percent sit at or below a result, the average of that result and the next one. The template applies that rule with plain formulas.
No macros and no circular reference. Inputs are blue cells; everything else is a formula, and a Checks sheet compares the outputs with the figures printed in the book.
You can reuse the structure and replace every input. The 14 companies and the tested party are fictional teaching figures, not market data, and nothing here is tax or legal advice.