Quantitative Finance
Brinson-Fachler Portfolio Attribution: Allocation, Selection & Interaction
Decompose active portfolio excess return into exact Brinson-Fachler Asset Allocation, Security Selection, and Interaction effects across sectors using SUMPRODUCT.
Explain whether active alpha came from sector tilts or stock picking!
Unlike older Brinson-Hood-Beebower attribution, the Brinson-Fachler (1985) model benchmarks each sector tilt (w_p,i - w_b,i) against the sector’s relative outperformance over the OVERALL benchmark return R_B = SUMPRODUCT(w_b, R_b): Allocation_i = (w_p,i - w_b,i) * (R_b,i - R_B). That guarantees overweighting a sector adds positive allocation alpha ONLY when that sector beats the total benchmark—even in down markets!
Summing Allocation + Selection SUMPRODUCT(w_b, R_p - R_b) + Interaction SUMPRODUCT(w_p - w_b, R_p - R_b) reconciles to the exact penny/basis-point with Total Active Return = SUMPRODUCT(w_p, R_p) - SUMPRODUCT(w_b, R_b)!
| Attribution Component | Vector Formula | Contribution (% pts) |
|---|---|---|
| Asset Allocation Effect | SUMPRODUCT(w_p - w_b, R_b - R_B) | +0.3500% |
| Security Selection Effect | SUMPRODUCT(w_b, R_p - R_b) | -0.1500% |
| Interaction Effect | SUMPRODUCT(w_p - w_b, R_p - R_b) | +0.3500% |
| Total Active Return (8.60% - 8.05%) | Allocation + Selection + Interaction | +0.5500% |
Brinson-Fachler Sector Allocation Effect (% Points)
Uses LET variable rbTot for total benchmark return SUMPRODUCT(C2:C4, E2:E4) and validates that portfolio and benchmark weights each sum to 1.
=IF(OR(ABS(SUM(B2:B4) - 1) > 0.0001, ABS(SUM(C2:C4) - 1) > 0.0001), "Weights Must Sum to 1", LET(rbTot, SUMPRODUCT(C2:C4, E2:E4), ROUND(SUMPRODUCT(B2:B4 - C2:C4, E2:E4 - rbTot), 4)))Security Selection Effect & Total Active Return Reconciliation
Measures stock-picking alpha weighted at benchmark sector weights C2:C4.
=ROUND(SUMPRODUCT(C2:C4, D2:D4 - E2:E4), 4)Try the example
Use the original values or change them to test your understanding.