Worksheet Functions

🧾 Worksheet Functions

📌 Guide, not policy. docs/PUBLIC_API.txt and the headers of SACCR_Formulas.bas govern; where this page differs, the repository wins.

The module SACCR_Formulas is the only supported API of K-SACCR: ten SACCR_* functions that compute the SA-CCR building blocks. The engine calls them for every regulatory formula, and you can type them in any cell to check an intermediate figure on your own inputs. They depend on the VBA runtime only (no WorksheetFunction), so results do not depend on the Excel version or locale. Their names, arguments, defaults and results are a contract: changing one is a public API change.

---

🧭 Error contract (#42)

Each function checks the domain of its inputs and returns an Excel error value instead of raising a VBA error or returning a misleading number:

Error	Returned when
#NUM!	A number is outside its domain, or a result overflows the range of a Double
#VALUE!	A value that is not a number where one is needed: a text Threshold in SACCR_LambdaCRR; a text, Boolean or other non-number element in SACCR_FactorAggregation
#REF!	SACCR_FactorAggregation gets ranges or arrays of different sizes or shapes
The error itself	An error value inside an input range (SACCR_FactorAggregation) or as Threshold (SACCR_LambdaCRR) is passed on

Nine functions return a Variant, so they can carry the error value; a VBA caller tests it with IsError. SACCR_NormCDF returns a Double: it accepts any real number and has no error result. The documented floors (S below 0, M below the minimum, SD below its floor) are applied, not rejected.

---

📚 The ten functions

Function	Returns	Reference
SACCR_SupervisoryDuration(S, E, [FloorYears])	Supervisory duration	CRE52.34; CRR Art. 279b
SACCR_MaturityFactor(M, [Margined], [MPOR_BD], [MinBD], [BDPerYear])	Maturity factor	CRE52.48, 52.52; CRR Art. 279c
SACCR_OptionDelta(P, K, T, Vol, IsCall, IsBought, [Lambda])	Supervisory delta of an option	CRE52.40; CRR Art. 279a
SACCR_LambdaCRR(P, K, IsInterestRate, [Threshold])	CRR lambda shift	Del. Reg. (EU) 2021/931 Art. 5, as amended by 2025/855
SACCR_CDODelta(A, D, IsLong)	Supervisory delta of a CDO tranche	CRE52.41
SACCR_IREffectiveNotional(D1, D2, D3, [Rho12], [Rho23], [Rho13])	IR effective notional across buckets	CRE52.57
SACCR_FactorAggregation(AddOns, Rhos)	Single-factor aggregation	CRE52.61, .66, .70
SACCR_Multiplier(VminusC, AddOn, [FloorPct])	PFE multiplier	CRE52.23; CRR Art. 278(3)
SACCR_ReplacementCost(V, C, [Margined], [TH], [MTA], [NICA])	Replacement cost	CRE52.10, .18; CRR Art. 275
SACCR_NormCDF(x)	Standard normal distribution N(x)	—

SACCR_SupervisoryDuration

Function SACCR_SupervisoryDuration(S As Double, E As Double, Optional FloorYears As Double = 0) As Variant

SD = (exp(−0.05 S) − exp(−0.05 E)) / 0.05, in years. S and E are years from the reporting date; a negative S (already started) is floored at 0. The result is at least FloorYears (ten business days is 10/250). #NUM! when E is before S after the floor, or FloorYears is negative.

=SACCR_SupervisoryDuration(0, 10)           → 7.8693868057…
=SACCR_SupervisoryDuration(0, 0.01, 10/250) → 0.04

SACCR_MaturityFactor

Function SACCR_MaturityFactor(M As Double, Optional Margined As Boolean = False, Optional MPOR_BD As Double = 10, Optional MinBD As Double = 10, Optional BDPerYear As Double = 250) As Variant

Unmargined: √min(max(M, MinBD / BDPerYear), 1). Margined: 1.5 × √(MPOR_BD / BDPerYear); M is then ignored. #NUM! when BDPerYear ≤ 0, or (margined) MPOR_BD ≤ 0, or (unmargined) MinBD < 0.

=SACCR_MaturityFactor(0.75)       → 0.8660254…
=SACCR_MaturityFactor(3/365)      → 0.2          (floored at 10 business days)
=SACCR_MaturityFactor(0, TRUE, 10) → 0.3

SACCR_OptionDelta

Function SACCR_OptionDelta(P As Double, K As Double, T As Double, Vol As Double, IsCall As Boolean, IsBought As Boolean, Optional Lambda As Double = 0) As Variant

d1 = (ln((P + λ)/(K + λ)) + ½ Vol² T) / (Vol √T); bought call +N(d1), sold call −N(d1), bought put −N(−d1), sold put +N(−d1). Result between −1 and 1. #NUM! when T ≤ 0, Vol ≤ 0, P + λ ≤ 0 or K + λ ≤ 0.

=SACCR_OptionDelta(0.06, 0.05, 1, 0.5, FALSE, TRUE) → -0.2693952…   (CRE99 Example 1 swaption)

SACCR_LambdaCRR

Function SACCR_LambdaCRR(P As Double, K As Double, IsInterestRate As Boolean, Optional Threshold As Variant) As Variant

Interest rate: λ = max(threshold − min(P, K), 0), default threshold 0.001 (0.10%). Commodity: λ = max(−(1 + threshold) × min(P, K), 0), default 0.1. Result 0 or positive. Under Basel the shift is left to judgement (CRE52.40 FAQ) and is entered on the trade instead. #VALUE! when Threshold is not a number, #NUM! when it is negative, the error itself when it is an error value.

=SACCR_LambdaCRR(-0.002, 0.001, TRUE) → 0.003
=SACCR_LambdaCRR(-5, -4, FALSE)       → 5.5

SACCR_CDODelta

Function SACCR_CDODelta(A As Double, D As Double, IsLong As Boolean) As Variant

δ = ±15 / ((1 + 14A)(1 + 14D)), positive for purchased protection. #NUM! unless 0 ≤ A < D ≤ 1.

=SACCR_CDODelta(0.03, 0.06, TRUE) → 5.7409675…

SACCR_IREffectiveNotional

Function SACCR_IREffectiveNotional(D1 As Double, D2 As Double, D3 As Double, Optional Rho12 As Double = 0.7, Optional Rho23 As Double = 0.7, Optional Rho13 As Double = 0.3) As Variant

EN = √(D1² + D2² + D3² + 2ρ12 D1D2 + 2ρ23 D2D3 + 2ρ13 D1D3); the defaults give the regulatory cross terms 1.4, 1.4 and 0.6. #NUM! when a correlation is outside 0 to 1 or the three do not form a valid correlation matrix (the same test the engine applies to IRCorrBucket*).

SACCR_FactorAggregation

Function SACCR_FactorAggregation(AddOns As Variant, Rhos As Variant) As Variant

AddOn = √((Σ ρk Ak)² + Σ (1 − ρk²) Ak²). AddOns is a range, an array or a single number (any sign; blank counts as 0); Rhos the matching correlations 0 to 1 (a blank correlation only beside a blank add-on). Two vectors are paired in order whatever their orientation; anything else must have the same rows and columns. #REF! for different sizes or shapes, #VALUE! for a non-number element, #NUM! for a correlation outside 0 to 1, and the first error value found in either input.

=SACCR_FactorAggregation(E18:G18, H18:J18) → 282.1288…   (FormulaTest row 18, CRE99 Example 2)

SACCR_Multiplier

Function SACCR_Multiplier(VminusC As Double, AddOn As Double, Optional FloorPct As Double = 0.05) As Variant

m = min(1, F + (1 − F) exp((V − C) / (2 (1 − F) AddOn))); 1 when AddOn is 0. Result between F and 1. #NUM! when AddOn < 0 or F is not in [0, 1).

=SACCR_Multiplier(-20, 282) → 0.9651926…
=SACCR_Multiplier(10, 282)  → 1

SACCR_ReplacementCost

Function SACCR_ReplacementCost(V As Double, C As Double, Optional Margined As Boolean = False, Optional TH As Double = 0, Optional MTA As Double = 0, Optional NICA As Double = 0) As Variant

Unmargined: max(V − C, 0). Margined: max(V − C, TH + MTA − NICA, 0); TH, MTA and NICA are used only when margined. #NUM! when a margined set has a negative TH or MTA.

=SACCR_ReplacementCost(5, 10, TRUE, 0, 10, 2) → 8

SACCR_NormCDF

Function SACCR_NormCDF(x As Double) As Double

N(x) by the Hart (1968) double-precision algorithm as published by West (2005), absolute error below 1E-14; N(−|x|) is taken as 0 beyond |x| = 37. Any real number; no error result.

=SACCR_NormCDF(1.96) → 0.9750021…

---

🧪 The FormulaTest sheet

FormulaTest unit-tests the ten functions on the workbook's yellow inputs. Each row computes the same rule with Excel's own functions (column L) and with the VBA function (column M), so changing an input tests the function on new values. Portfolio-level tests are on TestCatalogue (see Test Catalogue).

Position	Content
E4	Tolerance, workbook name FormulaTest_Tolerance; template value 0.00001 (0.001%); data validation > 0 and ≤ 0.01
Row 6	Headers
Rows 7–28	22 tests
C	Test name
D	Arguments (what Inputs 1–7 mean on that row)
E–K	Input 1 … Input 7 (yellow, editable)
L	Expected (Excel)
M	Function (VBA)
N	Rel. diff
O	Result: PASS / FAIL
P	Rule (CRE52 / CRR reference)

Rows	Function	Tests
7–9	SACCR_SupervisoryDuration	S=0 E=10; S=1 E=11; floor 10/250
10–12	SACCR_MaturityFactor	M = 0.75; M = 3 days (floor); margined, MPOR 10
13–15	SACCR_OptionDelta	Bought put; sold call; with lambda shift
16	SACCR_CDODelta	3–6% tranche, protection bought
17	SACCR_IREffectiveNotional	CRE99 Example 1, USD
18	SACCR_FactorAggregation	CRE99 Example 2
19–20	SACCR_Multiplier	V − C = −20; V − C > 0
21	SACCR_ReplacementCost	Margined, TH 0, MTA 10, NICA 2
22–23	SACCR_NormCDF	x = 1.96; x = −4
24–28	SACCR_LambdaCRR	Three interest-rate and two commodity cases

Normalized difference

Column N is labelled Rel. diff but is a normalized difference:

Rel. diff = |Function − Expected| / max(1, |Expected|)
=IFERROR(ABS(M7-L7)/MAX(1,ABS(L7)),"")

It is a relative error only when |Expected| ≥ 1; below 1 it is an absolute difference. A row PASSes when Rel. diff ≤ E4 (=IF(ISNUMBER(N7),IF(N7<=FormulaTest_Tolerance,"PASS","FAIL"),"FAIL")); an error value fails. Below about 1E-16 (double precision) only an exact match passes.

The Expected formulas apply the same boundaries as the functions: a negative duration start is floored at zero, a non-positive add-on or a positive V − C gives a multiplier of 1. Agreement between Excel and VBA is a regression check of the implementation, not an independent regulatory validation. Randomised inputs are planned in #165 (v1.3.0).

Screenshot: FormulaTest

The domain and error contract itself is tested by the TEST_Formulas suite (9 cases, 82 checks on 64-bit; 80 on 32-bit, which skips two LongLong checks), which also writes the formulas on a temporary sheet to check that the same results reach worksheet cells.

---

🔗 Related

• How It Works — where the engine calls each function
• Test Catalogue — TEST_Formulas and TEST_Harness
• Regulation: Potential Future Exposure · Replacement Cost and Collateral · Margining and MPOR
• Workbook Guide · Home