Financial and Risk Management Toolbox: Modeling guide

Complete input schemas, asset parameter tables, correlation matrix structure, and constraint semantics.

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

Introduction

This guide documents the data modeling schemas, parameter conventions, constraint rules, and solver status classifications implemented in the Financial and Risk Management Toolbox. The workbook organizes institutional portfolio modeling into clean, auditable worksheet ranges that feed directly into a Python computational engine.

1. Start Worksheet Schema

The Start worksheet establishes global runtime controls and catalogs the supported quantitative strategy models.

Portfolio & Risk Controls Table (`Start!A5:B10`)

Control Name	Address	Type	Permitted Values	Default	Semantic Description
Active task	`Start!B6`	String	Model key from library (see catalog below)	`portfolio_opt_sharpe`	Selects which optimization or risk estimation algorithm to execute.
Risk-free rate	`Start!B7`	Float	\(\ge 0.0\) (e.g., `0.04` or `4.0%`)	`0.04` (4.0%)	Annualized risk-free hurdle rate \(r_f\) used in Sharpe ratio calculation: \((\mu_p - r_f)/\sigma_p\).
Confidence level	`Start!B8`	Float	`0.90`, `0.95`, `0.99` (dropdown)	`0.95` (95%)	One-tailed cumulative probability \(\alpha\) for Value-at-Risk and Expected Shortfall estimation.
Horizon (days)	`Start!B9`	Integer	Integer \(\ge 1\)	`1`	Evaluation time horizon \(h\) in trading days. Down-tail risk metrics scale via \(\sqrt{h/252}\).
Target return	`Start!B10`	Float	Decimal \(\ge 0.0\) (e.g., `0.08` or `8.0%`)	`0.08` (8.0%)	Target annual portfolio return hurdle enforced when `Active task` is `portfolio_target_return`.

Strategy Library Catalog (`Start!D5:H10`)

The strategy catalog lists all available analytical tasks, their mathematical family, underlying numerical solver algorithm, and functional description:

Model ID (`Model`)	Label (`Label`)	Family (`Family`)	Algorithm (`Algorithm`)	Description
`portfolio_opt_sharpe`	Max Sharpe Ratio Allocation	Optimization	SLSQP	Maximize expected portfolio excess return per unit volatility subject to bounds and full investment.
`portfolio_min_variance`	Global Minimum Variance Portfolio	Optimization	SLSQP	Construct minimum volatility portfolio subject to full investment budget and asset bounds.
`portfolio_target_return`	Target Return Efficient Portfolio	Optimization	SLSQP	Minimize portfolio variance subject to achieving the specified annual target return hurdle.
`var_es_historical`	Value-at-Risk & Expected Shortfall	Risk	Quantile	Parametric (Gaussian and Student-t) and empirical historical down-tail quantile estimation.
`risk_attribution`	Marginal Risk & Beta Attribution	Risk	Euler	Decompose risk contributions, benchmark beta, active return, and tracking error across assets.

2. Asset Parameters Worksheet Schema

The Asset Parameters worksheet defines the asset universe, individual risk/return parameters, allocation bounds, and pairwise correlation structure.

Asset Universe Table (`Asset Parameters!A5:H20`)

The standard template includes six institutional asset classes and supports expansion up to 15 assets (rows 5 to 20):

Column Header	Field Name	Data Type	Permitted Range	Description
Asset	`Asset`	String	Unique alphanumeric identifier (no spaces recommended)	Unique ticker or class identifier (e.g., `US_Equity`, `Bonds_Agg`). Joins with correlation matrix and historical returns.
Name	`Name`	String	Text	Descriptive human-readable label (e.g., `US Large Cap Equity`).
ExpectedReturn	\(\mu_i\)	Float	\(-1.0 \le \mu_i \le 2.0\)	Annualized expected arithmetic mean return. Formatted as decimal or percentage.
Volatility	\(\sigma_i\)	Float	\(\sigma_i > 0.0\)	Annualized standard deviation of asset returns. Strictly positive.
MinWeight	\(l_i\)	Float	\(0.0 \le l_i \le 1.0\)	Minimum permitted portfolio allocation bound (box lower constraint). Defaults to `0.0` (no short sales).
MaxWeight	\(u_i\)	Float	\(0.0 \le u_i \le 1.0\)	Maximum permitted portfolio allocation bound (box upper constraint). Must satisfy \(u_i \ge l_i\).
BenchmarkWeight	\(w_{b,i}\)	Float	\(0.0 \le w_{b,i} \le 1.0\)	Strategic policy benchmark weight. Used to compute active tilt (\(w_i - w_{b,i}\)) and benchmark characteristics.
CurrentWeight	\(w_{0,i}\)	Float	\(0.0 \le w_{0,i} \le 1.0\)	Current portfolio holdings. Evaluated directly when running `var_es_historical` or `risk_attribution`.

