Formula Language

How to write formulas in the DigiFi platform.

Overview Of DigiFi Formulas

DigiFi's formula language is powerful tool that lets you write complex calculations using simple syntax and Excel-like functions. It is used through the DigiFi platform for a number of purposes, including:

Formulas in DigiFi are simple functions that look comparable to spreadsheet functions, for example in Microsoft Excel or Google Sheets. They can include mathematical operators and built-in functions, and can reference variables (i.e. data fields) within the calculations.


Basic Lexical Constructions

DigiFi's formula language consists of few basic lexical constructions, including:

Identifiers

You can access variables within formulas by using their system name.

borrower_first_name + borrower_last_name

Operators

Operator

Symbol

Example

Add, Concat

system_variable + system_variable_2

Subtract

system_variable - system_variable_2

Multiply

system_variable * system_variable_2

Divide

/

system_variable / system_variable_2

Divide And Floor

//

system_variable // sytem_variable_2

Equal

==

system_variable == 10
system_variable == system_variable_2
system_variable == null
system_variable == true

Greater Than

system_variable > 10
system_variable > system_variable_2

Greater Than Or Equal

=

system_variable >= 10
system_variable >= system_variable_2

Modulus

%

system_variable % system_variable_2

Negate

!

!system_variable

Power Of

^

system_variable ^ 2
system_variable ^ system_variable_2

Less than

<

system_variable < 10
system_variable < system_variable_2

Less than or equal

<=

system_variable <= 10
system_variable <= system_variable_2

Logical And

&&

system_variable && system_variable_2

Logical Or

||

system_variable || system_variable_2

Not Equal

!=

system_variable != 10
system_variable != system_variable_2
system_variable != null
system_variable != true


Ternary Operators

Conditional expressions check to see if the first segment evaluates to a truthy value. If so, the consequent segment is evaluated. Otherwise, the alternate is. If the consequent section is missing, the test result itself will be used instead.

Expression

Result

"" ? "Full" : "Empty"

"Empty"

system_name == 10 ? "Yes" : "No" where system_name equals to 20

"No"


Native Types

Type

Example

Boolean

true, false

String

"some_string"

Numeric

10, 20, 100000, -5000

Arrays

['hello', 'world!']


Groups

Expression

Result

(10 + 2) / 2

6

system_variable && (system_variable_2 || system_variable_3) where system_variable equals to 10, system_variable_2 equals to null and system_variable_3 equals to 40

40


Excel-Like Functions

DigiFi's formula language includes many commonly-used functions, including:

Date Functions

Function

Description

Format

Example

DATE

Returns a full date based on date inputs (in MM/DD/YYYY format for Date variables and in UTC format for Text variables)

DATE(year, month, day)

DATE(1980, 4, 23) == "04/23/1980")

DATEVALUE

Returns the UTC date format based on a date input as MM/DD/YYYY (only assign these results to variables with the data type of "Text"; assigning to "Date" variables will convert the result back to "MM/DD/YYYY" format)

DATEVALUE("MM/DD/YYYY")

DATEVALUE("04/23/1980") == "Wed Apr 23 1980 00:00:00 GMT+0000 (Coordinated Universal Time)"

DAY

Converts a date (in "MM/DD/YYYY" or UTC format) to a day of the month

DAY("MM/DD/YYYY")

DAY("12/25/2008") == 25

DAYS

Returns the number of days between two dates (in UTC or MM/DD/YYYY format)

DAYS(endDate, startDate)

DAYS("3/15/2022", "2/1/2022") == 42

EDATE

Adds a given number of months to a date.

EDATE(date, months)

EDATE("10/15/2020", -5) == "5/15/2020"

EOMONTH

Returns the date of the last day of the month before or after a specified number of months (in MM/DD/YYYY format for Date variables and in UTC format for Text variables)

EOMONTH("MM/DD/YYYY", months)

EOMONTH("12/01/2020", 1) == "01/31/2021"

ISOWEEKNUM

