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

OperatorSymbolExample
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.

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

Native Types

TypeExample
Booleantrue, false
String"some_string"
Numeric10, 20, 100000, -5000
Arrays['hello', 'world!']

Groups

ExpressionResult
(10 + 2) / 26
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 4040

Excel-Like Functions

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

Date Functions

FunctionDescriptionFormatExample
DATEReturns 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")
DATEVALUEReturns 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)"
DAYConverts a date (in "MM/DD/YYYY" or UTC format) to a day of the monthDAY("MM/DD/YYYY")DAY("12/25/2008") == 25
DAYSReturns 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
EDATEAdds a given number of months to a date.EDATE(date, months)EDATE("10/15/2020", -5) == "5/15/2020"
EOMONTHReturns 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"
ISOWEEKNUMReturns 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
MONTHConverts a date(in UTC or MM/DD/YYYY format) to a month numberMONTH("MM/DD/YYYY")MONTH("12/25/2008") == 12
NETWORKDAYSINTLReturns the number of whole workdays (M-F) between two dates(in UTC or MM/DD/YYYY format)NETWORKDAYSINTL(start_date, end_date) == 31NETWORKDAYSINTL("2/1/2022", "3/15/2022") == 31
NOWReturns 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"
TODAYReturns 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"
WEEKNUMConverts a date to a number representing where the week falls numerically with a yearWEEKNUM("MM/DD/YYYY")WEEKNUM("12/25/2020") == 52
WORKDAYINTLReturns 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"
YEARConverts a date (in UTC or MM/DD/YYYY format) to a yearYEAR("MM/DD/YYYY")YEAR("7/5/2008") == 2008
YEARFRACReturns 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

FunctionDescriptionFormatExample
ACCRINTReturns 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
CUMIPMTReturns the cumulative interest paid between two periodsCUMIPMT(interest_rate, number_of_periods, present_value, start_period_number, end_period_number, type)CUMIPMT(0.01, 360, 100000, 13, 24, 0) == -11934.45
CUMPRINCReturns the cumulative principal paid on a loan between two periodsCUMPRINC(interest_rate, number_of_periods, present_value, start_period_number, end_period_number, type)CUMPRINC(0.01, 360, 100000, 13, 24, 0) == -408.902
EFFECTReturns the effective annual interest rateEFFECT(interest_rate, npery)EFFECT(0.1, 4) == 0.104
FVReturns the future value of an investmentFV(interest_rate, number_of_periods, payment_amount, present_value, type)FV(0.01, 10, (-100), (-1000), 0) == 2150.843
IPMTReturns the interest payment for an investment for a given periodIPMT(interest_rate, start_period, total_periods, present_value, future_value, type)IPMT(0.01, 6, 24, 100000, 1000000, 0) == 1080.231
ISPMTCalculates the interest paid during a specific period of an investmentISPMT(interest_Rate, start_period, total_periods, present_value)ISPMT(0.01, 6, 24, 100000) == -750
NOMINALReturns the annual nominal interest rateNOMINAL(effective_rate, number_of_periods)NOMINAL(0.1, 4) == 0.096
NPERReturns the number of periods for an investmentNPER(interest_rate, payment_amount, present_value, future_value, type)NPER(0.01, (-100), (-1000), 10000, 0) == 60.082
NPVReturns the net present value of an investment based on a series of periodic cash flows and a discount rateNPV(discount_rate, value1, value2, …)NPV(0.1, (-10000), 2000, 4000, 8000) == 1031.350
PDURATIONReturns the number of periods required by an investment to reach a specified valuePDURATION(interest_rate, present_value, future_value)PDURATION(0.1, 1000, 2000) == 7.273
PMTReturns the periodic payment for an annuityPMT(interest_rate, number_periods, present_value, future_value, type)PMT(0.01, 24, 100000, 1000000, 0) == -41780.819
PPMTReturns the payment on the principal for an investment for a given periodPPMT(interest_rate, start_period, total_periods, present_value, future_value, type)PPMT(0.01, 6, 24, 100000, 1000000, 0) == -42861.05
PVReturns the present value of an investmentPV(discount_rate, number_periods, payment_amount, future_value, type)PV(0.01, 24, 1000, 10000, 0) == -29119.04
RATEReturns the interest rate per period of an annuityRATE(number_periods, payment_amount, present_value, future_value, type)RATE(24, (-1000), (-10000), 100000, 0) == 0.065

Logical Functions

