Financial and Risk Management Toolbox: Getting started

Step-by-step walkthrough for solving optimal portfolios, computing down-tail risk, and evaluating asset allocations.

← Overview · Getting started · Modeling guide · Methods guide · Examples · Reference

Introduction

This guide walks you through your first session with the Financial and Risk Management Toolbox. By following these six steps, you will learn how to run the default maximum Sharpe ratio optimization, switch between different portfolio construction and risk tasks, adjust capital market assumptions, customize horizon and confidence settings, interpret published risk/return metrics, and review the efficient frontier and model diagnostics.

Prerequisites

  • Microsoft Excel (desktop or Microsoft 365 web version).
  • Boardflare Python for Excel runtime enabled.
  • Basic familiarity with expected returns, volatilities, and asset allocation concepts.

Step 1: Open the workbook and inspect the baseline solve

When you open financial-risk-toolbox.xlsx, the workbook initializes with default institutional assumptions across six asset classes:

Sheet Key Cells Description
Start B6 = portfolio_opt_sharpe Selects maximum Sharpe ratio optimization.
Start B7 = 0.04, B8 = 0.95, B9 = 1, B10 = 0.08 Sets 4% risk-free rate, 95% confidence, 1-day horizon, 8% target return.
Asset Parameters A5:H11 Asset return, volatility, and allocation bounds for 6 assets.
Asset Parameters J5:P11 Pairwise correlation matrix.

In the reactive runtime, Python executes automatically upon loading. Navigate to the Results sheet to inspect the baseline solution:

  • Optimization 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)
  • Asset Allocations (Results!D5:K11):
    • US Large Cap Equity (US_Equity): 50.00% (constrained by maximum weight cap)
    • International Developed Equity (Intl_Equity): 1.45%
    • Aggregate Fixed Income (Bonds_Agg): 25.02%
    • Public Real Estate REITs (Real_Estate): 11.23%
    • Diversified Commodities (Commodities): 0.00%
    • Cash & Equivalents (Cash_Equiv): 12.30%

Step 2: Switch the active task on the Start sheet

The toolbox supports five distinct tasks managed from cell Start!B6:

portfolio_opt_sharpe     -> Max Sharpe Ratio Allocation
portfolio_min_variance   -> Global Minimum Variance Portfolio
portfolio_target_return  -> Target Return Efficient Portfolio
var_es_historical        -> Value-at-Risk & Expected Shortfall
risk_attribution         -> Marginal Risk & Beta Attribution

Try switching to Global Minimum Variance:

  1. Go to the Start worksheet.
  2. Click cell B6 and select portfolio_min_variance from the drop-down menu.
  3. Switch to the Results sheet. Notice how the portfolio dynamically rebalances:
    • Annualized volatility drops from 9.79% to 3.42% (0.034223).
    • Expected return adjusts to 4.39% (0.043930).
    • Equity exposure is sharply reduced: US Equity drops to 2.85%, while Cash increases to 40.00% and Aggregate Bonds increases to 45.45%.

Try switching to Target Return:

  1. On Start, set cell B6 to portfolio_target_return.
  2. Confirm cell B10 (Target return) is set to 0.07 (7.0%).
  3. Check Results:
    • The solver minimizes volatility subject to meeting or exceeding 7.0% return.
    • Expected return matches 7.00% exactly, with volatility of 7.21% (0.072098).

Step 3: Modify capital market assumptions and correlations

All asset parameters and pairwise correlations are stored directly in worksheet tables:

Modifying Asset Expectations:

  1. Navigate to the Asset Parameters sheet.
  2. Locate table A5:H11. Suppose your investment committee expects higher commodity returns and elevated bond volatility:
    • Change Commodities ExpectedReturn (C10) from 0.06 to 0.09 (9.0%).
    • Change Bonds Volatility (D8) from 0.06 to 0.08 (8.0%).
  3. The optimizer immediately re-evaluates allocations, shifting capital into Commodities up to its allowable limit (MaxWeight = 25%).

Updating the Correlation Matrix:

  1. Examine the correlation table at J5:P11.
  2. Notice that the diagonal entries are locked at 1.00.
  3. If you increase the correlation between US_Equity and Real_Estate in cell M6 from 0.50 to 0.80, make sure to update the symmetric counterpart at cell K9 (Real_Estate vs US_Equity) to 0.80.
  4. Built-in Safety: Even if you introduce minor numerical asymmetry or inconsistent correlation combinations, Python’s spectral clipping algorithm automatically restores positive semi-definiteness (\(\lambda_i \ge 10^{-7}\)), reporting any adjustments in Diagnostics.

Step 4: Adjust risk-free rate, confidence level, and horizon

The toolbox’s risk engine dynamically recalculates down-tail risk metrics according to the parameters defined on Start:

