Custom FunctionS

Lambda Functions

The definition of each function is a computation.

Name

Note

Parameters

Computation

OneWeekLater

One week after the specified date

date

AddDays( date, 7 )

OxfordComma

Apply the Oxford Comma format to a list of texts

list

Format( list, "Oxford Comma" )

MyTermInclusive

The term between two dates (inclusive) using cardinal numbers

start , finish

Term( start, finish, "Term All + cardinal" )

MyTermExclusive

The term between two dates (exclusive) using cardinal numbers

start , finish

MyTermInclusive( start, AddDays( finish, -1 ) )

 

Javascript Functions

The definition of each function is a series of Javascript statements finishing with a return statement.

Math

Name

Note

Parameters

Javascript

E

 

 

return Math.E ;

LN2

 

 

return Math.LN2 ;

LN10

 

 

return Math.LN10 ;

LOG2E

 

 

return Math.LOG2E ;

LOG10E

 

 

return Math.LOG10E ;

PI

 

 

return Math.PI ;

SQRT1_2

 

 

return Math.SQRT1_2 ;

SQRT2

 

 

return Math.SQRT2 ;

abs

 

a

return Math.abs( a ) ;

cbrt

 

a

return Math.cbrt( a ) ;

imul

 

a,b

return Math.imul( a, b ) ;

max

 

a,b

return Math.max( a, b ) ;

min

 

a,b

return Math.min( a, b ) ;

pow

 

a,b

return Math.pow( a, b ) ;

random

 

 

return Math.random() ;

sign

 

a

return Math.sign( a ) ;

sqrt

 

a

return Math.sqrt( a ) ;

ceil

 

a

return Math.ceil( a ) ;

floor

 

a

return Math.floor( a ) ;

fround

 

a

return Math.fround( a ) ;

round

 

a

return Math.round( a ) ;

trunc

 

a

return Math.trunc( a ) ;

DegreesToRadians

 

a

return a * ( Math.PI / 180 ) ;

RadiansToDegrees

 

a

return a / ( Math.PI / 180 ) ;

acos

 

a

return Math.acos( a ) ;

acosh

 

a

return Math.acosh( a ) ;

asin

 

a

return Math.asin( a ) ;

asinh

 

a

return Math.asinh( a ) ;

atan

 

a

return Math.atan( a ) ;

atanh

 

a

return Math.atanh( a ) ;

cos

 

a

return Math.cos( a ) ;

cosh

 

a

return Math.cosh( a ) ;

sin

 

a

return Math.sin( a ) ;

sinh

 

a

return Math.sinh( a ) ;

tan

 

a

return Math.tan( a ) ;

tanh

 

a

return Math.tanh( a ) ;

hypot

 

a,b,c

return Math.hypot( a, b, c ) ;

 

Microsoft Excel® Investment

Find solutions to the equations:

     r > 0 :    pv.(1 + r)n + c.(1 + r.b).((1 + r) n - 1)/r + fv = 0

     r = 0 :    pv + c.n + fv = 0

where:

               pv          the present value of a periodic investment

               fv          the future value of a periodic investment

               n            the number of periods (life) in a periodic investment

               c            the investment amount per period

               r            the interest rate per period

               b            true       each investment is made at the beginning of each period, or
                              false      each investment is made at the end of each period

Name

Note

Parameters

Javascript

fv

the future value of an investment

r, n, c, pv, b

var rn = Math.pow( ( 1 + r ), n ) ;

var rb = ( b ? r : 0 ) ;

var fv = ( ( r < 0.000001 )

         ? - ( pv + ( c * n ) )

         : - ( ( pv * rn ) + ( c * ( 1 + rb ) * ( rn - 1 ) / r ) ) ) ;

 

return fv ;

pv

the present value that is required for an investment to reach a future value

r, n, c, fv, b

var rn = Math.pow( ( 1 + r ), n ) ;

var rb = ( b ? r : 0 ) ;