Returns the number of the ISO week number of the year for a given date (in UTC or MM/DD/YYYY format)

ISOWEEKNUM("MM/DD/YYYY")

ISOWEEKNUM("12/25/2020") == 52

MONTH

Converts a date(in UTC or MM/DD/YYYY format) to a month number

MONTH("MM/DD/YYYY")

MONTH("12/25/2008") == 12

NETWORKDAYSINTL

Returns the number of whole workdays (M-F) between two dates(in UTC or MM/DD/YYYY format)

NETWORKDAYSINTL(start_date, end_date) == 31

NETWORKDAYSINTL("2/1/2022", "3/15/2022") == 31

NOW

Returns the current date and time (in MM/DD/YYYY format for Date variables and in UTC format for Text variables)

NOW()

NOW() == "05/16/2022"

TODAY

Returns the current date and time (in MM/DD/YYYY format for Date variables and in UTC format for Text variables)

TODAY()

TODAY() == "05/16/2022"

WEEKNUM

Converts a date to a number representing where the week falls numerically with a year

WEEKNUM("MM/DD/YYYY")

WEEKNUM("12/25/2020") == 52

WORKDAYINTL

Returns the date before or after a specified number of workdays (in MM/DD/YYYY format for Date variables and in UTC format for Text variables)

WORKDAYINTL("MM/DD/YYYY", workdays)

WORKDAYINTL("01/01/2020", 10) == "01/15/2020"

YEAR

Converts a date (in UTC or MM/DD/YYYY format) to a year

YEAR("MM/DD/YYYY")

YEAR("7/5/2008") == 2008

YEARFRAC

Returns the year fraction representing the number of whole days between start date and end date (in UTC or MM/DD/YYYY format)

YEARFRAC("MM/DD/YYYY", "MM/DD/YYYY")

YEARFRAC("01/01/2012", "07/30/2012", 3) == 0.578


Financial Functions

Function

Description

Format

Example

ACCRINT

Returns the accrued interest for a security that pays periodic interest (dates in MM/DD/YYYY format)

ACCRINT(issue_date, first_interest_date, settlement_date, interest_rate, par_value, frequency, basis)

ACCRINT("01/01/2011", "02/01/2011", "07/01/2014", 0.1, 1000, 1, 0) == 350

CUMIPMT

Returns the cumulative interest paid between two periods

CUMIPMT(interest_rate, number_of_periods, present_value, start_period_number, end_period_number, type)

CUMIPMT(0.01, 360, 100000, 13, 24, 0) == -11934.45

CUMPRINC

Returns the cumulative principal paid on a loan between two periods

CUMPRINC(interest_rate, number_of_periods, present_value, start_period_number, end_period_number, type)

CUMPRINC(0.01, 360, 100000, 13, 24, 0) == -408.902

EFFECT

Returns the effective annual interest rate

EFFECT(interest_rate, npery)

EFFECT(0.1, 4) == 0.104

FV

Returns the future value of an investment

FV(interest_rate, number_of_periods, payment_amount, present_value, type)

FV(0.01, 10, (-100), (-1000), 0) == 2150.843

IPMT

Returns the interest payment for an investment for a given period

IPMT(interest_rate, start_period, total_periods, present_value, future_value, type)

IPMT(0.01, 6, 24, 100000, 1000000, 0) == 1080.231

ISPMT

Calculates the interest paid during a specific period of an investment

ISPMT(interest_Rate, start_period, total_periods, present_value)

ISPMT(0.01, 6, 24, 100000) == -750

NOMINAL

Returns the annual nominal interest rate

NOMINAL(effective_rate, number_of_periods)

NOMINAL(0.1, 4) == 0.096

NPER

Returns the number of periods for an investment

NPER(interest_rate, payment_amount, present_value, future_value, type)

NPER(0.01, (-100), (-1000), 10000, 0) == 60.082

NPV

Returns the net present value of an investment based on a series of periodic cash flows and a discount rate