Default Asset Parameter Values:

Asset	Name	ExpectedReturn	Volatility	MaxWeight	BenchmarkWeight	CurrentWeight
`US_Equity`	US Large Cap Equity	0.110 (11.0%)	0.160 (16.0%)	0.50 (50.0%)	0.40 (40.0%)	0.35 (35.0%)
`Intl_Equity`	Intl Developed Equity	0.090 (9.0%)	0.180 (18.0%)	0.50 (50.0%)	0.20 (20.0%)	0.20 (20.0%)
`Bonds_Agg`	Aggregate Fixed Income	0.045 (4.5%)	0.060 (6.0%)	0.60 (60.0%)	0.25 (25.0%)	0.25 (25.0%)
`Real_Estate`	Public Real Estate (REIT)	0.080 (8.0%)	0.150 (15.0%)	0.30 (30.0%)	0.05 (5.0%)	0.10 (10.0%)
`Commodities`	Diversified Commodity Index	0.060 (6.0%)	0.200 (20.0%)	0.25 (25.0%)	0.05 (5.0%)	0.05 (5.0%)
`Cash_Equiv`	Cash & Equivalents	0.035 (3.5%)	0.020 (2.0%)	0.40 (40.0%)	0.05 (5.0%)	0.05 (5.0%)

Durable Correlation Matrix (`Asset Parameters!J5:P20`)

The correlation matrix is maintained directly in worksheet cells rather than hidden in code. This provides complete transparency for investment committees while guaranteeing strict mathematical properties:

          J            K             L            M            N            O            P
4    [ Asset correlation matrix ]
5    Asset         US_Equity    Intl_Equity    Bonds_Agg   Real_Estate  Commodities   Cash_Equiv
6    US_Equity       1.00          0.70          0.10         0.50         0.30         0.00
7    Intl_Equity     0.70          1.00          0.15         0.55         0.40         0.00
8    Bonds_Agg       0.10          0.15          1.00         0.20        -0.10         0.05
9    Real_Estate     0.50          0.55          0.20         1.00         0.25         0.00
10   Commodities     0.30          0.40         -0.10         0.25         1.00        -0.05
11   Cash_Equiv      0.00          0.00          0.05         0.00        -0.05         1.00

Mathematical Properties Enforced by the Runtime:

Unit Diagonal: The correlation of any asset with itself is identically \(C_{ii} = 1.00\).
Symmetry: Pairwise correlation is symmetric: \(C_{ij} = C_{ji}\). The Python ingestion layer enforces symmetry via \(\mathbf{C} = \frac{1}{2}(\mathbf{C} + \mathbf{C}^T)\).
Positive Semi-Definiteness (PSD): A valid correlation matrix must have non-negative eigenvalues: \(\lambda_i \ge 0\). If user adjustments yield negative eigenvalues (e.g. from inconsistent pairwise estimates), the runtime clips eigenvalues to \(10^{-7}\) and rescales diagonal entries to unity, logging a warning in Diagnostics.

3. Historical Returns Worksheet Schema (`Historical Returns!A5:H45`)

The Historical Returns sheet contains empirical monthly return observations spanning 36 periods (January 2024 through December 2026) for all six universe assets and the policy Benchmark:

Column	Header	Type	Description
`A`	`Period`	String	Observation period identifier in `YYYY-MM` format (e.g., `2024-01`).
`B`	`US_Equity`	Float	Monthly return of US Large Cap Equity.
`C`	`Intl_Equity`	Float	Monthly return of International Developed Equity.
`D`	`Bonds_Agg`	Float	Monthly return of Aggregate Fixed Income.
`E`	`Real_Estate`	Float	Monthly return of Public Real Estate REITs.
`F`	`Commodities`	Float	Monthly return of Diversified Commodities.
`G`	`Cash_Equiv`	Float	Monthly return of Cash & Equivalents.
`H`	`Benchmark`	Float	Monthly return of the multi-asset Strategic Policy Benchmark.