FunctionDescriptionFormatExample
ANDReturns TRUE if all of its arguments are TRUEAND(logical1, logical2, …)AND(1==1, 2==2) == true
IFSpecifies a logical test to performIF(logical_test, value_if_true, value_if_false)IF(3>2, "bigger", "smaller") == "bigger"
IFSChecks 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!"
INCLUDESReturns TRUE if a text string is contained within an array of strings.INCLUDES("text", ["test_text1", "test_text2", …])INCLUDES("apple", ["orange", "banana", "apple"]) == true
ISBLANKReturn TRUE if the value is blank stringISBLANK(value)ISBLANK("") == true
ISDATESTRINGReturn TRUE if the value is date stringISDATESTRING(value)ISDATESTRING("01/02/2022") == true
ISEMPTYReturns TRUE if the value is null or undefinedISEMPTY(value)ISEMPTY("12") == false
ISEVENReturns TRUE if the number is evenISEVEN(value)ISEVEN(4) == true
ISFINITEReturns TRUE if the value is finiteISFINITE(value)ISFINITE(24) == true
ISNANReturn TRUE if the value is not a numberISNAN(value)ISNAN("NaN") == true
ISNONTEXTReturns TRUE if the value is not textISNONTEXT(value)ISNONTEXT(987) == true
ISNOTEMPTYReturn TRUE if the value is not null or undefinedISNOTEMPTY(value)ISNOTEMPTY("12") == false
ISNOTNULLReturns TRUE if the value is not nullISNOTNULL(value)ISNOTNULL("123") == true
ISNOTUNDEFINEDReturns TRUE if the value is not undefinedISNOTUNDEFINED(value)ISNOTUNDEFINED("test") == true
ISNULLReturns TRUE if the value is nullISNULL(value)ISNULL("123") == false
ISNUMBERReturns TRUE if the value is a numberISNUMBER(value)ISNUMBER(987) == true
ISODDReturns TRUE if the number is oddISODD(value)ISODD(11) == true
ISTEXTReturns TRUE if the value is textISTEXT(value)ISTEXT(987) == false
ISUNDEFINEDReturns TRUE if the value is undefinedISUNDEFINED(value)ISUNDEFINED("12") == false
NINCLUDESReturns 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
NOTReverses the logic of its argumentNOT(logical)NOT(1==1) == false
ORReturns TRUE if any argument is TRUEOR(logical1, logical2, …)OR(1==2, 2==2) == true
RANGEReturns 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
SWITCHEvaluates 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"
UNIQUEReturns only unique items for provided array or listUNIQUE(value)UNIQUE(["a1", "a1", "a1", "b2", "c3", "d4", "d4", "c3"]) == ["a1", "b2", "c3", "d4"]
XORReturns a logical exclusive OR of all argumentsXOR(logical1, logical2, …)XOR(true, false, true) == false

Mathematical Functions