NPV(discount_rate, value1, value2, …)

NPV(0.1, (-10000), 2000, 4000, 8000) == 1031.350

PDURATION

Returns the number of periods required by an investment to reach a specified value

PDURATION(interest_rate, present_value, future_value)

PDURATION(0.1, 1000, 2000) == 7.273

PMT

Returns the periodic payment for an annuity

PMT(interest_rate, number_periods, present_value, future_value, type)

PMT(0.01, 24, 100000, 1000000, 0) == -41780.819

PPMT

Returns the payment on the principal for an investment for a given period

PPMT(interest_rate, start_period, total_periods, present_value, future_value, type)

PPMT(0.01, 6, 24, 100000, 1000000, 0) == -42861.05

PV

Returns the present value of an investment

PV(discount_rate, number_periods, payment_amount, future_value, type)

PV(0.01, 24, 1000, 10000, 0) == -29119.04

RATE

Returns the interest rate per period of an annuity

RATE(number_periods, payment_amount, present_value, future_value, type)

RATE(24, (-1000), (-10000), 100000, 0) == 0.065


Logical Functions

Function

Description

Format

Example

AND

Returns TRUE if all of its arguments are TRUE

AND(logical1, logical2, …)

AND(1==1, 2==2) == true

IF

Specifies a logical test to perform

IF(logical_test, value_if_true, value_if_false)

IF(3>2, "bigger", "smaller") == "bigger"

IFS

Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.

IFS(logical_test1, value_if_true1, logical_test2, value_if_true2)

IFS(false, "Hello!", true, "Goodbye") == "Goodbye!"

INCLUDES

Returns TRUE if a text string is contained within an array of strings.

INCLUDES("text", ["test_text1", "test_text2", …])

INCLUDES("apple", ["orange", "banana", "apple"]) == true

ISBLANK

Return TRUE if the value is blank string

ISBLANK(value)

ISBLANK("") == true

ISDATESTRING

Return TRUE if the value is date string

ISDATESTRING(value)

ISDATESTRING("01/02/2022") == true

ISEMPTY

Returns TRUE if the value is null or undefined

ISEMPTY(value)

ISEMPTY("12") == false

ISEVEN

Returns TRUE if the number is even

ISEVEN(value)

ISEVEN(4) == true

ISFINITE

Returns TRUE if the value is finite

ISFINITE(value)

ISFINITE(24) == true

ISNAN

Return TRUE if the value is not a number

ISNAN(value)

ISNAN("NaN") == true

ISNONTEXT

Returns TRUE if the value is not text

ISNONTEXT(value)

ISNONTEXT(987) == true

ISNOTEMPTY

Return TRUE if the value is not null or undefined

ISNOTEMPTY(value)

ISNOTEMPTY("12") == false

ISNOTNULL

Returns TRUE if the value is not null

ISNOTNULL(value)

ISNOTNULL("123") == true

ISNOTUNDEFINED

Returns TRUE if the value is not undefined

ISNOTUNDEFINED(value)

ISNOTUNDEFINED("test") == true

ISNULL

Returns TRUE if the value is null

ISNULL(value)

ISNULL("123") == false

ISNUMBER

Returns TRUE if the value is a number

ISNUMBER(value)

ISNUMBER(987) == true

ISODD

Returns TRUE if the number is odd

ISODD(value)

ISODD(11) == true

ISTEXT

Returns TRUE if the value is text

ISTEXT(value)

ISTEXT(987) == false

ISUNDEFINED

Returns TRUE if the value is undefined

ISUNDEFINED(value)

ISUNDEFINED("12") == false

NINCLUDES

Returns TRUE if a text string is not contained within an array of strings.

NINCLUDES("text", ["test_text1", "test_text2", …])

NINCLUDES("apple", ["orange", "banana", "apple"]) == false

NOT

Reverses the logic of its argument

NOT(logical)

NOT(1==1) == false

OR

Returns TRUE if any argument is TRUE

OR(logical1, logical2, …)

