Formulas

Formulas compute term values from other terms, constants, and built-in functions.

Formulas in Aire Labs

Formulas are expressions that compute the value of a term from other terms, constants, and built-in functions. They support standard arithmetic, arrays, units, financial calculations, and time-series logic.

---

Writing Formulas

Basic Syntax

Revenue * CapacityFactor
(CapEx + OpEx) / 12
IF(UtilizationRate > 0.8, HighTariff, LowTariff)

Operators

Operator	Meaning	Example
+	Add	Revenue + Grant
-	Subtract	Revenue - Costs
*	Multiply	Price * Volume
/	Divide	CapEx / Lifetime
^	Exponent	(1 + Rate) ^ Years
==	Equal	Scenario == 1
!=	Not equal	Status != 0
> < >= <=	Comparison	IRR >= HurdleRate
&&	And	A > 0 && B > 0
||	Or	A == 1 || B == 1

Comparison and logical operators return 1 (true) or 0 (false).

Referencing Other Terms

Refer to other terms by their name. Aire Labs resolves references from right to left — you can use a term’s full name or any unique suffix:

Project_Operations_Revenue

Operations_Revenue

Revenue

All three resolve to the same term as long as the suffix is unambiguous. If a suffix matches more than one term, the formula will show an #ambiguous error. Use a longer suffix to disambiguate.

Constants

Constant	Value
pi	3.14159…
true	1
false	0
null	empty / no value

---

Built-in Functions

Financial

Function	What it does
NPV(rate, values)	Net present value of a cash flow series discounted at rate
IRR(values)	Internal rate of return — first value is typically the initial investment (negative)
XIRR(values, dates)	IRR for cash flows with irregular dates
XNPV(rate, values, dates)	NPV for cash flows with irregular dates
PV(rate, nper, pmt, fv?, when?)	Present value of an annuity
FV(rate, nper, pmt, pv?, when?)	Future value of an annuity
PMT(rate, nper, pv, fv?, when?)	Periodic payment for a loan — rate is per period, pv is negative
IPMT(rate, per, nper, pv, fv?, when?)	Interest portion of a loan payment for a specific period
PPMT(rate, per, nper, pv, fv?, when?)	Principal portion of a loan payment for a specific period
MIRR(values, finance_rate, reinvest_rate)	Modified internal rate of return
PAYBACK(values, initial_investment)	Number of periods to recover the initial investment

Math

Function	What it does
ABS(x)	Absolute value
SQRT(x)	Square root
POW(base, exponent)	Raises base to a power
EXP(x)	e raised to x
LN(x)	Natural logarithm
LOG(x, base?)	Logarithm (default base e)
LOG10(x)	Base-10 logarithm
ROUND(x, decimals?)	Round to nearest — works element-wise on arrays
ROUNDUP(x, decimals?)	Always round away from zero
ROUNDDOWN(x, decimals?)	Always round toward zero
CEIL(x)	Round up to nearest integer
FLOOR(x)	Round down to nearest integer
MOD(x, y)	Remainder after division

Array

Function	What it does
SUM(array)	Total of all elements
AVERAGE(array)	Mean
MAX(array)	Largest value
MIN(array)	Smallest value
PRODUCT(array)	Product of all elements
COUNT(array)	Number of non-null elements
SUMPRODUCT(array1, array2, ...)	Sum of element-wise products
CUMSUM(array)	Cumulative sum — e.g. [100, 200, 300] → [100, 300, 600]
CUMPROD(array)	Cumulative product
CUMMAX(array)	Running maximum across an array — e.g. track peak generation output over a project’s operating period
CUMMIN(array)	Running minimum across an array — e.g. track floor prices over time
ARGSORT(array)	Returns the sort-order indices of an array — e.g. rank revenue streams by size without reordering the underlying model structure
RANGE(start, end, step?)	Generate a sequence — e.g. RANGE(1, 5) → [1, 2, 3, 4, 5]
DISTRIBUTE(total, periods)	Spread a value evenly across periods — useful for straight-line CapEx
REPEAT(value, count)	Repeat a value n times
LEN(array)	Number of elements
SLICE(array, start, end)	Extract a portion (0-indexed, end exclusive)
CONCAT(array1, array2)	Join two arrays
PUSH(array, value)	Append a value to an array
GET(array, index) / INDEX(array, index)	Element at position — negative index counts from end
INDEXOF(array, value)	Find position of a value
PAD(array, length)	Extend array to target length with nulls
UNIQUE(array)	Remove duplicates
MASK(array, condition)	Filter by condition — returns only matching elements
ISIN(value, array)	Returns 1 if value is in array, 0 if not
MATCH(value, array)	Find position of value — returns null if not found
XLOOKUP(lookup, lookup_array, return_array, if_not_found?)	Look up a value and return the corresponding element from another array

Conditional

Function	What it does
IF(condition, true_value, false_value)	Returns one of two values based on a condition
SUMIF(array, condition)	Sum of elements where condition is true
COUNTIF(array, condition)	Count of elements where condition is true

Date

Function	What it does
EDATE(date, months)	Add months to a date
EOMONTH(date, months)	Last day of the month, offset by months
DATEDIF(start, end, unit)	Difference between dates — units: "Y", "M", "D"
YEAR(date)	Extract the year
MONTH(date)	Extract the month
DAY(date)	Extract the day
BEFORE(date1, date2)	Returns 1 if date1 is before date2
AFTER(date1, date2)	Returns 1 if date1 is after date2
BETWEEN(date, start, end)	Returns 1 if date falls within range
CMP(date1, date2)	Returns -1, 0, or 1

String

Function	What it does
LEFT(string, length)	Left portion of a string
RIGHT(string, length)	Right portion of a string

Lookup & Navigation

These functions use lowercase names — this is intentional and reflects the underlying API convention.

Function	What it does
findheader(array, value)	Locates a matching header within your model structure — useful for navigating labeled data
findancestor(term, block)	Traverses the block hierarchy to find an ancestor term in a parent block
assoc(key, pairs)	Associative lookup — returns the value paired with a matching key from a list of key-value pairs

Unit Conversion

Function	What it does
CONVERT(value, from_unit, to_unit)	Converts a value between compatible units — e.g. CONVERT(1, "MWh", "kWh") → 1000

See Units for all supported unit strings.

Time & Calendar

Function	What it does
LAG(array, offset?, initial?)	Returns previous-period values — essential for period-to-period dependencies
PERIODS(duration, period_unit)	Converts a physical duration to a dimensionless period count
CALENDAR(rate, period)	Converts a physical rate to a calendar rate

Solver

Function	What it does
GOALSEEK(target, [...], tolerance?)	Iterative solver — finds the input that produces a target output

---

The Formula Editor

The formula editor is where you write and edit formulas for any calculated term. It’s available throughout Aire — open it from the term detail panel, from any block view, or directly from a cell in All Blocks.

Grid Mode adds one particularly useful entry point: clicking a calculated term opens the editor inline without navigating away from your model, so you can edit formulas while keeping your place in the spreadsheet. See All Blocks for the full layout options.

Authoring

• Enter to insert — Pressing Enter on any value or formula line inserts a new line directly below.
• Line numbers — Line numbers are visible in the editor for easier navigation in longer formulas.
• Inline computed values — Computed values display inline next to the formula that produced them.
• Human-readable display names — Formulas show display names for referenced terms; hover to reveal the underlying identifier.

Formula History

Every formula has a full version history accessible inside the editor. Select the History tab to see exactly how the expression has changed over time, who made each change, and when.