var pv = ( ( r < 0.000001 )

         ? - ( fv + ( c * n ) )

         : - ( ( c * ( 1 + rb ) * ( rn - 1 ) / r ) + fv ) / rn ) ;

 

return pv ;

nper

the number of periods required for an investment to grow from a present value to a future value

r, c, pv, fv, b

var rb = ( b ? r : 0 ) ;

var n  = ( ( r < 0.000001 )

         ? - (fv + pv) / c

         : Math.log( ( c * ( 1 + rb ) - ( fv * r ) ) / ( c * ( 1 + rb ) + ( pv * r ) ) ) / Math.log( 1 + r ) ) ;

 

return n ;

rate

the interest rate required for an investment to grow from a present value to a future value

n, c, pv, fv, b

var rmin = 0.00 ;

var rmax = 100 ;

var r  ;

var rn ;

var rb ;

var rf ;

 

do

{

  r    = rmax ;

  rn   = Math.pow( ( 1 + r ), n ) ;

  rb   = ( b ? r : 0 )            ;

  rf   = ( ( pv * rn ) + ( c * ( 1 + rb ) * ( rn - 1 ) / r ) + fv ) ;

  rmax = rmax / 2 ;

}

while ( rf >= 0 )

 

do

{

  r  = ( rmin + rmax ) / 2 ;

  rn = Math.pow( ( 1 + r ), n ) ;

  rb = ( b ? r : 0 )            ;

  rf = ( ( pv * rn ) + ( c * ( 1 + rb ) * ( rn - 1 ) / r ) + fv ) ;

  if ( rf < 0 )

  {

    rmax = r ;

  }

  else

  {

    rmin = r ;

  }

}

while ( Math.abs( rf ) > 0.00001 && ( rmax - rmin ) > 0.00001 )

 

return r ;

pmt

the periodic payment required for an investment to grow from a present value to a future value

r, n, pv, fv, b

var rn = Math.pow( ( 1 + r ), n ) ;

var rb = ( b ? r : 0 ) ;

var c  = ( ( r < 0.000001 )                                               

         ? - ( fv + pv ) / n

         : - ( ( fv + ( pv * rn ) ) * r / ( ( 1 + rb ) * ( rn - 1 ) ) ) ) ;

 

return c ;

ipmt

the interest for a specific period in an investment series

r, e, n, pv, fv, b

if ( e <= 1 )

{

  return 0 ;

}

 

var rn = Math.pow( ( 1 + r ), n ) ;

var rb = ( b ? r : 0 ) ;

var c  = ( ( r < 0.000001 )                                               

         ? - ( fv + pv ) / n

         : - ( ( fv + ( pv * rn ) ) * r / ( ( 1 + rb ) * ( rn - 1 ) ) ) ) ;

var i  = c

       - ( c * Math.pow( ( 1 + r ), ( e - 1 ) ) )

       - ( pv * r * Math.pow( ( 1 + r ), ( e - 1 - ( b ? 1 : 0 ) ) ) ) ;

 

return i ;

ppmt

the net periodic payment required for an investment to grow from a present value to a future value, less the interest for the specified period

r, e, n, pv, fv, b

if ( e <= 1 )

{

  return 0 ;

}

 

var rn = Math.pow( ( 1 + r ), n ) ;

var rb = ( b ? r : 0 ) ;

var c  = ( ( r < 0.000001 )                                               

         ? - ( fv + pv ) / n

         : - ( ( fv + ( pv * rn ) ) * r / ( ( 1 + rb ) * ( rn - 1 ) ) ) ) ;

var i  = c

       - ( c * Math.pow( ( 1 + r ), ( e - 1 ) ) )

       - ( pv * r * Math.pow( ( 1 + r ), ( e - 1 - ( b ? 1 : 0 ) ) ) ) ;

 

return ( c - i ) ;

 

Microsoft Excel® Depreciation

Name

Description

Parameters

Javascript

Sln

straight line

cost, salvage, life

return ( cost - salvage ) / life ;

Syd

sum of years

cost, salvage, life, period

