Independent educational model
Executive Deferred Compensation TRS Hedge Model
I built this fictional model around one question: how could a company keep a changing deferred-compensation liability and its hedge aligned?
What I didModel design, settlement logic, and monthly controls
Built withExcel-style model design, ETF proxy analysis, Settlement checks
What came out of itBy the end, I could follow every handoff from plan balances to the trade order and settlement check.
TRS Hedge ModelExecutive deferred compensation
- Data
- Fictional
- Review
- Controls clear
Plan exposure$12.48m
Target notional$11.48m
Hedge ratio92.0%
Reweight order+$301k
| Plan option | Exposure | Target | Order |
|---|---|---|---|
| US large cap | $5.12m | $4.71m | +$171k |
| US small cap | $2.08m | $1.91m | +$94k |
| Core bond | $3.16m | $2.91m | +$27k |
| International | $2.12m | $1.95m | +$9k |
2.4%Unhedged
0.9%Hedged
Inside the model
The model only works if the trail is complete.
A reviewer should be able to move from exposure to order to settlement without guessing where a number came from.$12.48mfictional plan exposure
92.0%target hedge ratio
$11.48mtarget swap notional
4 / 4sample controls passed
TRS Hedge ModelMonthly review
- Model date
- Illustrative
- Data
- Fictional
| Plan option | Exposure | Proxy | Target | Current | Order |
|---|---|---|---|---|---|
| US large cap | $5.12m | SPY | $4.71m | $4.54m | +$171k |
| US small cap | $2.08m | IWM | $1.91m | $1.82m | +$94k |
| Core bond | $3.16m | BND | $2.91m | $2.88m | +$27k |
| International | $2.12m | VEA | $1.95m | $1.94m | +$9k |
| Total | $12.48m | 4 proxies | $11.48m | $11.18m | +$301k |
Settlement
Monthly estimate
- Total-return leg
- $205,712
- Financing expense
- ($58,247)
- Applicable fees
- ($3,500)
- Estimated net settlement
- $143,965
Hedge effectiveness
Unhedged versus residual P&L
JanFebMarAprMayJunJulAugSepOctNovDec
Choosing proxies
ETF mapping review
| Plan option | ETF | Correlation | Tracking error | Fee | Rationale |
|---|---|---|---|---|---|
| US large cap | SPY | 0.99 | 0.48% | 0.09% | High liquidity |
| US small cap | IWM | 0.97 | 1.25% | 0.19% | Broad small-cap proxy |
| Core bond | BND | 0.98 | 0.62% | 0.03% | Diversified bond exposure |
| International | VEA | 0.96 | 1.41% | 0.03% | Developed-market proxy |
Controls
What I would check before sign-off
| Control | Test | Status |
|---|---|---|
| Balance roll-forward | Beginning balance + activity = ending balance | Pass |
| Allocation total | Participant elections total 100% | Pass |
| Proxy and price coverage | No missing mappings or observations | Pass |
| Settlement tolerance | Internal estimate within $25k threshold | Pass |
I built this as an educational example. It contains no real participant, employer, client, or counterparty data.
The question behind it
What I was trying to understand
Participant elections keep changing the liability. At the same time, the hedge has its own return, financing cost, and settlement terms. I wanted to see those moving parts in one place.The standard I set
What a useful answer needed
Every output needed to trace back to an input, and every exception needed a clear place to be reviewed.My part
Where I did the work.
- I set up fictional participant balances, elections, contributions, distributions, and total exposure by plan option.
- I matched the fictional plan options to public ETF proxies and compared correlation, tracking error, fees, liquidity, and distributions.
- I calculated target notional, reweighting orders, the return leg, financing expense, and a simplified settlement.
- I added checks for allocations, prices, mappings, settlement differences, and reviewer sign-off.
Process
How I got to an answer.
Aggregate the liability
I rolled participant elections and plan activity into one exposure total for each plan option.
Map and reweight
I compared ETF proxies, applied the hedge ratio, and calculated the order needed to bring the swap back to target.
Settle and control
I separated return from financing, estimated the settlement, and added checks for anything that fell outside tolerance.
Where judgment entered
- I kept every plan balance fictional and labeled the figures as examples.
- I included distributions in total return instead of looking only at price change.
- I kept proxy quality, hedge results, and control checks on the same monthly review.
Takeaway
What stayed with me
The formulas were the easy part. The real work was making sure another person could trace the result, find an exception, and know what to check next.