Free Excel templates

A free transfer pricing benchmark template in Excel, with a worked case

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.

Get the template

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.

The book behind this model

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

What is in the file

Four sheets. Blue cells on a light blue fill are inputs; black cells are formulas.

Read me
What the file does, the counting rule in words, how to replace the example with your own study, and where the full workbooks are.
Comparables
Up to 25 companies with three years of net sales and operating profit, an Accepted column (type No to leave a company out without deleting its line), and each company's weighted average operating margin: total profit over total sales.
Range
The number of accepted results, the 25th percentile, the median and the 75th percentile with the position used for each, the tested party's margin, where it falls, the true-up to the median and to the nearest edge, and the sorted results.
Checks
Each output set against the figure printed in the book, with a pass or fail verdict and a guard on every division.

What the worked case shows

MeasureValue
Accepted comparables14
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 margin0.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.

How to use it on your own study

  1. On Comparables, overwrite the example companies with the ones your search accepted, three years each. Type No in Accepted for any company you reject later, and keep the line: the rejection log stays in the file.
  2. On Range, enter your tested party's net sales and operating profit for the year under review.
  3. Read where the tested party falls, then the two true-up lines. Which point your policy targets is a decision for the group and its advisers, not for the file.
  4. Keep the Checks sheet: once you change an input it stops matching the book, which is expected.

Questions people ask about it

Is this transfer pricing template really free?

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.

Why not use the QUARTILE function of Excel?

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.

Does it use macros?

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.

Can I use it for a real study?

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.

Related reading