OR(1==2, 2==2) == true

RANGE

Returns TRUE if a number or date is between two specified values (inclusive of endpoints)

RANGE("test_value", "start_value", "end_value")

RANGE(12, 6, 18) == true

SWITCH

Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.

SWITCH(expression, value1, result1, default_or_value2, result2, …)

SWITCH(7, 9, "Nine", 7, "Seven") == "Seven"

UNIQUE

Returns only unique items for provided array or list

UNIQUE(value)

UNIQUE(["a1", "a1", "a1", "b2", "c3", "d4", "d4", "c3"]) == ["a1", "b2", "c3", "d4"]

XOR

Returns a logical exclusive OR of all arguments

XOR(logical1, logical2, …)

XOR(true, false, true) == false


Mathematical Functions

Function

Description

Format

Example

ABS

Returns the absolute value of a number

ABS(number)

ABS(-3) == 3

ACOS

Returns the arccosine of a number

ACOS(number)

ACOS(-0.5) == 2.094

ACOSH

Returns the inverse hyperbolic cosine of a number

ACOSH(number)

ACOSH(12) == 3.17

ACOT

Returns the arccotangent of a number

ACOT(number)

ACOT(8) == 0.124

ACOTH

Returns the hyperbolic arccotangent of a number

ACOTH(number)

ACOTH(44) == 0.023

ASIN

Returns the arcsine of a number

ASIN(number)

ASIN(-0.5) == -0.524

ASINH

Returns the inverse hyperbolic sine of a number

ASINH(number)

ASINH(-2.5) == -1.647

ATAN

Returns the arctangent of a number

ATAN(number)

ATAN(2) == 1.1071487177940904

ATAN2

Returns the arctangent from x- and y-coordinates

ATAN2(x_num, y_num)

ATAN2(2, 6) == 0.3217505543966422

ATANH

Returns the inverse hyperbolic tangent of a number

ATANH(number)

ATANH(-0.2) == -0.203

AVERAGE

Returns the average of its arguments

AVERAGE(number1, number2, ...)

AVERAGE(1,2,3,4) == 2.5

AVERAGEA

Returns the average of its arguments, including numbers, text, and logical values

AVERAGEA(value1, value2, ...)

AVERAGEA(1,2,3,4, "apple") == 2

AVERAGEIF

Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria

AVERAGEIF([criteria_value1, criteria_value2, ...], criteria, [average_value1, average_value2, …])

AVERAGEIF([2,4,8,16], ">5", [1, 2, 3, 4]) == 3.5

BASE

Converts a number into a text representation with the given radix (base)

BASE(number, radix, min_length)

BASE(15, 2, 10) == "0000001111"

COS

Returns the cosine of a number

COS(number)

COS(12) == 0.844

COSH

Returns the hyperbolic cosine of a number

COSH(number)

COSH(12) == 81377.396

COT

Returns the contangent of an angle

COT(number)

COT(42) == 0.436

COTH

Returns the hyperbolic cotangent of an angle

COTH(number)

COTH(42) == 1

COUNT

Counts how many numbers are in a list of arguments

COUNT(value1, value2, …)

COUNT(12, 14, 16, "apple") == 3

COUNTA

Counts how many values are in the list of arguments

COUNTA(value1, value2, …)

COUNTA(12, 14, 16, "apple") == 4

COUNTBLANK

Counts the number of blank cells within a range

COUNTBLANK(value1, value2, …)

COUNTBLANK("", 14, 16, "apple") == 1

COUNTIF

Counts the number of values within a range that meet the given criteria

COUNTIF([value1, value2, …], criteria)

COUNTIF(["John", "Andrew", "Alice"], "Alice") == 1

COUNTUNIQUE

Counts the number of unique values within a range of values

COUNTUNIQUE(value1, value2, …)

COUNTUNIQUE(1,2,2,3,3,3,4) == 4

CSC

Returns the cosecant of an angle

CSC(number)

CSC(12) == -1.8636795977824385