FunctionDescriptionFormatExample
ABSReturns the absolute value of a numberABS(number)ABS(-3) == 3
ACOSReturns the arccosine of a numberACOS(number)ACOS(-0.5) == 2.094
ACOSHReturns the inverse hyperbolic cosine of a numberACOSH(number)ACOSH(12) == 3.17
ACOTReturns the arccotangent of a numberACOT(number)ACOT(8) == 0.124
ACOTHReturns the hyperbolic arccotangent of a numberACOTH(number)ACOTH(44) == 0.023
ASINReturns the arcsine of a numberASIN(number)ASIN(-0.5) == -0.524
ASINHReturns the inverse hyperbolic sine of a numberASINH(number)ASINH(-2.5) == -1.647
ATANReturns the arctangent of a numberATAN(number)ATAN(2) == 1.1071487177940904
ATAN2Returns the arctangent from x- and y-coordinatesATAN2(x_num, y_num)ATAN2(2, 6) == 0.3217505543966422
ATANHReturns the inverse hyperbolic tangent of a numberATANH(number)ATANH(-0.2) == -0.203
AVERAGEReturns the average of its argumentsAVERAGE(number1, number2, ...)AVERAGE(1,2,3,4) == 2.5
AVERAGEAReturns the average of its arguments, including numbers, text, and logical valuesAVERAGEA(value1, value2, ...)AVERAGEA(1,2,3,4, "apple") == 2
AVERAGEIFReturns the average (arithmetic mean) of all the cells in a range that meet a given criteriaAVERAGEIF([criteria_value1, criteria_value2, ...], criteria, [average_value1, average_value2, …])AVERAGEIF([2,4,8,16], ">5", [1, 2, 3, 4]) == 3.5
BASEConverts a number into a text representation with the given radix (base)BASE(number, radix, min_length)BASE(15, 2, 10) == "0000001111"
COSReturns the cosine of a numberCOS(number)COS(12) == 0.844
COSHReturns the hyperbolic cosine of a numberCOSH(number)COSH(12) == 81377.396
COTReturns the contangent of an angleCOT(number)COT(42) == 0.436
COTHReturns the hyperbolic cotangent of an angleCOTH(number)COTH(42) == 1
COUNTCounts how many numbers are in a list of argumentsCOUNT(value1, value2, …)COUNT(12, 14, 16, "apple") == 3
COUNTACounts how many values are in the list of argumentsCOUNTA(value1, value2, …)COUNTA(12, 14, 16, "apple") == 4
COUNTBLANKCounts the number of blank cells within a rangeCOUNTBLANK(value1, value2, …)COUNTBLANK("", 14, 16, "apple") == 1
COUNTIFCounts the number of values within a range that meet the given criteriaCOUNTIF([value1, value2, …], criteria)COUNTIF(["John", "Andrew", "Alice"], "Alice") == 1
COUNTUNIQUECounts the number of unique values within a range of valuesCOUNTUNIQUE(value1, value2, …)COUNTUNIQUE(1,2,2,3,3,3,4) == 4
CSCReturns the cosecant of an angleCSC(number)CSC(12) == -1.8636795977824385
CSCHReturns the hyperbolic cosecant of an angleCSCH(number)CSCH(42) == 1.149904452858712e-18
DELTAReturns 1 if two numbers are equal (0 otherwise)DELTA(value1, value2)DELTA(20,20) == 1
EReturns the value of e (e.g. 2.718..)E()E() == 2.718281828459045
EVENRounds a number up to the nearest even integerEVEN(number)EVEN(3) == 4
EXPReturns e raised to the power of a given numberEXP(number)EXP(4) == 54.598
LARGEReturns the k-th largest value in a data setLARGE([number1, number2, …], 3)LARGE([3,5,3,5,4,4,2,4,6,7], 3) == 5
LNReturns the natural logarithm of a numberLN(number)LN(12) == 2.485
LOGReturns the logarithm of a number to a specified baseLOG(number, base)LOG(12, 10) == 1.079
MAXReturns the maximum value in a list of argumentsMAX(value1, value2, …)MAX(12, 24, 13, 82, 1, 92) == 92
MAXAReturns the maximum value in a list of arguments, including numbers, text, and logical valuesMAXA(value1, value2, …)MAXA(12, 24, 13, 82, 1, 92, "apple") == 92
MEDIANReturns the median of the given numbersMEDIAN(value1, value2, …)MEDIAN(12, 24, 13, 82, 92) == 24
MINReturns the minimum value in a list of argumentsMIN(value1, value2, …)MIN(12, 24, 13, 82, 1, 92) == 1
MINAReturns the smallest value in a list of arguments, including numbers, text, and logical valuesMINA(value1, value2, …)MINA(12, 24, 13, 82, 1, 92, "apple") == 0
MODReturns the remainder from divisionMOD(number, divisor)MOD(12,5) == 2
MODEReturns the most common value in a data setMODE(value1, value2, …)MODE(12, 12, 24, 13, 82) == 12
ODDRounds a number up to the nearest odd integerODD(number)ODD(2) == 3
PIReturns the value of pi (i.e. 3.142...)PI()PI() == 3.142
POWERReturns the result of a number raised to a powerPOWER(number, power)POWER(12,2) == 144
PRODUCTMultiplies its argumentsPRODUCT(value1, value2, …)PRODUCT(4,6,8) == 192
QUOTIENTReturns the integer portion of a divisionQUOTIENT(numerator, denominator)QUOTIENT(62,11) == 5
RADIANSConverts degrees to radiansRADIANS(angle)RADIANS(90) == 1.571
RANDReturns a random number between 0 and 1RAND()RAND() == 0.213
RANDBETWEENReturns a random number between the numbers you specifyRANDBETWEEN(bottom, top)RANDBETWEEN(12, 18) == 14
ROUNDRounds a number to a specified number of digitsROUND(number, num_digits)ROUND(626.321, 2) == 626.32
ROUNDDOWNRounds a number down, toward zeroROUNDDOWN(number, num_digits)ROUNDDOWN(6.8, 0) == 6
ROUNDUPRounds a number up, away from zeroROUNDUP(number, num_digits)ROUNDUP(6.2, 0) == 7
SECReturns the secant of an angleSEC(number)SEC(180) == -1.671
SECHReturns the hyperbolic secant of an angleSECH(number)SECH(180) == 1.343
SINReturns the sine of the given angleSIN(number)SIN(45) == 0.851
SINHReturns the hyperbolic sine of a numberSINH(number)SINH(3) == 10.018
SMALLReturns the k-th smallest value in a data setSMALL(array, k)SMALL([1,2,3,4,5,6], 3) == 3
SQRTReturns a positive square rootSQRT(number)SQRT(12) == 3.464
SQRTPIReturns the square root of (number * pi)SQRTPI(number)SQRTPI(12) == 6.14
STANDARDIZEReturns a normalized valueSTANDARDIZE(x, mean, standard_dev)STANDARDIZE(42, 40, 1.5) == 1.333
SUMAdds its arguments togetherSUM(number1, number2, …)SUM(10, 20, 11, 14) == 55
SUMIFAdds the cells specified by a given criteriaSUMIF([number1, number2, ...], logical)SUMIF([10, 20, 30, 11, 14], ">15") == 50
SUMSQReturns the sum of the squares of the argumentsSUMSQ(number1, number2, …)SUMSQ(3, 4) == 25
SUMX2MY2Returns the sum of the difference of squares of corresponding values in two arraysSUMX2MY2(array_x, array_y)SUMX2MY2([1,2], [3,4]) == -20
SUMX2PY2Returns the sum of the sum of squares of corresponding values in two arraysSUMX2PY2(array_x, array_y)SUMX2PY2([1,2], [3,4]) == 30
SUMXMY2Returns the sum of squares of differences of corresponding values in two arraysSUMXMY2(array_x, array_y)SUMXMY2([1,2], [3,4]) == 8
TANReturns the tangent of a numberTAN(number)TAN(10) == 0.648
TANHReturns the hyperbolic tangent of a numberTANH(number)TANH(0.2) == 0.197
TRUNCTruncates a number to an integerTRUNC(number, num_digits)TRUNC(12.222, 1) == 12.2

