Formulas Predefined by the Platform

Adds the number of days specified to the date, date and time, or time specified and returns the result in milliseconds.

The system is leap year aware. If you add 365 days to a leap year you will get a different result than if you add 365 days to a non-leap year.

Converts a Date Time value to a Date value based on the given time zone.

Example: DateFromDateTimeTZ(10/10/2012 12:00 AM EST,(GMT -5) Eastern Time (US, Canada) [US/Eastern]) returns 10/10/2012 Server Time Zone

Returns a string from the date, date and time, or time specified in the format specified. The following replacements are made:

• yyyy = year
• M = month (1-12)
• MM = month (01-12)
• MMM = English 3 character abbreviation of month (Jan-Dec)
• MMMM = English full spelling of month (January-December)
• dd = day of month (01-31)
• h = hour (1-12)
• hh = hour (01-12)
• HH = hour of day (00-23)
• mm = minute (00-59)
• ss = second (00-59)
• SSS = millisecond (000-999)
• zzz = English 3 character abbreviation of time zone (e.g., GMT, PST)
• a = AM or PM

Converts a Date value to a DateTime value with respect to a time zone. When this function is applied to a date, it returns a date time value that has the time value zeroed to 12 midnight.

Example: DateFromDateTimeTZ(10/8/2012,UTC-8) returns 10/8/2012 12:00 AM UTC-8

IPMT or Interest Payment formula calculates the interest payment for a given period of an investment based on periodic, constant payments and a constant interest rate.

The first input is the interest rate per period, which must be in a fraction form (already divided by 100). The second input is the period for which you want to find the interest and must be in the range 1 to numberOfPeriods. The next input is the total number of payment periods in an annuity. The fourth input is the present value, or the lump-sum amount that a series of future payments is worth right now.

Then you have the future value, or the cash balance one wants to attain after the last payment is made. Enter 0 for the default future value which means that annuity based on present value is attained or paid off after the last payment is made.

The due type indicates when payments are due. A warning is logged if the type is not supported and, in this case, the platform defaults to 0:

• 0 if payments are due at the end of the period
• 1 if payments are due at the beginning of the period

For the presentValue and futureValue parameters, the cash you pay out (such as deposits to savings) is represented by negative numbers; the cash you receive (such as loan) is represented by positive numbers.

This is a slight variation of the IPMT formula, where payment is specified instead of the future value. This enhanced formula allows to pre-calculate periodic payment (PMT) before calculating IPMT inside an iteration of periods.

Using this formula instead of the regular IPMT yields better performance as it does not have to re-calculate the payment for every iteration.

When pre-calculating PMT, make sure to specify the same rate, numberOfPeriods, presentValue, futureValue and dueType parameter values as you would specify for the regular IPMT formula to achieve the correct interest payment result.

Calculates the Rate of Return (IRR). IRR is related to Net Present Value (NPV). The value of IRR is the rate that will cause the NPV to result in zero. All parameters are Number field type. The hint parameter is a guess at the correct IRR. With a good hint the calculation can be performed much faster. If a hint is not available, 0 can be passed in and the function calculates a reasonable starting point.

For example: Given an initial investment of $100,000 and a return of $175,000 for 3 periods, the IRR would be approximately 166%.

In the IRR function you would get that using one of the following 2 calls:

• (using a hint) irr = IRR(3, 175000.00, 100000.00, 1.60)
• (without a hint) irr = IRR(3, 175000.00, 100000.00, 0)

When checking results remember that calculating an IRR gives an approximate answer so it is possible that the result from Maximo® Real Estate and Facilities’s IRR function and from Excel might be slightly different. However, given a good hint they should be within a fraction of a percent (+- 0.001).

This enhanced formula supports an array of payment values to the IRR calculation. The IRRX function requires 3 parameters and returns an approximate IRR value that is accurate to 12 decimal places.

The parameters required are as follows:

An initial payment - This is a positive number representing the undiscounted cost of an investment.

An array of payments - This is an array of positive numbers representing payments that will be received by the investor against the initial investment.

