HomeFormulas

# 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) = 2avg(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) = 15 % 2 = 1reccurence 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 | Spreads a value over a time period. Click here for more information. | 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

See Working with 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

Working Examples of Financial Functions here

Function | What it does | Example |
---|---|---|

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

Function | What it does | Example |
---|---|---|

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) |

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) |