MXL Formula Language - Engineering Docs

MXL (Maybern Expression Language) is a formula language designed for financial calculations and data transformations. It provides an Excel-like syntax for working with tables, performing calculations, and aggregating data across complex financial structures.

Basic Syntax

Literals and Values

MXL supports several literal types:

Type	Examples
Numbers	`42`, `3.14`, `100.5`
Strings	`"USD"`, `"capital"`
Booleans	`TRUE`, `FALSE`
Null	`NULL`
Arrays	`[1, 2, 3]`, `["USD", "EUR"]`, `[]`
Time Intervals	`1 day`, `2 months`, `3 quarters`, `1 year`

Column References

Columns can be referenced in two ways:

# Simple name
column_name
capital_amount

# Qualified name (required when ambiguous)
TableName.column_name
InvestmentAdjustedTransaction.capital_amount

Column Access

Use bracket notation to select columns from a table:

# Access by simple name
Transaction[capital_amount]

# Access by qualified name
Transaction[InvestmentAdjustedTransaction.capital_amount]

# Access multiple columns
Transaction[entity_id, capital_amount]

# Computed expressions
Transaction[capital_amount * -1]
Transaction[capital_amount > 0]

Variables

Context variables are accessed directly by name:

distribution_date
as_of_date

Operators

Arithmetic Operators

Operator	Description
`+`	Addition
`-`	Subtraction
`*`	Multiplication
`/`	Division
`^`	Power
`-` (unary)	Negation

Comparison Operators

Operator	Description
`=`	Equal
`<>`	Not equal
`<`	Less than
`<=`	Less than or equal
`>`	Greater than
`>=`	Greater than or equal

Logical Operators

Operator	Description
`AND`	Logical AND
`OR`	Logical OR
`NOT`	Logical NOT

Operator Precedence

From highest to lowest:

Field access (., [])
Negation (-)
Exponentiation (^)
Multiplication and Division (*, /)
Addition and Subtraction (+, -)
Comparison operators (<, <=, >, >=, =, <>)
Logical NOT (NOT)
Logical AND (AND)
Logical OR (OR)

Use parentheses to override precedence: (a + b) * c

Contexts

The Context of an MXL calculation signifies its functional purpose. It determines which tables, columns, and variables you can reference.

Default Context

Tables:

TransactionsBySecurityOwnership
TransactionsBySecurityOwnershipAndInvestment
Transaction

Common Use Cases: Allocation rule basis

Waterfall Context

Tables:

InvestmentAdjustedTransaction
InvestmentAdjustedTransactionUngrouped
WaterfallTierResults
InvestmentRealizationStatusVersion

Variables:

distribution_date (DATE)
hurdle_rate

Notice Context

Tables:

Transaction

Variables:

fund_family_sub_event_id (STRING)
investor_entity_id (STRING)

Performance Metric Context

Tables:

GLEntry
Transaction

Variables:

as_of_date (quarter-end date)

Credit Facility Context

Tables:

CreditFacilityVersion
TransactionsByBorrowingBaseGroup
TransactionsBySecurityOwnership

Variables:

total_unused
total_outstanding_amount_for_day
facility_commitment_amount

Type System

Basic Types

Type	Description
`Boolean`	True/false values
`Integer`	Whole numbers
`Decimal`	Precise decimal numbers
`Float`	Floating-point numbers
`String`	Text values
`Date`	Date values
`TimeDelta`	Time intervals
`UUID`	Unique identifiers
`ForeignKey`	References to other tables
`NULL`	Null/missing values

Compound Types

Numeric - Union of Integer | Decimal | Float
Any - Any type (used where type doesn’t matter, like COUNT)
T - Generic type variable (used in IF and COALESCE)

Type Notation

Column[Type] - A column containing values of the specified type
Type - A scalar/literal value
[Type, ...] - An array of values
Table - A table/range reference

Display Types

Display types control how calculation results are formatted in the UI:

