6. Building Formulae
6.1 Introduction
To apply profit or charges on an account, you require certain data. For example, to calculate profit for an account you would require the following data:
- Principal – The amount for which you want to calculate profit
- Profit period – The number of days for which you want to apply profit
- Profit rate
When you want to apply charges on an account, you may have to specify the conditions for which you would need to apply charges. The amount that is charged may be different for different conditions. For example, you may want to apply charges on every extra account statement that has to be given to the customer.
When you define a ‘Rule’, you specify exactly how such data is to be picked up for calculating either the profit or charge. A ‘Rule’ identifies the method in which profit or charges have to be calculated.
The data required to calculate profit and charges are broadly referred to as ‘data elements’. Data elements are of two types:
- System Data Elements (SDEs)
- User Data Elements (UDEs)
Using the System Data Elements and the User Data Elements that you define for a rule, you can create formulae. Formulae connect SDEs and UDEs to give a result. The result of a formula is the profit or charge that has to be applied on an account.
To use a metaphor, SDEs and UDEs are the pillars on which an arch called formula rests. Many arches combine to form a dome called Rule. The things that you choose to put under a dome correspond to account classes (or accounts themselves) to which the rule applies.
This chapter contains the following sections:
6.2 Building Blocks of Formulae
You can build arches (formulae) to complete the domes (rules) that you are creating in the ‘Formula Wizard’ screen. You can define any number of formulae for a rule using this screen.
You can invoke this screen by clicking on the ‘Formula Wizard’ button if the ‘Formulas’ sub screen of e ‘Profit & Charges Rule Maintenance’ screen.
Element
Select valid building blocks to build a formula from the adjoining drop-down list. This list displays SDEs, UDEs or the result of other formulae that you have previously created.
Functions
Select a valid function to defining a formula from the adjoining drop-down list. This list displays the following functional operators:
Operator |
Description |
ABS |
Absolute value of |
LEAST |
Minimum of |
GREATEST |
Maximum of |
SUM |
The total value of |
ROUND |
Round to |
TRUNC |
Integer part of |
FLOOR |
Round off to the (lower) nearest |
CEILING |
Round off to the (higher) nearest |
POWER |
To the power of |
MOD |
The remainder |
Operators
Select a valid operator that you would require to define a formula from the adjoining drop-down list. Operators are symbols that you would use to build mathematical expressions while defining a formula. This list displays the following symbols:.
Operator |
Description |
+ |
Plus |
- |
Minus |
/ |
Divide by |
* |
Multiply |
Logical Operators
Select a valid logical operator that you would require to define a formula from the adjoining drop-down list. Logical Operators are indicators of certain conditions that you specify while building a formula. The list displays the following logical operators:
Operator |
Description |
AND |
the conjunction ‘and’ |
OR |
the conjunction ‘or’ |
> |
greater than |
>= |
greater than or equal to (please note that there is no space between the two symbols) |
< |
less than |
<= |
less than or equal to (please note that there is no space between the two symbols) |
< > |
Not equal to (please note that there is no space between the two symbols) |
= |
equal to |
6.3 Building Formulae
Using the building blocks discussed earlier, you can create or build formulae. You can build any number of formulae for a rule using the SDEs, UDEs and the results of formulae that you have defined for the rule.
The following example illustrates the procedure to create a formula.
REQUIREMENT
You would like to pay profit on the monthly minimum credit balance for all current accounts.
You would like to apply different rates of profit for the following credit balances:
Credit balance |
Rate |
1 - 10,000 |
1.5% |
10,001 - 15,000 |
1.75% |
15,001 - 20,000 |
2% |
above 20,000 |
3% |
SOLUTION
Step 1
You define ‘Monthly Minimum Credit Balance’ (MMCB) as an SDE at ‘SDE Maintenance’ level.
Step 2
You define a rule (let us say, Rule 1) and specify ‘Monthly Minimum Credit Balance’, Days, and Year as the SDEs. The following will be the UDEs for the Rule:
Amount 1, Amount 2, and Amount 3 (these will represent the three upper limits in the tier structure)
Rate 1, Rate 2, Rate 3, and Rate 4 (these will represent the four rates applicable for different amounts in the tier)
Step 3
When you build formulae to calculate credit profit for current accounts, you choose these SDEs and UDEs appropriately, along with the intermediate results, in the following manner:
Slab Structure:
|
Case |
Result |
1) |
MMCB <= AMOUNT1 |
(MMCB * DAYS * RATE1) / YEAR |
2) |
MMCB <= AMOUNT2 |
(MMCB * DAYS * RATE2) / YEAR |
3) |
MMCB <= AMOUNT3 |
(MMCB * DAYS * RATE3) / YEAR |
4) |
MMCB > AMOUNT 3 |
(MMCB * DAYS * RATE4) / YEAR |
Tier Structure:
Formula 1: Non-booked
Case |
Result |
MMCB <= AMOUNT1 |
(MMCB * DAYS * RATE1) / YEAR |
Formula 2: Non-booked
Formula 3: Non-booked
Case |
Result |
MMCB > AMOUNT1 |
[LEAST (MMCB, AMOUNT2) - AMOUNT1] * DAYS * RATE2 / YEAR |
Case |
Result |
MMCB > AMOUNT2 |
[LEAST (MMCB, AMOUNT3) - AMOUNT2] * DAYS * RATE3 / YEAR |
Formula 4: Non-booked
Case |
Result |
MMCB > AMOUNT3 |
[LEAST (MMCB, AMOUNT4) - AMOUNT3] * DAYS * RATE4 / YEAR |
Formula 5: Booked
Formula 1 + Formula 2 + Formula 3 + Formula 4