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.
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).
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
Measure
Value
Benchmark return
9.89%
Portfolio return
10.84%
Active return
0.95 points
Allocation
0.6400 points
Selection
0.2950 points
Interaction
0.0150 points
Allocation from the bond underweight
0.3327 points
Allocation from the equity overweight
0.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
On Attribution, type each segment’s name, benchmark weight, portfolio weight, benchmark return and portfolio return over the blue cells, in per cent.
Use average weights over the period if the portfolio moved during it; beginning weights if it did not trade.
Check that both weight columns add up to 100 per cent: the Checks sheet tests it.
Read the totals and the shares. A large interaction usually means the overweights were also where the manager picked well, or badly.
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.
How do you calculate active share of a fund?Half the sum of absolute weight differences against the index. An illustrative fund scores 72.5%, so its 0.75% fee is 1.02% on the active part.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
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.