Type	Example	Description
Decimal	`1234.56`	Number with fractional precision
Integer	`42`	Whole number
Money	`$129.99`	Currency value
Percent	`22.4%`	Percentage value
Multiple	`1.68x`	Numeric multiplier
Date	`2025-10-23`	Calendar date
Datetime	`2025-10-23T14:30:00Z`	Full timestamp
Time Delta	`10 days`	Number of days
Text	`Port Co X`	Plain text
Boolean	`TRUE`	Logical value

Functions Reference

Arithmetic Functions

ADD / SUB / MULT / DIV / POW

ADD(a, b)  or  a + b
SUB(a, b)  or  a - b
MULT(a, b) or  a * b
DIV(a, b)  or  a / b
POW(base, exponent)  or  base ^ exponent

Examples:

ADD(Transaction[capital_amount], Transaction[signed_capital_amount])
ADD(Transaction[effective_date], 1 quarter)

Aggregation Functions

SUM

SUM(table, column)
SUM(column_selection)

Examples:

SUM(Transaction, capital_amount)
SUM(Transaction[capital_amount])
SUM(GROUP_BY(Transaction, Transaction.fund), capital_amount)

MIN / MAX

MIN(column)
MAX(column)

Examples:

MIN(InvestmentAdjustedTransaction[transaction_date])
MAX(WaterfallTierResults[total_distribution])

AVG

AVG(column)

Returns Decimal for precision.

COUNT

COUNT(column)

Counts non-null values.

GEO_MEAN

GEO_MEAN(column)

Geometric mean of positive values. Returns NULL if any value is negative or zero.

Comparison Functions

LEAST / GREATEST

LEAST(a, b)
GREATEST(a, b)

Examples:

LEAST(capital_amount, 1000000)
GREATEST(0, signed_capital_amount)

COALESCE

COALESCE(a, b)

Returns the first non-null value.

Conditional Functions

IF

IF(condition, true_value, false_value)

Examples:

IF(capital_amount > 0, "Call", "Distribution")
IF(affects_waterfall, capital_amount, 0)

IN

IN(value, array)

Checks if a value exists in an array. Examples:

IN(transaction_code, ["cap call", "fund distribution", "management fee"])

EXISTS

EXISTS(column)

Returns TRUE if the value is not NULL.

Date Functions

DATE

DATE(year, month, day)

Creates a date from components.

DATE_TRUNC

DATE_TRUNC(precision, date)

Truncates to: "day", "week", "month", "quarter", "year", "decade", "century", "millennium"

DAY / MONTH / YEAR

DAY(date)    # Returns 1-31
MONTH(date)  # Returns 1-12
YEAR(date)   # Returns four-digit year

DAYS

DAYS(interval)

Converts a time interval to number of days. Example:

DAYS(distribution_date - transaction_date)

Table Operations

FILTER

FILTER(table, condition)

Example:

FILTER(Transaction, capital_amount > 0)

GROUP_BY

GROUP_BY(table, column1, column2, ...)

Examples:

GROUP_BY(Transaction, Transaction.entity)
GROUP_BY(InvestmentAdjustedTransaction, investor_entity_id, fund_entity_id)

WINDOW

WINDOW(table, partition_by, order_by, preceding, following)

Creates a window frame for running calculations. Example:

WINDOW(Transaction, NULL, [transaction_date, id], NULL, 0)

LOOKUP

LOOKUP(left_table, right_table, left_column, right_column)

Performs a left join between two tables.

STACK

STACK(selection1, selection2)

Vertically unions column selections. Columns are matched by position, not name. Example:

STACK(
    Transaction[entity_id, -capital_amount AS negative_amount],
    GLEntry[entity_id, amount]
)

Financial Functions

FV (Future Value)

FV(amount, rate, period)

Calculates: amount * ((1 + rate)^period - 1) Example:

FV(capital_amount, 0.08, DAYS(distribution_date - transaction_date) / 365.0)

XIRR

XIRR(amounts, dates)
XIRR(amounts, dates, guess)

Internal rate of return for irregular cash flows. Example:

XIRR(Transaction[capital_amount], Transaction[transaction_date])