Rate hint - This is a number (positive or negative) that is a best guess or an approximation of the actual IRR. If a good hint is supplied it will make the calculation faster. If no hint is known use the value 0 and a hint will be calculated internally.

This enhanced formula supports an array of payment values to the NPV calculation. The period will then be determined by the number of payment values passed to the NPVX formula and the order of those values will be followed. All inputs should be numbers. The first input is the result of a query token, which is an array of number values. The second is the rate of discount over the length of one period. The third is the initial cost of investment which is to be deducted from the NPV result.

The enhanced query token can be set to Total or Values:

• Values – assigns all the number values returned by the query as an array of numbers. The query token passed to the NPVX formula should have the type set to Values so that the formula can understand the array of number values.
• Total – assigns the total of all the number values returned by the query.

The PMT or Payment formula calculates the periodic payment of an annuity based on constant payments and a constant interest rate. It contains the principal payment and interest of an annuity. The function can be used in IPMTX and PPMTX formulas to improve the performance when calculating these formulas inside an iteration of periods.

The first input is the interest rate per period, which must be in a fraction form (already divided by 100). The second input is the total number of payment periods in an annuity. The third input is the present value, or the lump-sum amount that a series of future payments is worth right now.

Then you have the future value, or the cash balance one wants to attain after the last payment is made. Enter 0 for the default future value which means that annuity based on present value is attained or paid off after the last payment is made.

The due type indicates when payments are due. A warning is logged if the type is not supported and, in this case, the platform defaults to 0:

• 0 if payments are due at the end of the period
• 1 if payments are due at the beginning of the period

For the presentValue and futureValue parameters, the cash you pay out (such as deposits to savings) is represented by negative numbers; the cash you receive (such as loan) is represented by positive numbers.

The PPMT or Principal Payment formula calculates the principal payment for a given period of an investment based on periodic, constant payments and a constant interest rate.

The first input is the interest rate per period, which must be in a fraction form (already divided by 100). The second input is the period for which you want to find the principal payment and must be in the range 1 to numberOfPeriods. The next input is the total number of payment periods in an annuity. The fourth input is the present value, or the lump-sum amount that a series of future payments is worth right now.

Then you have the future value, or the cash balance one wants to attain after the last payment is made. Enter 0 for the default future value which means that annuity based on present value is attained or paid off after the last payment is made.

The due type indicates when payments are due. A warning is logged if the type is not supported and, in this case, the platform defaults to 0:

• 0 if payments are due at the end of the period
• 1 if payments are due at the beginning of the period

For the presentValue and futureValue parameters, the cash you pay out (such as deposits to savings) is represented by negative numbers; the cash you receive (such as loan) is represented by positive numbers.

This is a slight variation of the PPMT formula, where payment is specified instead of the future value. This formula allows to pre-calculate periodic payment (PMT) before calculating the PPMT inside an iteration of periods.

Using this formula instead of the regular PPMT yields better performance as it does not have to re-calculate the payment in every iteration.

Generates a random string value that is at least minLen characters long and no longer than maxLen. The string is generated using English characters (a-Z) and digits (0-9) based on rules defined by the style parameter.

minLen and maxLen

Define the minimum and maximum length of the generated string. The generated string will be at least minLen characters long and will be no longer than maxLen characters long. If minLen is less than 1 it will default to 1. If maxLen is less than minLen the minimum length will be used.

style

RandomString(minLen,maxLen,style)

[continued]

The style parameter is a string that is used to control certain aspects of the format of the generated string. Except for the special characters ‘0’, ‘1’, ‘i’, ‘I’, ‘l’, ‘L’, ‘o’, and ‘O’ (see below) the characters used in the style parameter do not matter.

