Financial and Risk Management Toolbox: Worked examples
Five institutional portfolio construction, down-tail risk, target return, and risk attribution walkthroughs.
← Overview · Getting started · Modeling guide · Methods guide · Examples · Reference
Overview
This guide provides five complete institutional worked examples demonstrating the practical application of the Financial and Risk Management Toolbox. Each example details the business objective, workbook setup, numerical results, and operational takeaways.
Example 1: Institutional Multi-Asset Allocation (Max Sharpe)
Objective
An endowment fund seeks to construct an optimal long-term strategic asset allocation across equities, fixed income, real estate, and commodities that maximizes excess return per unit of volatility relative to a 4.0% cash risk-free hurdle.
Workbook Setup
- Start Worksheet:
Active task(B6):portfolio_opt_sharpeRisk-free rate(B7):0.04(4.0%)Confidence level(B8):0.95(95%)Horizon (days)(B9):1
- Asset Parameters Worksheet: Default baseline universe (US Equity capped at 50%, Intl Equity at 50%, Bonds at 60%, Real Estate at 30%, Commodities at 25%, Cash at 40%).
Solution & Results
Navigate to Results to view the optimal solution:
- Status (
Results!B8):OPTIMAL - Expected Return (
Results!B9):8.25%(0.082544) - Annualized Volatility (
Results!B10):9.79%(0.097871) - Sharpe Ratio (
Results!B11):0.435(0.434690) - Parametric VaR (1d, 95%):
1.02%(0.010211) - Student-t VaR (1d, 95%):
1.17%(0.011743) - Expected Shortfall (CVaR):
1.29%(0.012886)
Optimal Asset Allocations (Results!D5:K11)
| Asset | Weight (\(w_i\)) | Benchmark (\(w_{b,i}\)) | Active Tilt | MCR | Risk Share (%RC) |
|---|---|---|---|---|---|
US_Equity |
50.00% | 40.00% | +10.00% | 0.1348 | 68.85% |
Intl_Equity |
1.45% | 20.00% | -18.55% | 0.1251 | 1.85% |
Bonds_Agg |
25.02% | 25.00% | +0.02% | 0.0255 | 6.52% |
Real_Estate |
11.23% | 5.00% | +6.23% | 0.1172 | 13.44% |
Commodities |
0.00% | 5.00% | -5.00% | 0.0822 | 0.00% |
Cash_Equiv |
12.30% | 5.00% | +7.30% | 0.0074 | 9.33% |
Key Takeaways:
- US Large Cap Equity hits its 50.0% institutional ceiling due to its high Sharpe ratio (\(11\% - 4\% = 7\%\) excess return for \(16\%\) volatility).
- Although US Equity comprises 50% of capital, it generates 68.85% of the portfolio’s total risk.
- Fixed income and cash provide substantial diversification benefits, pulling total portfolio volatility down to 9.79% despite high equity exposure.
Example 2: Conservative Capital Preservation (Global Minimum Variance)
Objective
A wealth manager managing an ultra-conservative retiree portfolio wants to minimize total volatility, eliminating equity downside exposure without holding 100% cash.
Workbook Setup
- Start Worksheet:
- Change
Active task(B6) to:portfolio_min_variance - Retain default risk-free rate (
0.04) and confidence settings.
- Change
Solution & Results
- Status (
Results!B8):OPTIMAL - Expected Return (
Results!B9):4.39%(0.043930) - Annualized Volatility (
Results!B10):3.42%(0.034223) - Sharpe Ratio (
Results!B11):0.115(0.114840) - Parametric VaR (1d, 95%):
0.35%(0.003534) - Expected Shortfall (CVaR):
0.45%(0.004454)
Optimal Asset Allocations (Results!D5:K11)
| Asset | Weight (\(w_i\)) | Benchmark | Active Tilt | Marginal Risk (MCR) | Risk Share (%RC) |
|---|---|---|---|---|---|
US_Equity |
2.85% | 40.00% | -37.15% | 0.0342 | 2.85% |
Intl_Equity |
0.00% | 20.00% | -20.00% | 0.0451 | 0.00% |
Bonds_Agg |
45.45% | 25.00% | +20.45% | 0.0342 | 45.45% |
Real_Estate |
0.00% | 5.00% | -5.00% | 0.0454 | 0.00% |
Commodities |
11.70% | 5.00% | +6.70% | 0.0342 | 11.70% |
Cash_Equiv |
40.00% | 5.00% | +35.00% | 0.0342 | 40.00% |
Key Takeaways:
- Portfolio volatility is reduced from 9.79% to 3.42%, a 65% risk reduction.
- Cash saturates its maximum allowable ceiling (40.00%).
- Remarkably, the optimizer allocates 11.70% to Commodities despite its 20% standalone volatility, because Commodities’ negative correlation with Aggregate Bonds (\(-0.10\)) and low correlation with Cash (\(-0.05\)) creates a variance-dampening hedge.
- In the GMV portfolio, all actively held unconstrained assets exhibit identical marginal contribution to risk (\(\text{MCR} = 0.0342\)).
Example 3: Pension Liability Matching with 7.0% Target Return
Objective
A defined benefit pension plan needs an asset allocation that delivers an expected return of at least 7.0% to match actuarial liability discounting, while minimizing portfolio volatility.
Workbook Setup
- Start Worksheet:
- Change
Active task(B6) to:portfolio_target_return - Set
Target return(B10) to:0.07(7.00%)
- Change
Solution & Results
- Status (
Results!B8):OPTIMAL - Expected Return (
Results!B9):7.00%(0.070000) - Annualized Volatility (
Results!B10):7.21%(0.072098) - Sharpe Ratio (
Results!B11):0.416(0.416098) - Parametric VaR (1d, 95%):
0.75%(0.007481) - Expected Shortfall (CVaR):
0.95%(0.009476)
Optimal Asset Allocations (Results!D5:K11)
| Asset | Weight (\(w_i\)) | Benchmark | Active Tilt | MCR | Risk Share (%RC) |
|---|---|---|---|---|---|
US_Equity |
34.79% | 40.00% | -5.21% | 0.1064 | 51.35% |
Intl_Equity |
0.00% | 20.00% | -20.00% | 0.1009 | 0.00% |
Bonds_Agg |
37.79% | 25.00% | +12.79% | 0.0279 | 14.61% |
Real_Estate |
10.02% | 5.00% | +5.02% | 0.0911 | 12.65% |
Commodities |
0.00% | 5.00% | -5.00% | 0.0614 | 0.00% |
Cash_Equiv |
17.40% | 5.00% | +12.40% | 0.0119 | 21.39% |
Key Takeaways:
- The target return constraint is satisfied exactly at \(7.0000\%\).
- Volatility is reduced to 7.21% (compared to 9.79% in the unconstrained Max Sharpe portfolio), providing a conservative asset mix that precisely meets the actuarial requirement.
- If the committee were to test an unattainable return (e.g. \(12.0\%\), exceeding the highest-returning asset’s \(11.0\%\)), the solver immediately flags
Results!B8asFAILEDand diagnostics alert the reviewer.
Example 4: Down-Tail Risk Assessment & Kupiec POF Model Validation
Objective
A chief risk officer must compute Basel regulatory 10-day 99% Value-at-Risk across current portfolio holdings and backtest the risk engine against empirical monthly history using the Kupiec Proportion of Failures likelihood ratio test.
Workbook Setup
- Start Worksheet:
Active task(B6):var_es_historicalConfidence level(B8):0.99(99% one-tailed confidence)Horizon (days)(B9):10(regulatory 10-day period)
- Asset Parameters Worksheet: The model evaluates
CurrentWeight(A5:H11), reflecting current holdings (\(35\%\) US Equity, \(20\%\) Intl Equity, \(25\%\) Bonds, \(10\%\) REITs, \(5\%\) Commodities, \(5\%\) Cash).
Solution & Results
- Status (
Results!B8):OPTIMAL - Expected Return (
Results!B9):7.42%(0.074250) - Annualized Volatility (
Results!B10):8.44%(0.084365) - Results!A12 Label:
Parametric VaR (10d, 99%) - Parametric VaR (
Results!B12):3.63%(0.036294) - Student-t VaR (
Results!B13):4.45%(0.044521) - Historical VaR (
Results!B14):4.16%(0.041639) - Expected Shortfall (CVaR) (
Results!B15):4.57%(0.045741)
Model Validation Diagnostics (Diagnostics!A5:C11)
- VaR down-tail coverage & fat tails (
Diagnostics!A8:C8):- Severity:
PASS - Detail:
10d 99% VaR: Parametric=3.63%, Student-t=4.45% (gap: 0.82%). - Interpretation: Heavy tails account for an additional 82 basis points of tail risk beyond the standard Gaussian distribution.
- Severity:
- Kupiec POF backtest validation (
Diagnostics!A9:C9):- Severity:
PASS - Detail:
0 breach(es) observed in 36 monthly periods (p-value: 0.4623). - Interpretation: Over the 36-month backtest window, zero exceedances of the 99% monthly VaR threshold were recorded. The Kupiec test p-value of 0.4623 (\(> 0.05\)) confirms the risk model passes regulatory backtesting standards.
- Severity:
Example 5: Active Management Tracking Error & Factor Risk Attribution
Objective
An institutional equity portfolio manager evaluates active risk against the strategic policy benchmark (\(40\%\) US Equity, \(20\%\) Intl Equity, \(25\%\) Bonds, \(5\%\) REITs, \(5\%\) Commodities, \(5\%\) Cash) to decompose tracking error and marginal factor exposures.
Workbook Setup
- Start Worksheet:
Active task(B6):risk_attribution- Retain standard 95% confidence and 1-day horizon.
Solution & Results
- Status (
Results!B8):OPTIMAL - Portfolio Beta vs Benchmark (
Results!B16):0.9693 - Annualized Tracking Error (
Results!B17):0.76%(0.007563) - Active Return: \(+0.55\%\)
- Information Ratio: \(0.72\)
Active Weights and Marginal Risk Attribution (Results!D5:K11)
| Asset | Portfolio (\(w_i\)) | Benchmark (\(w_{b,i}\)) | Active Weight | ExpReturn | Volatility | MarginalRisk | RiskSharePct |
|---|---|---|---|---|---|---|---|
US_Equity |
35.00% | 40.00% | -5.00% | 11.00% | 16.00% | 0.1254 | 52.02% |
Intl_Equity |
20.00% | 20.00% | 0.00% | 9.00% | 18.00% | 0.1238 | 29.35% |
Bonds_Agg |
25.00% | 25.00% | 0.00% | 4.50% | 6.00% | 0.0247 | 7.33% |
Real_Estate |
10.00% | 5.00% | +5.00% | 8.00% | 15.00% | 0.0768 | 9.10% |
Commodities |
5.00% | 5.00% | 0.00% | 6.00% | 20.00% | 0.0354 | 2.10% |
Cash_Equiv |
5.00% | 5.00% | 0.00% | 3.50% | 2.00% | 0.0016 | 0.10% |
Key Takeaways:
- The active tilt is focused: an underweight of \(-5.0\%\) in US Large Cap Equity offset by an overweight of \(+5.0\%\) in Real Estate REITs.
- This tilt generates an annualized tracking error of 0.76% against the benchmark with an Information Ratio of 0.72.
- Even with a reduced allocation of 35%, US Equity still contributes 52.02% of total portfolio volatility.
- The portfolio beta of 0.9693 indicates market-neutral alignment with slight defensive tilt relative to the benchmark index.