IRR

IRR(amounts)
IRR(amounts, guess)

Internal rate of return for regularly spaced cash flows.

Type Conversion Functions

INTEGER(value)
DECIMAL(value)
FLOAT(value)
STRING(value)
BOOLEAN(value)
DATE(value)
COLUMN(value)

Real-World Examples

Calculate Total Capital Contributions

SUM(
    FILTER(
        InvestmentAdjustedTransaction[
            IN(transaction_code_name, [
                "Capital Call - Investments",
                "Capital Call - Expenses",
                "Capital Call - Management Fees"
            ])
        ]
    )[signed_investment_adjusted_capital_amount]
)

Calculate 8% Preferred Return

SUM(
    InvestmentAdjustedTransaction[
        FV(
            -signed_capital_amount,
            0.08,
            DAYS(distribution_date - pref_date) / 365.0
        )
    ]
)

Group Transactions by Investor

SUM(
    GROUP_BY(
        InvestmentAdjustedTransaction,
        investor_entity_id
    )[signed_investment_adjusted_capital_amount]
)

Running Total with Window Function

SUM(
    WINDOW(
        Transaction,
        [entity],
        [transaction_date],
        NULL,
        0
    )[capital_amount]
)

Stack Cash Flows with Fair Values for IRR

irr_data = STACK(
    Transaction[
        entity_id,
        transaction_date,
        -capital_amount AS signed_amount
    ],
    FairValue[
        entity_id,
        COLUMN(valuation_date),
        fair_value
    ]
)

XIRR(irr_data[signed_amount], irr_data[transaction_date])

Testing

MXL

CI/CD

Tools

​Basic Syntax

​Literals and Values

​Column References

​Column Access

​Variables

​Operators

​Arithmetic Operators

​Comparison Operators

​Logical Operators

​Operator Precedence

​Contexts

​Default Context

​Waterfall Context

​Notice Context

​Performance Metric Context

​Credit Facility Context

​Type System

​Basic Types

​Compound Types

​Type Notation

​Display Types

​Functions Reference

​Arithmetic Functions

​ADD / SUB / MULT / DIV / POW

​Aggregation Functions

​SUM

​MIN / MAX

​AVG

​COUNT

​GEO_MEAN

​Comparison Functions

​LEAST / GREATEST

​COALESCE

​Conditional Functions

​IF

​IN

​EXISTS

​Date Functions

​DATE

​DATE_TRUNC

​DAY / MONTH / YEAR

​DAYS

​Table Operations

​FILTER

​GROUP_BY

​WINDOW

​LOOKUP

​STACK

​Financial Functions

​FV (Future Value)

​XIRR

​IRR

​Type Conversion Functions

​Real-World Examples

​Calculate Total Capital Contributions

​Calculate 8% Preferred Return

​Group Transactions by Investor

​Running Total with Window Function

​Stack Cash Flows with Fair Values for IRR

Basic Syntax

Literals and Values

Column References

Column Access

Variables

Operators

Arithmetic Operators

Comparison Operators

Logical Operators

Operator Precedence

Contexts

Default Context

Waterfall Context

Notice Context

Performance Metric Context

Credit Facility Context

Type System

Basic Types

Compound Types

Type Notation

Display Types

Functions Reference

Arithmetic Functions

ADD / SUB / MULT / DIV / POW

Aggregation Functions

SUM

MIN / MAX

AVG

COUNT

GEO_MEAN

Comparison Functions

LEAST / GREATEST

COALESCE

Conditional Functions

IF

IN

EXISTS

Date Functions

DATE

DATE_TRUNC

DAY / MONTH / YEAR

DAYS

Table Operations

FILTER

GROUP_BY

WINDOW

LOOKUP

STACK

Financial Functions

FV (Future Value)

XIRR

IRR

Type Conversion Functions

Real-World Examples

Calculate Total Capital Contributions

Calculate 8% Preferred Return

Group Transactions by Investor

Running Total with Window Function

Stack Cash Flows with Fair Values for IRR