Finance & Accounting functions

REC_PAY

IFRS 15 - Revenue; & IFRS ##s - Expenses

About

Helps with showing Receivable or Payable Amounts in Finance & Accounting, in accordance with the I.F.R.S. Standards.

Recognition and De-Recognition dates are complex! They depend on:

  1. whether you are considering a Revenue or Expense
  2. whether the Contract is Cancellable or Non-Cancellable
  3. when amounts become Un-Conditionally due
  4. when the corresponding Invoice has been issued (then received or sent), and
  5. when / if Cash has been received or paid

So, the logic for these dates has been excluded from this function. (You’ll, for now, need to work them out separately, depending on what documents you have).

Calls DAYS_PASSED.PCT.

Inputs:

  • amount : Contract Amount (‘Net’ and/or ‘Gross’ amount. See Note, below, on V.A.T.)
  • contract_start : Start Date of the Contract
  • contract_end : End Date of the Contract
  • eom_dates : Horizontal vector of month end dates
  • recog_date : Recognition Date
  • [derecog_date] : OPTIONAL - De-Recognition Date (usually when cash is received or paid)
  • [receivable_payable_needed] : OPTIONAL - if a receivable or payable is needed, set to ‘TRUE’; if not needed, set to ‘FALSE’. If blank, ‘TRUE’ is assumed

More Info:

NOTE: The recognition / de-recognition dates of any V.A.T. amounts may have to be calculated separately, as there are additional V.A.T. rules on this.

Code

M.S. Excel 
REC_PAY = LAMBDA(amount, contract_start, contract_end, eom_dates, recog_date, [derecog_date], [receivable_payable_needed],
    LET(
        receivable_payable, IF(
            (recog_date <= eom_dates) *
                IF(ISOMITTED(derecog_date), 1, (eom_dates < derecog_date)),
                amount,
                0
        ) * --IF(ISOMITTED(receivable_payable_needed), TRUE, receivable_payable_needed),
        receivable_payable
    )
);