Control Cell Supported Values Interpretation
Risk-free rate B7 Any decimal, e.g. 0.03 to 0.06 Baseline cash hurdle rate used for Sharpe ratio calculation: \((\mu_p - r_f) / \sigma_p\).
Confidence level B8 0.90, 0.95, 0.99 (drop-down) One-tailed confidence probability \(\alpha\). Default is 95%.
Horizon (days) B9 Integer \(\ge 1\) (e.g., 1, 5, 10, 21) Holding period \(h\). Down-tail metrics scale using square-root of time \(\sqrt{h/252}\).

Example: Regulatory 10-day 99% VaR

  1. Set Confidence level (B8) to 0.99.
  2. Set Horizon (days) (B9) to 10.
  3. Check Results!B12: The metric label updates to Parametric VaR (10d, 99%), and the calculated loss threshold scales from 1.02% (1-day 95%) to 4.21% (0.042080).

Step 5: Read and interpret Results summary metrics and allocation shares

The Results sheet provides two synchronized output blocks published by Python:

1. Portfolio Summary Block (Results!A5:B17)

  • Active strategy (B6): Human-readable name of the executed strategy (e.g., Max Sharpe Ratio Allocation).
  • Family (B7): Strategy category (Optimization or Risk).
  • Status (B8): OPTIMAL, CONVERGED, INFEASIBLE, or FAILED.
  • Expected Return (Annual) (B9): Annualized portfolio expected return: \(w^T \mu\).
  • Annualized Volatility (B10): Annualized portfolio standard deviation: \(\sqrt{w^T \Sigma w}\).
  • Sharpe Ratio (B11): Excess return over risk-free rate per unit volatility.
  • Parametric VaR (B12): Gaussian analytical Value-at-Risk over horizon \(h\).
  • Student-t VaR (B13): Fat-tailed VaR accounting for kurtosis with \(\nu = 5\) degrees of freedom.
  • Historical VaR (B14): Non-parametric percentile loss computed across 36 historical monthly returns.
  • Expected Shortfall (CVaR) (B15): Average expected loss conditional on exceeding the VaR cutoff.
  • Portfolio Beta (vs Bmk) (B16): Covariance of portfolio monthly returns with the Benchmark divided by Benchmark variance.
  • Tracking Error (Ann.) (B17): Annualized standard deviation of active returns \((r_{p,t} - r_{b,t})\).

2. Asset Allocations & Risk Shares (Results!D5:K11)

  • Weight: Optimal decision weight \(w_i\).
  • BenchmarkWeight: Policy benchmark weight \(w_{b,i}\).
  • ActiveWeight: Active tilt: \(w_i - w_{b,i}\).
  • MarginalRisk (MCR): Marginal contribution to risk \(\partial \sigma_p / \partial w_i = (\Sigma w)_i / \sigma_p\).
  • RiskSharePct: Percentage of total portfolio risk contributed by the asset: \((w_i \cdot \text{MCR}_i / \sigma_p) \times 100\%\). The sum across all assets equals 100% exactly (Euler’s homogeneous function theorem).

Step 6: Explore the efficient frontier and diagnostic checks

Exploring the Efficient Frontier (Analysis sheet)

Navigate to the Analysis sheet. The table at A5:F17 contains a 12-point parametric sweep across the efficient frontier: - FrontierPoint: Points P1 (Global Minimum Variance) through P12 (Maximum Return). - Return & Volatility: The optimal risk-return tradeoff locus. - SharpeRatio: Efficiency at each point along the curve. - DominantAsset: The asset receiving the largest allocation at that frontier point. - DiversificationRatio: Ratio of weighted average asset volatilities to portfolio volatility \(\sum w_i \sigma_i / \sigma_p\). Values \(> 1.0\) indicate diversification benefit.

The embedded Marimo chart visually plots the frontier curve, highlighting your current portfolio’s position relative to the policy benchmark.

Reviewing Integrity Diagnostics (Diagnostics sheet)

Navigate to Diagnostics (A5:C11). The toolbox runs six automated compliance checks every time calculations execute:

Check Expected Severity Description
Full investment budget PASS Confirms \(\sum w_i = 1.000000\) (deviation \(\le 10^{-4}\)).
Box constraint feasibility PASS Confirms all weights satisfy \(l_i \le w_i \le u_i\). If user sets \(l_i > u_i\), flags FAIL.
Covariance conditioning & PSD PASS Verifies covariance condition number \(< 10^5\) and that matrix is strictly positive semi-definite.
VaR down-tail coverage & fat tails PASS Quantifies the fat-tail premium between Student-t VaR and Gaussian VaR.
Kupiec POF backtest validation PASS Tests whether empirical exceedances over 36 months are consistent with the theoretical confidence level (\(p \ge 0.05\)).
Benchmark tracking & active risk PASS Confirms annualized tracking error is within institutional limits (\(< 25\%\)).

Next steps

  • Explore the Modeling guide for detailed schemas, box constraint bounds, and custom universe expansion up to 15 assets.
  • Review the Methods guide for mathematical derivations of SLSQP optimization, Kupiec backtesting, and Student-t scaling.
  • Study Worked examples for five concrete institutional investment use cases.