Getting Started

Formulas

Charts & Dashboards

Managing models and collaboration

Functions

Math

Function

What it does

Example

abs

The absolute value of a number.

The absolute value of a number.

avg

The arithmetic mean (average) of the inputs. Can also be used as avgif.

avg(1, 2, 3) = 2

avg(if Var[all] % 2 = 0 then Var[all] else none = avg of all even values

first_nonzero_value

The first value that is not zero or empty.

first_nonzero_value(0,none,4,6) = 4

is_empty

Evaluates to 1 if variable has no expression, and otherwise to 0.

is_empty(Var[current])

log

Natural logarithm (base e).

log(e^2) = 2

log10

Common logarithm (base 10).

log10(100) = 2

max

The highest of the given values.

max(1, 2, 3) = 3; max([1, 2, 3]) = 3

min

The lowest of the given values.

min(1, 2, 3) = 1; min([1, 2, 3]) = 1

mod

The remainder of a division.
You can also use mod as "%"

mod(5, 2) = 1
5 % 2 = 1
reccurence every 4 months: if timeStep % 4 = 0 then 1 else 0

reverse

Reverses the order of an array.

reverse([1, 2]) = [2, 1]

round

Rounds the number to the closest integer. Optionally, you can specify the decimal places as the second argument.

round(2.3) = 2, round(2.5) = 3, round(2.26, 1) = 2.3

rounddown

Rounds the number down, toward 0. Optionally, you can specify the decimal places as the second argument.

rounddown(2.8) = 2, rounddown(-2.8) = -2, rounddown(2.88, 1) = 2.8

roundup

Rounds the number up, away from 0. Optionally, you can specify the decimal places as the second argument.

roundup(2.2) = 3, roundup(-2.2) = -3, roundup(2.22, 1) = 2.3

signchange

Timestep on which the first sign change occurs.

signchange(Var[all])

spread

spread(New Customers[0:t], Payments[0:t])

sqrt

Square root of a value.

sqrt(4) = 2

sum

The sum of the inputs. Can also be used as sumif or countif.

sum(1, 2, 3) = 6. sum(if Var[all] % 2 = 0 then Var[all] else 0) = sum of all even values

sumproduct

Multiplies two or more arrays together and returns the sum of products.

sumproduct(Var1[all], Var2[all])

exp

Exponential function

exp(2) = e^2

Time

Function

What it does

Example

date

The time step number of a date. Time steps start with 0.

date(2021,12,24) = 11 in a monthly model that starts in Jan 2021

month_from_date

Extracts the month of a date or time step.

month_from_date( date(2021,12,24) ) = 12

year_from_date

Extracts the year of a date or time step.

year_from_date( date(2021,12,24) ) = 2021

Financial

cumipmt

Cumulative interest paid

cumipmt(rate, periods, value, start, end, type)

finance.AM

Amortization

finance.AM(principal, rate, period, yearOrMonth, payAtBeginning)

finance.CAGR

Compound Annual Growth Rate

finance.CAGR(beginning value, ending value, number of periods)

finance.CI

Compound Interest

finance.CI(rate, compoundings per period, principal, number of periods)

finance.DF

Discount Factor (returns an array; must index array to obtain values -- to see all values of array, see working example above)

finance.DF(rate, number of periods)[index]

finance.FV

Future Value

finance.FV(rate, nper, pmt, pv, type)

finance.IAR

Inflation-adjusted Return

finance.IAR(investment return, inflation rate)

finance.irr

Internal rate of return.

finance.IRR(Cashflows[all], [guess])

finance.LR

Leverage Ratio

finance.LR(total liabilities, total debts, total income)

finance.NPV

Net Present Value

finance.NPV(rate, initial investment, cash flows[all])

finance.PI

Profitability Index

finance.PI(rate, initial investment, cash flows[all])

finance.PMT

Payment for a loan based on constant payments and a constant interest rate

finance.PMT(fractional interest rate, number of payments, principal)

finance.PP

Payback Period

finance.PP(number of periods, cash flows[all])

finance.PV

Present Value

finance.PV(rate, nper, pmt, [fv], [type])

finance.R72

Rule of 72

finance.R72(rate)

finance.roi

Return on investment

finance.ROI(Cashflows[all])

finance.WACC

Weighted Average Cost of Capital

finance.WACC(market value of equity, market value of debt, cost of equity, cost of debt, tax rate)

finance.xirr

Internal rate of return for a schedule of cash flows.

finance.xirr([-10,11], [0,12], 12) = 10%

fv

Future Value

fv(rate, nper, pmt, pv, [type])

fvifa

Future Value Interest Factor of an Annuity.

fvifa(rate, nper)

ipmt

Interest portion of a given loan payment.

ipmt(rate, per, nper, pv, [fv], [type])

pmt

Periodic payment for a loan.

pmt(rate, nper, pv, [fv], [type])

ppmt

Principal portion of a given loan payment.

ppmt(rate, per, nper, pv, [fv], [type])

pv

Present Value

pv(rate, nper, pmt, fv, [type])

pvif

Present Value Interest Factor

pvif(rate, nper)

rate

Interest rate per period of an annuity.

rate(nper, pmt, pv, [fv], [type], [guess])

Probability

beta

Beta distribution

beta(alpha, beta)

binominal

Binomial distribution

binomial(n, p)

cauchy

Cauchy distribution

cauchy(local, scale)

chisq

Chi-squared distribution

chisq(k)

exponential

Exponential distribution

exponential(rate)

gamma

Gamma distribution

gamma(shape, scale)

invgamma

Inverse-gamma distribution

invgamma(shape, scale)

lognormal

Log-normal distribution

lognormal(Î¼, Ïƒ^2)

lognormal_from

Log-normal distribution

lognormal_from_interval(from, to, confidence_percent)

normal

Normal distribution

normal(mean, variance)

normal_from

Normal distribution

normal_from_interval(from, to, confidence_percent)

pareto

Pareto distribution

pareto(min, alpha)

poisson

Poisson distribution

poisson(Î»)

stdev

The standard deviation of an array

stdev(Var[all])

triangle

Triangle distribution

triangle(from, to, confidence_percent)

uniform

Uniform distribution

uniform(from, to)

variance

The variance of an array

variance(Var[all])

Other

Function

What it does

Example

error

Error to assert an invalid state.

error()

flat_cohort_forecast

Forecasts cohort data without the cohort category.

flat_cohort_forecast(oldCohorts[all], newCohorts[all], retentionRates[all], lastDataDate, t, [additionalChurn])

gaussian_ramp

Ramping up a value over a time period in the shape of a gaussian bell curve.

gaussian_ramp(timestep,

date(2022,10), 2, 100)

is_data

Evaluates to 1 if argument is derived from a datasource, and otherwise to 0.

is_data(Variable[all])

is_locked

Evaluates to 1 if argument is a Locked Category Item, and otherwise to 0.

is_locked(categoryitem)

last_data_timestep

Returns the last timestep that is derived from a datasource.

last_data_timestep(Var[all])

logistic_ramp

Ramping up a value over a time period in an S-shape (logistic function).

logistic_ramp(timestep, date(2022,1), date(2022,10), 1.8, 100, 200)

quadratic_ramp

Ramping up a value quadratically over a time period.

quadratic_ramp(timestep,

date(2022,1), date(2022,10) [startValue], [endValue])

ramp

Ramping up a value linearly over a time period.

ramp(timestep, date(2022,1), date(2022,10), 100, 200)

ramp_normalized

A ramp with the cumulative sum of 1. Useful for distributing a fixed value over a time period.

ramp_normalized(timestep, date(2022,1), date(2022,10)

Last modified 18d ago

Copy link

On this page

Math

Time

Financial

Probability

Other