General Operands:

The following operands are supported in the Dynamic Expressions:

Functions: 

OFFSET: Find an expression or variable’s value(s) in certain point(s) in time. The OFFSET function can be used to get a prior value or a range of value for a variable or expression.

Usage: OFFSET(VAR, N, RANGE, STEP)

  • VAR – The expression or variable.
  • N – The amount of periods back compared to the current period. 
  • RANGE – Optional, get multiple values for a range of months
  • STEP – Optional, target a specific step in the waterfall. If not stated, the value is taken from the top of the waterfall

Based on the RANGE parameter, the function will either return a single value or a list of values, which can then be used in tandem with functions such as SUM, AVERAGE, MIN and MAX.

 

MAX/MIN: Returns the maximum/minimum value for a list of values.

  • The MAX and MIN functions operate on a list of values, either through the output of another function, or any number of variables/expressions.
  • Usage: MAX (VAR_1, VAR_2, …, VAR_N)

 

AVERAGE:  Returns the average value for a list of values.

  • The AVERAGE function operates on a list of values, either through the output from
    another function, or any number of variables/expressions.
  • Usage: AVERAGE (VAR_1, VAR_2, …, VAR_N)

 

PRODUCT: Returns the product of two lists of values.

  • The PRODUCT function operates on two lists of values, for example combined with the output of the OFFSET function and returns a list that contains the in-place multiplications values of the lists. The two lists must have the same length.
  • Usage: PRODUCT (OFFSET($(VAR_1, N, RANGE), OFFSET($(VAR_2, N, RANGE))

 

SUM: Returns the total sum of values in a list

  • The SUM function can be used to return the total sum of all values in a list.
  • Usage: SUM (VALUE_LIST)

 

COUNT: Returns the count of values in a list

  • The COUNT function can be used to return the number of values in a list.
  • Usage: COUNT (VALUE_LIST)

 

ROUND: Rounds any number to the closet number with the specified number of digits. 

Example: ROUND (number, number of digits)

IRR: Internal Rate of Return, of an array of cash flow. The result is given as a decimal number. The range of values is obtained by OFFSET function. 

  • Syntax: IRR (range_of_values).

 

XIRR: Internal Rate of return of cash flow, aligned to the date of each cash flow, at cash flow report. The result is given as a decimal number. The range of values is obtained by OFFSET function.

  • Syntax: XIRR (range_of_values).

 

NPV: Net present value of a serial of cash flow (positive and negative), based on a discount rate. rate is given as a decimal number.

  • Example: NPV (rate, range_of_values)

 

XNPV: Is an NPV that aligns the cash flow to the dates time line of the cash flow report. The range of values is obtained by OFFSET function.

  • Example: XNPV (rate, range_of_values)

 

POWER: Number raised to a power.

  • Example: POWER (number, power)

 

Important to Note: Errors with Line Number details

When an error occurs within the Dynamic Expression Editor, the Dynamic Expression editor syntax validation provides line number indication of a syntax error.

Upon clicking on Validate button, in the case that there is a syntax error, a warning message is displayed followed by the line number the error is in the code.

 

In the example below, the variable name is incomplete, the error here is with the syntax within Line 2 of the dynamic expression, the error message states that “The formula is not valid, at line no. 2.”

 

Picture3.png

See further details about Dynamic Expressions.