CSCH

Returns the hyperbolic cosecant of an angle

CSCH(number)

CSCH(42) == 1.149904452858712e-18

DELTA

Returns 1 if two numbers are equal (0 otherwise)

DELTA(value1, value2)

DELTA(20,20) == 1

E

Returns the value of e (e.g. 2.718..)

E()

E() == 2.718281828459045

EVEN

Rounds a number up to the nearest even integer

EVEN(number)

EVEN(3) == 4

EXP

Returns e raised to the power of a given number

EXP(number)

EXP(4) == 54.598

LARGE

Returns the k-th largest value in a data set

LARGE([number1, number2, …], 3)

LARGE([3,5,3,5,4,4,2,4,6,7], 3) == 5

LN

Returns the natural logarithm of a number

LN(number)

LN(12) == 2.485

LOG

Returns the logarithm of a number to a specified base

LOG(number, base)

LOG(12, 10) == 1.079

MAX

Returns the maximum value in a list of arguments

MAX(value1, value2, …)

MAX(12, 24, 13, 82, 1, 92) == 92

MAXA

Returns the maximum value in a list of arguments, including numbers, text, and logical values

MAXA(value1, value2, …)

MAXA(12, 24, 13, 82, 1, 92, "apple") == 92

MEDIAN

Returns the median of the given numbers

MEDIAN(value1, value2, …)

MEDIAN(12, 24, 13, 82, 92) == 24

MIN

Returns the minimum value in a list of arguments

MIN(value1, value2, …)

MIN(12, 24, 13, 82, 1, 92) == 1

MINA

Returns the smallest value in a list of arguments, including numbers, text, and logical values

MINA(value1, value2, …)

MINA(12, 24, 13, 82, 1, 92, "apple") == 0

MOD

Returns the remainder from division

MOD(number, divisor)

MOD(12,5) == 2

MODE

Returns the most common value in a data set

MODE(value1, value2, …)

MODE(12, 12, 24, 13, 82) == 12

ODD

Rounds a number up to the nearest odd integer

ODD(number)

ODD(2) == 3

PI

Returns the value of pi (i.e. 3.142...)

PI()

PI() == 3.142

POWER

Returns the result of a number raised to a power

POWER(number, power)

POWER(12,2) == 144

PRODUCT

Multiplies its arguments

PRODUCT(value1, value2, …)

PRODUCT(4,6,8) == 192

QUOTIENT

Returns the integer portion of a division

QUOTIENT(numerator, denominator)

QUOTIENT(62,11) == 5

RADIANS

Converts degrees to radians

RADIANS(angle)

RADIANS(90) == 1.571

RAND

Returns a random number between 0 and 1

RAND()

RAND() == 0.213

RANDBETWEEN

Returns a random number between the numbers you specify

RANDBETWEEN(bottom, top)

RANDBETWEEN(12, 18) == 14

ROUND

Rounds a number to a specified number of digits

ROUND(number, num_digits)

ROUND(626.321, 2) == 626.32

ROUNDDOWN

Rounds a number down, toward zero

ROUNDDOWN(number, num_digits)

ROUNDDOWN(6.8, 0) == 6

ROUNDUP

Rounds a number up, away from zero

ROUNDUP(number, num_digits)

ROUNDUP(6.2, 0) == 7

SEC

Returns the secant of an angle

SEC(number)

SEC(180) == -1.671

SECH

Returns the hyperbolic secant of an angle

SECH(number)

SECH(180) == 1.343

SIN

Returns the sine of the given angle

SIN(number)

SIN(45) == 0.851

SINH

Returns the hyperbolic sine of a number

SINH(number)

SINH(3) == 10.018

SMALL

Returns the k-th smallest value in a data set

SMALL(array, k)

SMALL([1,2,3,4,5,6], 3) == 3

SQRT

Returns a positive square root

SQRT(number)

SQRT(12) == 3.464

SQRTPI

Returns the square root of (number * pi)