• Allow ‘0’: If style contains a ‘0’ (zero) the string can contain the digit ‘0’; otherwise ‘0’ is not used.
• Allow ‘1’: If style contains a ‘1’ (one) the string can contain the digit ‘1’; otherwise ‘1’ is not used.
• Allow ‘i’/’I’: If style contains an ‘i’ (case does not matter for this rule) the string can contain ‘i’/’I’ characters; otherwise ‘i’/’I’ is not used.
• Allow ‘l’/’L’: If style contains an ‘l’ (case does not matter for this rule) the string can contain ‘l’/’L’ characters; otherwise ‘l’/’L’ is not used.
• Allow ‘o’/’O’: If style contains an ‘o’ (case does not matter for this rule) the string can contain ‘o’/’O’ characters; otherwise ‘o’/’O’ is not used.
• Uppercase only: If style contains only uppercase characters the string will be completely uppercase.
• Lowercase only: If style contains only lowercase characters the string will be completely lowercase.
• Digits only: If style contains only digit characters the string will be completely digits.
• Uppercase and Digits: If style contains only uppercase characters and digits the string will contain only uppercase characters and digits.

RandomString(minLen,maxLen,style)

[continued]

• Lowercase and Digits: If style contains only lowercase characters and digits the string will contain only lowercase characters and digits.
• Mixed case: If the style contains uppercase and lowercase characters the string will be mixed case.
• Mixed case and Digits: If the style contains uppercase, lowercase, and digit characters the string will be mixed case with digits.
• Start with uppercase: If the style starts with two uppercase characters the string will start with an uppercase character.
• Start with lowercase: If the style starts with two lowercase characters the string will start with a lowercase character.
• Start with digit: If the style starts with two digit characters the string will start with a digit.

If the length of the generated string does not allow all the rules to be satisfied, the rules that specify the starting type take priority. For instance, if the generated string is two characters long and the rules specify to start with a digit and have mixed case; the string will start with a digit but will not have both uppercase and lowercase characters.

Style Examples:

• “aA” – Generate a mixed case string without digits or the special characters ‘I’/’I’,’L’/’l’,’O’/’o’.
• “aO” – Generate a mixed case string without digits allowing ‘o’ and ‘O’ to be used.
• “a2” – Generate a string with lowercase and digit characters without allowing the special characters.
• “AAa2” – Generate a mixed case string that includes digits that starts with an uppercase character.
• “01Aa” – Generate a mixed case string that includes digits, starts with a digit, and can contain the special characters ‘0’ and ‘1’.
• “A” – Generate a string with uppercase characters only.

All inputs should be text values. Returns the first string with all occurrences of the carriage return line break (CRLF) replaced with the second string.

Example:

• replaceCRLF(A, “<br/>”)

Rounds the fractional portion of value to the number of decimal places given by precision. The operation looks at the fractional digits to the right of the digit given by precision to determine the value of the fractional digit at precision and returns the converted value. If the digit to the right is 5 or greater, the digit at precision is rounded up. If the digit to the right is 4 or less, the digit at precision is rounded down. This operation is sometimes referred to as ‘Round Half Up’. The value of precision should be greater than or equal to zero. If precision is less than zero it is treated as zero.

Examples:

• Round(1.2345, 2) = 1.23
• Round(1.235, 2) = 1.24
• Round(1.2347, 3) = 1.235
• Round(-1.12164, 3) = -1.122

Rounds the fractional portion of a value to the number of decimal places given by precision. The operation effectively truncates the value at the number of decimal places given by precision, discarding any fractional value to the right of the digit at precision.

Examples:

• RoundDown(1.2345, 2) = 1.23
• RoundDown(1.328, 2) = 1.32
• RoundDown(-2.125, 2) = -2.12

Rounds the fractional portion of a value to the number of decimal places given by precision. The operation increases the value of the digit at precision if there are any digits to the right of the digit at precision.

Examples:

• RoundUp(1.2365, 2) = 1.24
• RoundUp(1.2325, 2) = 1.24
• RoundUp(1.23, 2) = 1.23

RoundX provides control of the rounding function via the mode parameter. There are seven modes.

• ROUND_CEILING (3)

  Round the fractional digit at precision so the value moves toward positive infinity.

• ROUND_UP (2)

  Same operation as RoundUp(value, precision).

• ROUND_HALF_UP (1)

  Same operation as Round(value, precision).

