Portfolio Performance Attribution: TWR, Benchmarks & Brinson Allocation/Selection

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Split one client account's year into five sub-periods at every deposit and withdrawal, calculate each sub-period's return and link them into the time-weighted return.
• Turn the account's deposits and withdrawals into the investor's cash flows, calculate the money-weighted return with XIRR and compare it with the time-weighted return from lab 1.
• Compare twelve monthly fund returns with the benchmark, link both into annual returns, and measure how far and how consistently the fund deviates: tracking error and information ratio.
• Aggregate the fund's twelve holdings into six sectors with SUMIF and SUMPRODUCT, so the fund and the benchmark sit side by side: weight and return per sector, plus both total returns.
• Measure how much of the fund's active return came from over- and underweighting sectors relative to the benchmark: the Brinson-Fachler allocation effect, sector by sector.
• Add the selection and interaction effects to the allocation effect and prove that, together, they explain the fund's active return to the last basis point.
• Link four quarters of returns, see why the quarterly effects no longer add up to the annual excess return, measure the geometric excess and rescale the effects so the annual report ties out.
• Finish the quarterly attribution report: total effect per sector, column totals that tie to the active return, the top contributor and largest detractor, and a bar chart of the effects by sector.