HomeFormulas

Functions

Math

FunctionWhat it doesExample
absThe absolute value of a number.The absolute value of a number.
avgThe arithmetic mean (average) of the inputs. Can also be used as avgif.avg(1, 2, 3) = 2avg(if Var[all] % 2 = 0 then Var[all] else none = avg of all even values
first_nonzero_valueThe first value that is not zero or empty.first_nonzero_value(0,none,4,6) = 4
logNatural logarithm (base e).log(e^2) = 2
log10Common logarithm (base 10).log10(100) = 2
maxThe highest of the given values.max(1, 2, 3) = 3; max([1, 2, 3]) = 3
minThe lowest of the given values.min(1, 2, 3) = 1; min([1, 2, 3]) = 1
modThe remainder of a division.You can also use mod as "%"mod(5, 2) = 15 % 2 = 1reccurence every 4 months: if timeStep % 4 = 0 then 1 else 0
reverseReverses the order of an array.reverse([1, 2]) = [2, 1]
roundRounds 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
rounddownRounds 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
roundupRounds 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
signchangeTimestep on which the first sign change occurs.signchange(Var[all])
spreadSpreads a value over a time period.spread(New Customers[0:t], Payments[0:t])
sqrtSquare root of a value.sqrt(4) = 2
sumThe 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
sumproductMultiplies two or more arrays together and returns the sum of products.sumproduct(Var1[all], Var2[all])
expExponential functionexp(2) = e^2

Time

See Working with Time

FunctionWhat it doesExample
dateThe 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_dateExtracts the month of a date or time step.month_from_date( date(2021,12,24) ) = 12
year_from_dateExtracts the year of a date or time step.year_from_date( date(2021,12,24) ) = 2021

Financial

Working Examples of Financial Functions here

FunctionWhat it doesExample
cumipmtCumulative interest paidcumipmt(rate, periods, value, start, end, type)
finance.AMAmortizationfinance.AM(principal, rate, period, yearOrMonth, payAtBeginning)
finance.CAGRCompound Annual Growth Ratefinance.CAGR(beginning value, ending value, number of periods)
finance.CICompound Interestfinance.CI(rate, compoundings per period, principal, number of periods)
finance.DFDiscount 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.FVFuture Valuefinance.FV(rate, nper, pmt, pv, type)
finance.IARInflation-adjusted Returnfinance.IAR(investment return, inflation rate)
finance.irrInternal rate of return.finance.IRR(Cashflows[all], [guess])
finance.LRLeverage Ratiofinance.LR(total liabilities, total debts, total income)
finance.NPVNet Present Valuefinance.NPV(rate, initial investment, cash flows[all])
finance.PIProfitability Indexfinance.PI(rate, initial investment, cash flows[all])
finance.PMTPayment for a loan based on constant payments and a constant interest ratefinance.PMT(fractional interest rate, number of payments, principal)
finance.PPPayback Periodfinance.PP(number of periods, cash flows[all])
finance.PVPresent Valuefinance.PV(rate, nper, pmt, [fv], [type])
finance.R72Rule of 72finance.R72(rate)
finance.roiReturn on investmentfinance.ROI(Cashflows[all])
finance.WACCWeighted Average Cost of Capitalfinance.WACC(market value of equity, market value of debt, cost of equity, cost of debt, tax rate)
finance.xirrInternal rate of return for a schedule of cash flows.finance.xirr([-10,11], [0,12], 12) = 10%
fvFuture Valuefv(rate, nper, pmt, pv, [type])
fvifaFuture Value Interest Factor of an Annuity.fvifa(rate, nper)
ipmtInterest portion of a given loan payment.ipmt(rate, per, nper, pv, [fv], [type])
pmtPeriodic payment for a loan.pmt(rate, nper, pv, [fv], [type])
ppmtPrincipal portion of a given loan payment.ppmt(rate, per, nper, pv, [fv], [type])
pvPresent Valuepv(rate, nper, pmt, fv, [type])
pvifPresent Value Interest Factorpvif(rate, nper)
rateInterest rate per period of an annuity.rate(nper, pmt, pv, [fv], [type], [guess])

Probability

FunctionWhat it doesExample
betaBeta distributionbeta(alpha, beta)
binominalBinomial distributionbinomial(n, p)
cauchyCauchy distributioncauchy(local, scale)
chisqChi-squared distributionchisq(k)
exponentialExponential distributionexponential(rate)
gammaGamma distributiongamma(shape, scale)
invgammaInverse-gamma distributioninvgamma(shape, scale)
lognormalLog-normal distributionlognormal(μ, σ^2)
lognormal_fromLog-normal distributionlognormal_from_interval(from, to, confidence_percent)
normalNormal distributionnormal(mean, variance)
normal_fromNormal distributionnormal_from_interval(from, to, confidence_percent)
paretoPareto distributionpareto(min, alpha)
poissonPoisson distributionpoisson(λ)
stdevThe standard deviation of an arraystdev(Var[all])
triangleTriangle distributiontriangle(from, to, confidence_percent)
uniformUniform distributionuniform(from, to)
varianceThe variance of an arrayvariance(Var[all])

Other

FunctionWhat it doesExample
errorError to assert an invalid state.error()
flat_cohort_forecastForecasts cohort data without the cohort category.flat_cohort_forecast(oldCohorts[all], newCohorts[all], retentionRates[all], lastDataDate, t, [additionalChurn])
gaussian_rampRamping up a value over a time period in the shape of a gaussian bell curve.gaussian_ramp(timestep,date(2022,10), 2, 100)
if_errorIf 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_dataEvaluates to 1 if argument is derived from a datasource, and otherwise to 0.is_data(Variable[all])
is_lockedEvaluates to 1 if argument is a Locked Category Item, and otherwise to 0.is_locked(categoryitem)
last_data_timestepReturns the last timestep that is derived from a datasource.last_data_timestep(Var[all])
logistic_rampRamping 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_rampRamping up a value quadratically over a time period.quadratic_ramp(timestep,date(2022,1), date(2022,10) [startValue], [endValue])
rampRamping up a value linearly over a time period.ramp(timestep, date(2022,1), date(2022,10), 100, 200)
ramp_normalizedA 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)