return ( ( cost - salvage ) * ( life - period + 1 ) * 2 ) / ( life * ( life + 1 ) ) ;

Db

fixed declining balance

cost, salvage, life, period, months

var rate = ( 1 - Math.pow( ( salvage / cost ), ( 1 / life ) ) ) ;

 

// first year

var totaldepreciation = ( cost * rate * ( months / 12 ) ) ;

 

if ( period > 1 )

{

  // intermediate years

  for ( var j = 2 ; j < period ; j ++ )

  {

    totaldepreciation += ( ( cost - totaldepreciation ) * rate ) ;

  }

 

  // final year

  var finalyearproportion = ( ( months !== 12 && period === life + 1 )

                            ? ( ( 12 - months ) / 12 )

                            : 1 ) ;

  totaldepreciation = ( cost - totaldepreciation ) * rate * finalyearproportion ;

}

 

return totaldepreciation ;

Ddb

double declining balance

cost, salvage, life, period, factor

return this.vdb( cost, salvage, life, ( period - 1 ), period, factor ) ;

Vdb

variable double declining balance

cost, salvage, life, startperiod, finishperiod, factor

var startdepreciation = 0 ;

 

for ( var j = 0 ; j < startperiod ; j ++ )

{

  var periodDepreciation = Math.min(

                           ( ( cost - startdepreciation ) * ( factor / life ) ),

                           ( ( cost - startdepreciation ) - salvage           ) ) ;

 

  startdepreciation += periodDepreciation ;

}

 

var totaldepreciation = startdepreciation ;

 

for ( var j = startperiod ; j < finishperiod ; j ++ )

{

  var perioddepreciation = Math.min(

                           ( ( cost - totaldepreciation ) * ( factor / life ) ),

                           ( ( cost - totaldepreciation ) - salvage           ) ) ;

 

  totaldepreciation += periodDepreciation ;

}

 

return totaldepreciation - startdepreciation ;

 

Bad Definitions

Name

Description

Parameters

Javascript

SYNTAX

The code is not Javascript

 

variable a = 1 ;

variable b = 2 ;

returning a + b ;

NORETURN

The Javascript code does not return a value

 

var a = 1 ;

var b = 2 ;

ERROR

The Javascript code throws an error

 

return UnknownFunction() ;

LOOPYFOR

The Javascript code contains a FOR loop that exceeds the maximum number of iterations (1000)

 

for ( var i = 0 ; i <= 1001 ; i ++ )

{

  console.log( i ) ;

}              

 

return 1234 ;

LOOPYWHILE

The Javascript code contains an infinite WHILE loop

 

var j = 0 ;

while ( true )

{

  console.log( j ) ;

  j ++ ;

}

 

return 1234 ;

LOOPYDO

The Javascript code contains an infinite DO loop

 

var k = 0 ;

do

{

  console.log( k ) ;

  k ++ ;

}

while ( true )

 

return 1234 ;

 

FormatS

Note that the name of the format, and any override, is not case-sensitive.

A format is applied using the built-in function:

               format( value, format-name )

For example, using the formats defined below:

               format( -10005, "usd" )

will generate the text:

               -$1,000.50¢

Formats can be overriden.  For example:

               format( -10005, "USD+NoCent" )

will generate the text:

               -$1,000.50

The format overrides are:

CurrencyName Use the currency name rather than the currency symbol

CurrencySymbol Use the currency symbol rather than the currency name

Cent Include the cent character

NoCent                                          Do not include the cent character

TrimDecimal Decimal digits are not included

NoTrimDecimal Decimal digits are included up to the precision required

ZeroTrimDecimal                        Do not include decimal digits that are all 0s.

UpperCase Uppercase all characters in the resultant text

LowerCase Lowercase all characters in the resultant text

Formats can be combined by commas (similar to CSS styles) with precedence taken by later formats.  For example:

               format( date(2020,2,29), "javascript,upper" )

will generate the text:

               SAT FEB 29 2020

Although the following example formats are organised according to type (number, date, etc.) a single format may combine any of the table columns into a single definition (see French Combo below).

