Free Excel templates

A free Brinson attribution model in Excel: allocation, selection and interaction

Active return split segment by segment into allocation, selection and interaction on the Brinson-Fachler method, with the Brinson-Hood-Beebower allocation alongside: up to twelve segments, one period, every effect a live formula, and a reconciliation that has to close to the last decimal.

The Brinson model splits a portfolio’s active return into what came from the weights and what came from the returns within each segment. Allocation is (portfolio weight − benchmark weight) × (segment benchmark return − total benchmark return); selection is benchmark weight × (portfolio segment return − segment benchmark return); interaction is the product of the two differences. On Marchwood, the illustrative pension mandate from the book, the portfolio returned 10.84 per cent against 9.89 for the benchmark, and the file returns allocation of 0.6400, selection of 0.2950 and interaction of 0.0150 points, which add up exactly to the 0.95 points of active return.

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 129 free models.

Brinson_Attribution_Model.xlsx (12 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 one worked case from Asset Management. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 36 paperbacks by Julian R. Sterling on how to read the documents, printed on demand and shipped worldwide. Browse the bookshop →

The three effects, segment i

Allocation = (wp,i − wb,i) × (rb,i − Rb)

Selection = wb,i × (rp,i − rb,i)

Interaction = (wp,i − wb,i) × (rp,i − rb,i)

What is in the file

3 sheets. Blue type on a pale blue fill is an input; black type is a formula.

Read Me
What the file does, the worked case, how to enter your own segments, and why several periods need a linking method.
Attribution
Up to twelve segments: benchmark and portfolio weights and returns in blue, then the weight difference, each segment’s return against the total benchmark, and the three effects. The Brinson-Hood-Beebower allocation, (wp − wb) × rb, sits in the last column. Totals, the active return and the share from each effect below.
Checks
Four identities that hold for any inputs (weights add to 100 per cent, the effects add to the active return, both allocation methods have the same total), then the worked case. The file ends ALL OK.

What the worked case returns

MeasureValue
Benchmark return9.89%
Portfolio return10.84%
Active return0.95 points
Allocation0.6400 points
Selection0.2950 points
Interaction0.0150 points
Allocation from the bond underweight0.3327 points
Allocation from the equity overweight0.2553 points

Two thirds of the outperformance is allocation, and its largest piece is not the equity overweight but the bond underweight: government bonds lost 1.2 per cent in a year when the benchmark made 9.89, so being three points light in them was worth 0.3327. On the Brinson-Hood-Beebower allocation the same underweight shows as a small positive and the equity overweight as a large one; the totals agree, the segment story does not. That is why the file shows both, and why the Brinson-Fachler column is the one to read segment by segment.

How to use it on your own figures

  1. On Attribution, type each segment’s name, benchmark weight, portfolio weight, benchmark return and portfolio return over the blue cells, in per cent.
  2. Use average weights over the period if the portfolio moved during it; beginning weights if it did not trade.
  3. Check that both weight columns add up to 100 per cent: the Checks sheet tests it.
  4. Read the totals and the shares. A large interaction usually means the overweights were also where the manager picked well, or badly.
  5. For several periods, run one copy per period. Adding single-period effects does not reconcile to the compounded active return; that needs a linking method such as Carino or Menchero.

Questions people ask about it

What is the Brinson model?

A method, from Brinson, Hood and Beebower (1986) and Brinson and Fachler (1985), that splits a portfolio’s return against its benchmark into an allocation effect from the weights, a selection effect from the returns within each segment, and an interaction effect.

What is the difference between Brinson-Fachler and Brinson-Hood-Beebower?

Only the allocation formula. BHB uses (wp − wb) × rb; Brinson-Fachler subtracts the total benchmark return, so an overweight in a segment that lagged the benchmark counts as negative. The totals are identical, 0.6400 points here; the segment figures differ.

Do the effects always add up to the active return?

In a single period, yes, exactly: 0.6400 + 0.2950 + 0.0150 = 0.9500 points. The Checks sheet tests it. Over several periods the sums need linking.

Is this Brinson attribution template really free?

Yes. It is a companion file to a book. You enter an email address, the download opens at once, and you can leave the reader list in one click. It has no macros.

Where do the figures come from?

From Marchwood, the illustrative multi-asset pension mandate in the book’s companion workbooks, year 1. The mandate is fictional, and nothing here is investment advice.

The rest of the files

This template sits alongside the companion files for Asset Management: the workbooks behind every chapter, the worked cases and the checklists. Free, like this one.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from Asset Management. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.