SQRTPI(number)

SQRTPI(12) == 6.14

STANDARDIZE

Returns a normalized value

STANDARDIZE(x, mean, standard_dev)

STANDARDIZE(42, 40, 1.5) == 1.333

SUM

Adds its arguments together

SUM(number1, number2, …)

SUM(10, 20, 11, 14) == 55

SUMIF

Adds the cells specified by a given criteria

SUMIF([number1, number2, ...], logical)

SUMIF([10, 20, 30, 11, 14], ">15") == 50

SUMSQ

Returns the sum of the squares of the arguments

SUMSQ(number1, number2, …)

SUMSQ(3, 4) == 25

SUMX2MY2

Returns the sum of the difference of squares of corresponding values in two arrays

SUMX2MY2(array_x, array_y)

SUMX2MY2([1,2], [3,4]) == -20

SUMX2PY2

Returns the sum of the sum of squares of corresponding values in two arrays

SUMX2PY2(array_x, array_y)

SUMX2PY2([1,2], [3,4]) == 30

SUMXMY2

Returns the sum of squares of differences of corresponding values in two arrays

SUMXMY2(array_x, array_y)

SUMXMY2([1,2], [3,4]) == 8

TAN

Returns the tangent of a number

TAN(number)

TAN(10) == 0.648

TANH

Returns the hyperbolic tangent of a number

TANH(number)

TANH(0.2) == 0.197

TRUNC

Truncates a number to an integer

TRUNC(number, num_digits)

TRUNC(12.222, 1) == 12.2


Text Functions

Function

Description

Format

Example

BUILDFULLADDRESS

Converts address variable to string

BUILDFULLADDRESS(variable)

BUILDFULLADDRESS(variable) == "Some Address"

CLEAN

Removes all nonprintable characters from text

CLEAN("text")

CLEAN("mytext") == "mytext"

CONCAT

Combines the text from strings into a single string

CONCAT("text1", "text2", …)

CONCAT("apple", ", ", "orange") == "apple, orange"

EXACT

Checks to see if two text values are identical (case sensitive)

EXACT(value1, value2)

EXACT("apple", "apple") == true

FIND

Finds one text value within another (case-sensitive)

FIND("find_text", "within_text", start_num)

FIND("M", "Miriam McGovern", 3) == 8

LEFT

Returns the leftmost characters from a text value

LEFT(value, num_chars)

LEFT("apple", 3) == "app"

LEN

Returns the number of characters in a text string

LEN(value)

LEN("apple") == 5

LOWER

Converts text to lowercase

LOWER("text")

LOWER("APPLE") == "apple"

MID

Returns a specific number of characters from a text string starting at the position you specify

MID(value, start_num, num_chars)

MID("apple", 2, 2) == "pp"

PROPER

Capitalizes the first letter in each word of a text value

PROPER("text")

PROPER("FIX this Sentence") == "Fix This Sentence"

REPLACE

Replaces characters within text

REPLACE("old_text", start_number, number_characters, "new_text")

REPLACE("abcdefghijk", 6, 5, "") == "abcdek"

REPT

Repeats text a given number of times

REPT("text", number_times)

REPT("-", 3) == "---"

RIGHT

Returns the rightmost characters from a text value

RIGHT(value, num_chars)

RIGHT("apple", 3) == "ple"

SEARCH

Finds one text value within another (not case-sensitive)

SEARCH(find_text, within_text, start_num)

SEARCH("orange", "apple & orange", 1) == 9

SUBSTITUTE

Substitutes new text for old text in a text string

SUBSTITUTE("text", "old_text", "new_text")

SUBSTITUTE("apple", "p", "a") == "aaale"

TRIM

Removes spaces from text

TRIM("text")

TRIM("apple") == "apple"

UPPER

Converts text to uppercase

UPPER("text")

UPPER("convert THIS text") == "CONVERT THIS TEXT"

VALUE

Converts a text argument to a number

VALUE("text")

VALUE("12") == 12