Number Formats

Name

000

Decimal

Fixed

Min

Max

 

Example

Comma Dot

,

.

 

 

 

 

1,000.5059

Dot Comma

.

,

 

 

 

 

1.000,5059

Precision 0

 

 

0

 

 

 

-1000

Precision 1

 

 

1

 

 

 

-1000.5

Precision 2

 

 

2

 

 

 

-1000.51

Precision 3

 

 

3

 

 

 

-1000.506

Precision 4

 

 

4

 

 

 

-1000.5059

Max 2

 

 

 

 

2

 

-1000.51

Min 2

 

 

 

2

 

 

-1000.51

Min 2 Max 4

 

 

 

2

4

 

-1000.5059

 

Currency Formats

Name

Currency

Symbol

Location

Minus

Trim

000

Decimal

Cent

 

Examples

USD

USD

$

 

 

 

,

.

¢

 

-$1,000.50¢                    -$1,000.00

GBP

GBP

£

afterminus

 

never

,

.

 

 

-£1,000.50                       -£1,000.00

EUR

EUR

beforeminus

 

zero

.

,

 

 

€-1.000,50                       €-1.000

Accounts Outside

YEN

¥

beforeminus

(,)

 

+

:::

 

 

¥ (1+000:::50)                 ¥ (1+000:::00)

Accounts Inside

YEN

¥

afterminus

(,)

 

+

:::

 

 

(¥1+000:::50)                  (¥1+000:::00)

Location                         afterminus (default)                             The currency symbol is located after the minus sign for a negative amount                              e.g. -$1                             e.g. ($1)
beforeminus                             The currency symbol is located before the minus sign for a negative amount                             e.g. $-1                             e.g. $(1)

Trim                                 never (default)                             The decimal digits are always included                             e.g. 1.0                             e.g. -1.002
zero                             The decimal digits are included except when all the digits are zero                             e.g. 1                             e.g. -1.002
always                             The decimal digits are never included                             e.g. 1                             e.g. -1

Date Formats

Name

Date

Case

Example

USA

[month2]-[day2]-[year4]

 

02-29-2020

UK

[day]/[month]/[year4]

 

29/2/2020

JavaScript

[weekdayabbr] [monthabbr] [day2] [year4]

 

Sat Feb 29 2020

ISO

[year4]-[month2]-[day2]T12:00:00

 

2020-02-29T12:00:00

Years

[year4] ++ [year2] ++ [yearcardinal] ++ [yearordinal] ++ [yearordinalsuffix]

 

2020 ++ 20 ++ Two Thousand and Twenty ++ Two Thousand and Twentieth ++ 2020th

Months

[month] ++ [month2] ++ [monthname] ++ [monthabbr]

 

2 ++ 02 ++ February ++ Feb

Days

[day] ++ [day2] ++ [daycardinal] ++ [dayordinal] ++ [dayordinalsuffix]

 

29 ++ 29 ++ Twenty Nine ++ Twenty Ninth ++ 29th

Weekdays

[weekday] ++ [weekdayname] ++ [weekdayabbr]

 

6 ++ Saturday ++ Sat

Upper

[dayordinal] of [monthname]

UPPERCASE

TWENTY NINTH OF FEBRUARY

Lower

[dayordinal] of [monthname]

lowercase

twenty ninth of February

 

Boolean Formats

Name

True

False

 

Example

French

Oui

Non

 

Oui

 

List Formats

Note that < > are required to preserve table cells that begin/end with a space character.

Name

Separator 1

Separator 2

 

Example

And

< and >

 

 

a and b and c and d

Oxford Comma

<, >

<, and >

 

a, b, c, and d

 

Combination Formats

Name

True

False

Date

Symbol

000

Decimal

Separator 1

Separator 2

Example

French Combo

Oui

Non

[day] [month] [year4]

.

,

< et >

 

Oui et Non

29 2 2020 et 1 3 2020

€1.200.300,00 et €-7,00

 

today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
today
Choose a building block.
Choose a building block.
today