Skip to Content

Calculation functions in the Entry templates

3688  Entry templates

The Entry templates contain a Formula field in which you can enter a calculation formula to generate the amount of the entry line. Various functions (similar to those used in Spreadsheets) are available:

General functions

Basic operators + - * / and parentheses

The basic operators are addition, subtraction, multiplication, and division. Parentheses can be used to fix the precedence of operations.

Operator ^n

Allows you to raise a number to the power of n. Useful for compound interest calculations.

Abs(value)

Absolute value of the number (transforms a negative number into a positive number).

IF(condition;value_if_TRUE;value_if_FALSE)

Returns the second or third argument depending on whether the condition passed as the first argument is true or false.

IntegerOf(value)

Truncates the decimal part of the number passed as argument.

Min(value1;value2;…)

Returns the smallest value of the values passed as arguments.

Max(value1;value2;…)

Returns the largest value of the values passed as arguments.

Random

Generates a random number between 0 and 0.9999999… A new number is generated each time the model is used. Allows for the inclusion of an element of uncertainty in simulation scripts.

Round(value; number_digits)

If the number_digits argument is positive, the rounding is done to the right of the decimal point. If it is negative, the rounding is done to the left of the decimal point. If it is zero, the number is rounded to the nearest integer.

Financial functions

These functions take arguments whose code meanings are as follows:

Abbreviation Argument
i interest rate over the period
n number of periods
m monthly payment made at the end of the period
f final value at the end of the period
p present value of the loan

PMT(i;n;p)

Calculate the amount of monthly repayments for a loan.

VA1(i;n;m)

Calculate the present value of a sum using compound interest.

VA2(i;n;f)

Calculate the present value of a sum using simple interest.

VF1(i;n;m)

Calculate the final value of a sum using compound interest.

VF2(i;n;f)

Calculate the final value of a sum using simple interest.

Date functions

AddToDate(date;number_of_days;number_of_monthes;number_of_years)

Add (or subtract if negative) a certain number of days, months or years to the date passed in the first argument.

Day(date)

Day of the date passed as argument, between 1 and 31.

EntryDate

Entry date.

Month(date)

Month of the date passed as argument, between 1 and 12.

Year(date)

Four-digit year of the date passed in argument.

Accounting functions

This is a subset of the accounting functions offered in the spreadsheet and report editor.

Ask(“Question”;{Suggested_amount})

Calls up a dialog asking the user to answer a question. Always returns a number. If the user clicks the Cancel button, the entire model generation is aborted. This function cannot be used in a periodic model.

Bal(Account;{Center};{Type};{Date1};{Date2})

Voir cet article .

CellValue(Spreadsheet_name;Cell_name)

Allows you to refer to a cell in a worksheet whose name is passed as the first argument. The cell must have been named in the worksheet and that name is passed as the second argument. This function only returns a result if the called worksheet is simultaneously open in another window.

CellValueRC(Spreadsheet_name;Line_number;Row_number)

Similar to the previous function, but targeting a cell by its row and column numbers rather than by its name. This function only returns a result if the called worksheet is simultaneously open in another window.

CurrencyRate(Currency_code;{Date})

Voir cet article .

CurrentLineValue

Returns the calculated value of the first line of the journal entry. If the amount of the corresponding line is a credit, it will be negative. This function is useful for partial templates to complete an entry that is being entered, for example to split the amount entered on the first line into sub-accounts.

EndBalance

This function can only be used once per template. It represents the amount to balance the entry. It must be on the last line of the template, is mandatory for a complete template, and can only be used alone in a formula. The balance value is calculated after calculating the VAT if the Calculate VAT option has been checked.

LineValue(Line_number_of_model)

Returns the calculated value of the row in the template. Can only refer to a row higher than the one where the formula is located. If the amount of the corresponding row is a credit, it will be negative.

MonthBal(Account;Center;Type;Period;Origin)

Voir cet article .

OpBal(Account;{Center};{Type};{Origin})

Voir cet article .

TotDebit(Account;{Center};{Type};{Date1};{Date2})

Voir cet article .

TotCredit(Account;{Center};{Type};{Date1};{Date2})

Voir cet article .

Variation(Account;Center;Type;Period1;{Period2})

Voir cet article .
Calculation functions in the Entry templates