Operational Roles of the Historical Series:

Empirical Historical VaR & CVaR: Portfolio returns are simulated historically as \(r_{p,t} = \sum_{i=1}^n w_i r_{i,t}\) for \(t = 1, \dots, 36\). The empirical \(\alpha\)-quantile provides non-parametric VaR without assuming Gaussian normality.
Kupiec POF Backtesting: Historical monthly portfolio returns are evaluated against the theoretical monthly VaR cutoff to count exceedances (\(r_{p,t} < -\text{VaR}_{1\text{m}}\)) and calculate the likelihood ratio test statistic.
Active Tracking & Beta: Sample covariance between \(r_{p,t}\) and \(r_{b,t}\) determines empirical portfolio beta \(\beta_p = \text{Cov}(r_p, r_b) / \text{Var}(r_b)\), annualized tracking error \(\text{TE} = \sqrt{12} \cdot \text{std}(r_p - r_b)\), and Information Ratio \(\text{IR} = (\mu_p - \mu_b) / \text{TE}\).

4. Constraint Semantics and Feasibility Criteria

The optimization algorithms enforce three categories of constraints:

1. Budget Constraint (Full Investment)

The portfolio must be 100% invested with zero unallocated capital: \[\sum_{i=1}^n w_i = 1.0\] In the solver, this is formulated as an equality constraint: \(g_{\text{budget}}(w) = \sum_{i=1}^n w_i - 1.0 = 0\).

2. Box Constraints (Asset Allocation Limits)

Each asset allocation weight \(w_i\) is constrained within institutional upper and lower bounds: \[l_i \le w_i \le u_i, \quad \forall i = 1, \dots, n\] - For long-only portfolios: \(l_i \ge 0.0\). - To cap exposure in a volatile asset (e.g. commodities): \(u_i \le 0.25\). - To enforce mandatory minimum exposure: \(l_i > 0.0\).

3. Target Return Constraint

When running portfolio_target_return, the portfolio expected return must meet or exceed the hurdle rate set in Start!B10: \[w^T \mu = \sum_{i=1}^n w_i \mu_i \ge r_{\text{target}}\] This is implemented as an inequality constraint: \(g_{\text{target}}(w) = w^T \mu - r_{\text{target}} \ge 0\).

5. Feasibility Pre-Flight and Solver Status Classifications

Before launching numerical optimization, the runtime conducts analytical feasibility checks:

Feasibility Pre-Flight Tests:

Individual Box Bounds Feasibility: \[\forall i, \quad l_i \le u_i + 10^{-6}\] If any \(l_i > u_i\), the problem is analytically infeasible.
Aggregate Budget Feasibility: \[\sum_{i=1}^n l_i \le 1.0 + 10^{-6} \quad \text{and} \quad \sum_{i=1}^n u_i \ge 1.0 - 10^{-6}\] If the sum of minimum weights exceeds 100%, or the sum of maximum weights is less than 100%, no allocation can satisfy both the box bounds and full investment.
Target Return Reachability: \[r_{\text{target}} \le \max_{w \in \mathcal{F}} (w^T \mu)\] If \(r_{\text{target}}\) exceeds the maximum return attainable within the feasible region \(\mathcal{F}\), the target return constraint cannot be satisfied.

Status Codes (`Results!B8`):

Status Code	Meaning	Criteria
`OPTIMAL`	Global/local optimum successfully certified.	Solver converged (`success=True`) and all constraint violations satisfy \(\| \Delta \| \le 10^{-4}\).
`CONVERGED`	Numerical solution reached with minor constraint slack.	Solver converged but maximum constraint violation is between \(10^{-4}\) and \(10^{-2}\).
`INFEASIBLE`	No valid portfolio exists.	Pre-flight check failed (\(l_i > u_i\), \(\sum l_i > 1\), or \(\sum u_i < 1\)) or solver could not find any point satisfying constraints.
`FAILED`	Numerical divergence or optimizer failure.	SLSQP failed to converge, encountered singular gradients, or exceeded iteration limits.

When INFEASIBLE occurs, the optimizer halts cleanly, retains the safest projection \(w_{\text{init}}\), marks Results!B8 as INFEASIBLE, and flags Diagnostics!B7 (Box constraint feasibility) with FAIL.