Functions
Every function that a planning formula can call, with its parameters, what it gives, and one example.
A function takes values in parentheses and gives a result, such as round(Gross margin, 2). This page lists every function in the planning formula language. The formula bar suggests the same functions while you type.
How to read this page
- Brackets in a signature mark an optional parameter, such as
[decimal_places]. - A parameter such as
values,inputs, orcashflowsaccepts many values. Give a list, such as[1, 2, 3], or a range, such asRevenue[all]. - When that parameter is the last one, you can also separate the values with commas, such as
sum(5, 10, 15). When more parameters follow it, as infinance.irr(cashflows, [guess]), use a list or a range. - Function names are not case-sensitive.
- The examples use fixed numbers, so you can check the result. In a model, use variable names in place of the numbers.
Math
| Function | Gives | Example | Result |
|---|---|---|---|
abs(number) | The value without its sign | abs(-250) | 250 |
avg(inputs) | The mean. Blank values are skipped | avg(4, 8, 12) | 8 |
avgif(values, condition) | The mean of the values where the condition is not 0 | avgif([10, 20, 30], [1, 0, 1]) | 20 |
ceiling(number) | The next whole number up | ceiling(4.2) | 5 |
countif(values, [condition]) | The number of non-blank values where the condition is not 0 | countif([5, 0, 8], [1, 1, 0]) | 2 |
cov(array1, array2) | The covariance of two series | cov([1, 2, 3], [2, 4, 6]) | 2 |
exp(number) | e raised to the number | exp(1) | 2.718… |
first_nonzero_value(values) | The first value that is not 0 or blank | first_nonzero_value(0, 0, 35, 40) | 35 |
floor(number) | The next whole number down | floor(4.8) | 4 |
hyp2f1(a, b, c, z) | The Gaussian hypergeometric function | hyp2f1(1, 1, 2, 0.5) | 1.386… |
log(number) | The natural logarithm | log(e) | 1 |
log10(number) | The base-10 logarithm | log10(1000) | 3 |
match(number, array) | The position of the first match, counted from 0 | match(30, [10, 20, 30]) | 2 |
max(values) | The largest value | max(3, 9, 6) | 9 |
median(values) | The middle value | median(2, 9, 4) | 4 |
min(values) | The smallest value | min(3, 9, 6) | 3 |
mod(dividend, divisor) | The remainder after division | mod(10, 4) | 2 |
pearson_correlation(array1, array2) | The Pearson correlation of two series | pearson_correlation([1, 2, 3], [2, 4, 7]) | 0.993… |
regression(inputArray, outputArray, timestep) | The output of a fitted straight line at a new input | regression([1, 2, 3], [5, 7, 9], 4) | 11 |
regression_coeff(inputArray, outputArray, n) | Coefficient n of the fitted line. n = 1 is the slope | regression_coeff([1, 2, 3], [5, 7, 9], 1) | 2 |
regression_intercept(inputArray, outputArray) | The intercept of the fitted line | regression_intercept([1, 2, 3], [5, 7, 9]) | 3 |
regression_r2(inputArray, outputArray) | The R squared of the fitted line | regression_r2([1, 2, 3], [5, 7, 9]) | 1 |
reverse(array) | The series in the opposite order | reverse(1, 2, 3)[0] | 3 |
round(number, [decimal_places]) | The nearest value | round(3.456, 2) | 3.46 |
rounddown(number, [decimal_places]) | The value rounded toward 0 | rounddown(3.456, 1) | 3.4 |
roundup(number, [decimal_places]) | The value rounded away from 0 | roundup(3.421, 1) | 3.5 |
signchange(values) | The position where the values first change sign, counted from 0 | signchange(-50, -20, 10, 30) | 2 |
spread(x, y) | With two numbers, x split evenly into y parts. With two series, each input applied to age-based weights | spread(1200, 12)[0] | 100 |
sqrt(number) | The square root | sqrt(16) | 4 |
stdev(values) | The sample standard deviation | stdev(2, 4, 4, 4, 5, 5, 7, 9) | 2.138… |
sum(inputs) | The total | sum(5, 10, 15) | 30 |
sumif(values, condition) | The total of the values where the condition is not 0 | sumif([10, 20, 30], [1, 0, 1]) | 40 |
sumproduct(array1, array2) | The sum of the products, position by position | sumproduct([2, 3], [10, 20]) | 80 |
variance(values) | The sample variance | variance(2, 4, 4, 4, 5, 5, 7, 9) | 4.571… |
Time
A time step is the position of a period in the model, counted from 0. These results depend on the model's dates.
| Function | Gives | Example |
|---|---|---|
date(year, month, [day]) | The time step of a date | date(2027, 1) gives the time step of Jan 2027 |
day_from_date(date) | The day of a date or time step | day_from_date(0) |
month_from_date(date) | The month, 1 to 12, of a date or time step | month_from_date(timeStep) |
year_from_date(date) | The year of a date or time step | year_from_date(date(2027, 1)) gives 2027 |
Finance
Rates use two scales. Read the rate column before you enter a rate.
- Fraction:
0.1means 10%. - Percent:
10means 10%.
If you type a rate as a number on the wrong scale, the formula bar shows a hint, such as finance.NPV reads rate in percentage points, so 0.1 means 0.1%. For 10% write 10.
| Function | Gives | Rate | Example | Result |
|---|---|---|---|---|
pmt(rate, nper, pv, [fv], [type]) | The payment for each period of a loan | Fraction | pmt(0.01, 36, 30000) | −996.43 |
ipmt(rate, per, nper, pv, [fv], [type]) | The interest part of one payment | Fraction | ipmt(0.01, 1, 36, 30000) | −300 |
ppmt(rate, per, nper, pv, [fv], [type]) | The principal part of one payment | Fraction | ppmt(0.01, 1, 36, 30000) | −696.43 |
cumipmt(rate, periods, value, start, end, [type]) | The total interest between two payments | Fraction | cumipmt(0.01, 36, 30000, 1, 12) | −3,124.68 |
fv(rate, nper, pmt, [pv], [type]) | The future value | Fraction | fv(0.05, 10, -1000) | 12,577.89 |
pv(rate, nper, pmt, [fv], [type]) | The present value | Fraction | pv(0.05, 10, -1000) | 7,721.73 |
fvifa(rate, nper) | The future value factor of an annuity | Fraction | fvifa(0.05, 10) | 12.578… |
pvif(rate, nper) | The present value factor | Fraction | pvif(0.05, 10) | 0.614… |
rate(nper, pmt, pv, [fv], [type], [guess]) | The interest rate for each period | Result is a fraction | rate(36, -996.43, 30000) | 0.01 |
finance.FV(rate, nper, pmt, [pv], [type]) | The same result as fv | Fraction | finance.FV(0.05, 10, -1000) | 12,577.89 |
finance.PV(rate, nper, pmt, [fv], [type]) | The same result as pv | Fraction | finance.PV(0.05, 10, -1000) | 7,721.73 |
finance.PMT(fractional_rate, payments, principal) | The payment for each period of a loan | Fraction | finance.PMT(0.01, 36, 30000) | −996.43 |
finance.IAR(investment_return, inflation_rate) | The return after inflation, in percent | Fraction | finance.IAR(0.08, 0.03) | 4.85… |
finance.irr(cashflows, [guess]) | The internal rate of return | Result is a fraction | finance.irr([-1000, 400, 400, 400]) | 0.097… |
finance.xirr(cashflows, time_steps, granularity) | The internal rate of return for cash flows at irregular time steps | Result is a fraction | finance.xirr([-1000, 600, 600], [0, 6, 12], 12) | 0.278… |
finance.roi(cashflows) | The return on investment | Result is a fraction | finance.roi(-1000, 400, 400, 400) | 0.2 |
finance.AM(principal, rate, period, [yearOrMonth], [payAtBeginning]) | The monthly payment that pays off a principal | Percent | finance.AM(30000, 12, 3, 0) | 996.43 |
finance.CAGR(beginning_value, ending_value, periods) | The compound annual growth rate, in percent | — | finance.CAGR(1000000, 2000000, 3) | 25.99 |
finance.CI(rate, compoundings, principal, periods) | The principal plus compound interest | Percent | finance.CI(5, 12, 10000, 2) | 11,049.41 |
finance.DF(rate, number of periods) | A series of discount factors | Percent | finance.DF(10, 3)[1] | 0.909… |
finance.LR(total_liabilities, total_debts, total_income) | The leverage ratio | — | finance.LR(500, 300, 1000) | 0.8 |
finance.NPV(rate, initial_investment, cashflows) | The net present value | Percent | finance.NPV(10, -1000, 400, 400, 400) | −5.26 |
finance.PI(rate, initial_investment, cashflows) | The profitability index | Percent | finance.PI(10, -1000, 400, 400, 400) | 0.99 |
finance.PP(number_of_periods, cashflows) | The payback period | — | finance.PP(0, -1000, 400, 400, 400) | 2.5 |
finance.R72(rate) | The years to double, by the rule of 72 | Percent | finance.R72(8) | 9 |
finance.WACC(equity_value, debt_value, cost_equity, cost_debt, tax_rate) | The weighted average cost of capital, in percent | Percent | finance.WACC(600000, 400000, 10, 6, 25) | 7.8 |
Probability
A probability function describes a range of values. In the grid, it shows one stable central value. During an uncertainty simulation, it gives a different value in each run. The Uncertainty page explains the simulation.
| Function | Distribution | Example | Grid value |
|---|---|---|---|
uniform(from, to) | Every value between the limits is equally likely | uniform(80, 120) | 100 |
triangle(from, to, [confidence_percent]) | Values near the middle are more likely | triangle(80, 120) | 100 |
normal(mean, variance) | Normal. The second parameter is the variance, not the standard deviation | normal(100, 25) | 100 |
normal_from_interval(from, to, [confidence_percent]) | Normal, from an interval. The default confidence is 90 | normal_from_interval(80, 120) | 100 |
normal_from(from, to, [confidence_percent]) | The same as normal_from_interval | normal_from(80, 120, 80) | 100 |
lognormal(mu, sigma_squared) | Log-normal | lognormal(0, 1) | 1 |
lognormal_from_interval(from, to, [confidence_percent]) | Log-normal, from an interval. The default confidence is 90 | lognormal_from_interval(10, 100) | 31.62… |
lognormal_from(from, to, [confidence_percent]) | The same as lognormal_from_interval | lognormal_from(10, 100, 90) | 31.62… |
sample(values) | One of the given values, each equally likely | sample(80, 100, 120) | 100 |
poisson(lambda) | Poisson, for counts of events | poisson(4) | 4 |
binomial(n, p) | Binomial, for successes in n trials | binomial(10, 0.3) | 3 |
binominal(n, p) | The same as binomial | binominal(10, 0.3) | 3 |
beta(alpha, beta) | Beta | beta(2, 5) | 0.286… |
cauchy(local, scale) | Cauchy | cauchy(0, 1) | 0 |
chisq(k) | Chi-squared | chisq(3) | 3 |
exponential(rate) | Exponential | exponential(0.5) | 1.386… |
gamma(shape, scale) | Gamma | gamma(2, 3) | 6 |
invgamma(shape, scale) | Inverse gamma | invgamma(3, 2) | 1 |
pareto(min, alpha) | Pareto | pareto(1, 3) | 1.5 |
Other
| Function | Gives | Example |
|---|---|---|
currency(value, currency code) | The value, marked as a currency. It does not convert the value | currency(Revenue, "EUR") |
error([reason]) | An error in the cell, with your reason as the message | error("Enter a start date") |
if_error(value, value if error) | The second value when the first value gives an error | if_error(Revenue / Units, 0) |
is_data(variable) | 1 when the variable's values come from a data source, else 0 | is_data(Revenue) |
is_locked(categoryitem) | 1 when the row that it names is locked, else 0 | is_locked(Revenue) |
last_data_timestep(variable) | The last time step that has values from a data source | last_data_timestep(Revenue) |
forecast(timestep, past_values, [growth_rate]) | The past value where one exists. For a later period, a forecast that Eigenn calculates in the background. The cell is empty until the forecast is ready | forecast(timeStep, Revenue[all]) |
flat_cohort_forecast(oldCohorts, newCohorts, retentionRates, lastDataDate, timestep, [additionalChurn]) | Old and new cohorts carried through a retention curve by age | flat_cohort_forecast(100, 0, 0.9, 0, 1) gives 90 |
ramp(timestep, start_date, end_date, [startValue], [endValue]) | A straight-line change between two time steps | ramp(timeStep, 0, 5, 0, 100) |
quadratic_ramp(timestep, start_date, end_date, [startValue], [endValue]) | A curved change that starts slowly | quadratic_ramp(timeStep, 0, 5, 0, 100) |
logistic_ramp(timestep, start_date, end_date, steepness, startValue, endValue) | An S-shaped change between two time steps | logistic_ramp(timeStep, 0, 11, 1, 0, 100) |
gaussian_ramp(timestep, date, width, endValue) | A bell-shaped change around one time step | gaussian_ramp(timeStep, 6, 2, 100) |
ramp_normalized(timestep, start_date, end_date, [flat]) | A ramp whose values add up to 1. Use 1 for flat to split evenly | ramp_normalized(timeStep, 0, 4) |
The ramp functions take time steps for their dates. Use date(2027, 1) to get the time step of a month.