• ROUND_HALF_EVEN (0)

  If the fractional digit at precision needs to be rounded, round the value up or down as needed to make it even. This is sometimes referred to as a ‘Banker’s Round’.

• ROUND_HALF_DOWN (-1)

  If the fractional digit at precision needs to be rounded, use the digit to its right to determine the direction of the round. If the value is 6 or greater round up, if it is 5 or less round down.

• ROUND_DOWN (-2)

  Same operation as RoundDown(value, precision).

• ROUND_FLOOR (-3)

  If the fractional digit at precision needs to be rounded, round the fractional digit at precision so the value moves toward negative infinity.

Examples:

• ROUND_CEILING
  • RoundX(1.234, 2, 3) = 1.24
  • RoundX(1.12, 2, 3) = 1.13
  • RoundX(-1.234, 2, 3) = -1.23

RoundX(value,precision, mode)

[continued]

• ROUND_UP
  • RoundX(1.234, 2, 2) = 1.24
  • RoundX(1.23, 2, 2) = 1.23
  • RoundX(-1.234, 2, 2) = -1.24

• ROUND_HALF_UP
  • RoundX(1.234, 2, 1) = 1.23
  • RoundX(1.236, 2, 1) = 1.24
  • RoundX(1.235, 2, 1) = 1.24
  • RoundX(1.23, 2, 1) = 1.23

• ROUND_HALF_EVEN
  • RoundX(1.234, 2, 0) = 1.23
  • RoundX(1.236, 2, 0) = 1.24
  • RoundX(1.235, 2, 0) = 1.24
  • RoundX(1.245, 2, 0) = 1.24
  • RoundX(1.23, 2, 0) = 1.23

• ROUND_HALF_DOWN
  • RoundX(1.234, 2, -1) = 1.23
  • RoundX(1.236, 2, -1) = 1.24
  • RoundX(1.235, 2, -1) = 1.23
  • RoundX(1.23, 2, -1) = 1.23

• ROUND_DOWN
  • RoundX(1.234, 2, -2) = 1.23
  • RoundX(1.236, 2, -2) = 1.23
  • RoundX(1.235, 2, -2) = 1.23
  • RoundX(1.23, 2, -2) = 1.23

• ROUND_FLOOR
  • RoundX(1.234, 2, -3) = 1.23
  • RoundX(1.236, 2, -3) = 1.23
  • RoundX(1.235, 2, -3) = 1.23
  • RoundX(-1.234, 2, -3) = -1.24

Returns the number of characters in the string specified. The first position is zero.

Note:

• stringlength(“”) = 0
• stringlength(null) = 0

Returns the portion of the string specified that starts and ends at the indexes specified.

Examples:

• substring(“abcdefgh”,3,5) => cde
• substring(“weight”,2,6) => eight

Returns the date and time that corresponds to the date and time in the string specified. It recognizes a variety of formats in the text, which follow a predefined sequence:

• Numeric date in milliseconds since January 1, 1970
• MM/dd/yyyy hh:mm AM/PM
• dd/MM/yyyy hh:mm AM/PM
• dd-Month-yyyy HH:mm (Month name, Apr or April, and 24 hour time)
• dd/MM/yyyy hh:mm AM/PM zone (EST, PST, GMT-08:00, and so on)
• MM/dd/yyyy HH:mm:ss (24 hour time)
• dd/MM/yyyy HH:mm:ss (24 hour time)
• Month dd,yyyy hh:mm AM/PM zone (Month name and zone, for example, EST, PST, GMT-08:00, and so on)
• MM/dd/yyyy
• dd-Month-yyyy (Month name)
• dd/MM/yyyy
• Month dd/yyyy
• Month dd yyyy h:mmAM/PM

Note that the input “10/11/2011” is recognized as October 11^th since that will match the MM/dd/yyyy format. There is no way for this to be interpreted as the 10^th of November.

Where the formulas above say “Month”, the month abbreviation (Apr) or the full month name (April) can be used.

Returns the string specified with all leading and trailing spaces or control characters removed.

Note:

• trim(“”) = empty string
• trim(null) = null