Text Functions

FunctionDescriptionFormatExample
BUILDFULLADDRESSConverts address variable to stringBUILDFULLADDRESS(variable)BUILDFULLADDRESS(variable) == "Some Address"
CLEANRemoves all nonprintable characters from textCLEAN("text")CLEAN("mytext") == "mytext"
CONCATCombines the text from strings into a single stringCONCAT("text1", "text2", …)CONCAT("apple", ", ", "orange") == "apple, orange"
EXACTChecks to see if two text values are identical (case sensitive)EXACT(value1, value2)EXACT("apple", "apple") == true
FINDFinds one text value within another (case-sensitive)FIND("find_text", "within_text", start_num)FIND("M", "Miriam McGovern", 3) == 8
LEFTReturns the leftmost characters from a text valueLEFT(value, num_chars)LEFT("apple", 3) == "app"
LENReturns the number of characters in a text stringLEN(value)LEN("apple") == 5
LOWERConverts text to lowercaseLOWER("text")LOWER("APPLE") == "apple"
MIDReturns a specific number of characters from a text string starting at the position you specifyMID(value, start_num, num_chars)MID("apple", 2, 2) == "pp"
PROPERCapitalizes the first letter in each word of a text valuePROPER("text")PROPER("FIX this Sentence") == "Fix This Sentence"
REPLACEReplaces characters within textREPLACE("old_text", start_number, number_characters, "new_text")REPLACE("abcdefghijk", 6, 5, "") == "abcdek"
REPTRepeats text a given number of timesREPT("text", number_times)REPT("-", 3) == "---"
RIGHTReturns the rightmost characters from a text valueRIGHT(value, num_chars)RIGHT("apple", 3) == "ple"
SEARCHFinds one text value within another (not case-sensitive)SEARCH(find_text, within_text, start_num)SEARCH("orange", "apple & orange", 1) == 9
SUBSTITUTESubstitutes new text for old text in a text stringSUBSTITUTE("text", "old_text", "new_text")SUBSTITUTE("apple", "p", "a") == "aaale"
TRIMRemoves spaces from textTRIM("text")TRIM("apple") == "apple"
UPPERConverts text to uppercaseUPPER("text")UPPER("convert THIS text") == "CONVERT THIS TEXT"
VALUEConverts a text argument to a numberVALUE("text")VALUE("12") == 12