Institutional Balance Sheet & Market Risk Model

An institutional asset-liability management and market risk reference across multi-tenor cash-flow discounting, key-rate durations, duration gap analysis, and macro yield curve stress testing.

Macroeconomic Stress Scenarios Download Excel Model (.xlsx)

Operating boundary

This is an educational asset-liability management and bank treasury decision reference. It connects discrete multi-tenor cash-flow discounting, two-sided numerical key-rate duration sensitivity ladders, economic surplus capitalization, and net interest spread income into an inspectable, auditable system.

Benchmark Yield Curve Shocks by Tenor (bps)

1Y Rate Shock0 bps

3Y Rate Shock0 bps

5Y Rate Shock0 bps

7Y Rate Shock0 bps

10Y Rate Shock0 bps

Valuation & Spread Shocks (bps)

Asset Credit Spread Shock (Valuation)0 bps

Asset Earned Yield Shock (Income)0 bps

Funding Cost Shock (Expense)0 bps

Table-First Data Flow: Adjusting any slider recalculates the Multi-Tenor Ledger Table in real time, which directly feeds all headline KPIs, SVG curves, and KRD bars.

Economic Surplus ($M)

$100.0M

Base: $100.0M (0.0%)

Economic Surplus Ratio

10.0%

Capital Cushion (S / Asset PV)

Annual Spread Income

$26.5M

Base: $26.5M (0.0%)

Duration Gap / DV01

-0.38y

Base: -0.38y | Surplus DV01: +$37.5K/bp

Asset Market Value

$1,000.0M

Dur: 4.88y | C: 35.4

Liability Value

$900.0M

Dur: 5.84y | C: 47.1

Net Interest Spread

2.25%

Earned: 6.25% - Fund: 4.00%

Risk Status

STABLE

Yield & Asset Discount Curves

Stressed Net Tenor KRD Exposure ($M / bp)

Executive Risk Diagnosis & Attribution

Primary Risk Driver

Balanced positioning / no material stress

Attribution: $0.0M impact

Secondary Driver / Dominant Tenor

Secondary exposure minimal

Dominant Tenor: None

Next Analyst Investigation

Re-verify asset credit spread assumptions and monitor benchmark yield curve shifts.

Multi-Tenor Cash Flow & Valuation Ledger Click to toggle tabular breakdown

Tenor	Benchmark Rate (r*f)	Asset Discount (r*A)	Asset CF (CF_A)	Liab CF (CF_L)	Stressed Asset PV	Stressed Liab PV	Net Tenor Surplus	Asset KRD	Liab KRD	Net Sens. ($M/bp)	Tenor P&L

Model Assumptions & Baseline Register

Assumption	Baseline Value	Unit	Class	Rationale & ALM Intuition
Asset Economic Value	$1,000.0M	$ Millions	Exposure Base	Standard institutional tranche size ($1M = 10 bps).
Liability Ratio	0.90 ($900M)	Ratio	Capital Structure	10.0% capital cushion ($100M surplus).
Asset Credit Spread	125 bps	Basis Points	Credit Spread	Investment-grade spread added to benchmark curve.
Asset Earned Yield	6.25%	Percentage	Accounting Yield	Contractual annual cash earned yield.
Funding / Liability Cost	4.00%	Percentage	Funding Cost	Weighted annual funding and crediting expense.
Asset Duration	4.88 yrs	Years	Effective Duration	Intermediate cash-flow weighting (30% in 5Y).
Liability Duration	5.84 yrs	Years	Effective Duration	Long-dated obligation profile (50% in 7Y-10Y).

Mathematical Specifications & Derivations

1. Discrete Cash-Flow Reconstruction & Baseline Parity

CF_A(t) = A₀ · w_A,t · (1 + r_A,t)^t where r_A,t = r_f,t + s_A

Reconstructs discrete annual coupon and principal cash flows from maturity weights w_A,t (∑ w = 1.0) and tenor discount yields. Guarantees PV_A = A₀ = $1,000.0M and PV_L = L₀ = $900.0M at baseline by construction.

2. Effective Duration & Convexity (Two-Sided Central Difference)

D_eff = − PV(r + h) − PV(r − h) 2 · PV(r) · h (first derivative / price sensitivity)

C = PV(r − h) + PV(r + h) − 2 · PV(r) PV(r) · h² (second derivative / curvature)

Two-sided 1 bp central difference finite perturbation (h = 0.0001 = 1 bp). Evaluates price sensitivity and curvature across non-parallel yield curve shocks without assuming flat curves.

3. Signed Surplus DV01 & Balance Sheet Sensitivity

DV01_S = DV01_L − DV01_A = +$37.5K / bp where DV01 = PV · D_eff · 0.0001

Dollar sensitivity per 1 bp rate shift: DV01_A = $48.8K/bp, DV01_L = $52.5K/bp. Because liability duration exceeds asset duration (5.84y vs 4.88y), parallel rate increases discount liabilities faster than assets, expanding economic surplus (+ sign).

4. Net Interest Income (NII) & Spread Income Separation

NII = A₀ · y_A − L₀ · c_L = ($1,000M × 6.25%) − ($900M × 4.00%) = +$26.5M/yr

Disentangles mark-to-market economic valuation from annual accrual income. Captures the divergence where rate cuts inflate asset valuations but compress spread margins.