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