Functions
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 |
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 |
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 |
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]) |
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]) |
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) |
if_error | If the first argument evaluates to an Invalid Number error, we return the second argument. Otherwise, we return the first argument. | if_error(Y/X, Z) - useful where the denominator X is 0, and so the first argument would result in a divided by 0 (invalid number error) |
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 2mo ago